Windows 7 - Circular Calculation with TREND Function?

Asked By Justin on 10-Aug-12 01:27 AM
I am trying to use the TREND function to predict sales.
I am getting an error saying the formula has a circular reference.  I am
completely stumped as to why this is.  The formula is in cell B7, and as
per my screen shot, I do not refer to B7 anywhere.  Why would it be circular?

=TREND(B$2:B$6,A$2:A$6,A7,1)

http://postimage.org/image/iz1ichnk7/




joeu2004 replied to Justin on 10-Aug-12 01:42 AM
I have no problem with that formula when B2:B6, A2:A6 and A7 all contain
constants.

Perhaps in your situation, one or more of those cells have a circular
formula.
joeu2004 replied to joeu2004 on 10-Aug-12 01:45 AM
Instead of uploading an image file, upload an example Excel file that
demonstrates the problem.

You can upload an example Excel file (devoid of any private data) that
demonstrates the problem to a file-sharing website and post the "shared",
following is a list of some free file-sharing websites; or use your own.

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com
Justin replied to joeu2004 on 10-Aug-12 02:01 AM
Thanks for the tip.  This ought to do it!
My usenet server will not let me post anything with http(colon, slash, shash)
www.filedropper.com/troublesometrendfunction

I copy and pasted the problem area into a brand new file.  The other one
had several other sheets that I am sure nobody wants to look at!
joeu2004 replied to Justin on 10-Aug-12 03:25 AM
Unfortunately, when I try to download the file from filedropper.com, it
wants to install an application.  I do not want that, as benign as it might
seem.  It is the principle of the matter.

If you can upload to filedropper.com, you should have no problem uploading
to box.net/files.  You do not need the prefix http://, just as you did not
use that prefix to get to filedropper.com apparently.

I hope you can upload the Excel file directly, instead of an "archive" (zip
file) that contains the Excel file.

Alternatively, send the file to me directly.  Send email to joeu2004 "at"
hotmail.com.



Just so long sa the new file duplicates the circular reference problem.
Justin replied to joeu2004 on 10-Aug-12 11:42 PM
That's why I use a Mac.  Nothing gets installed on this machine unless I
tell it.
I do not see why you would need the file, when I clicked on my own link,
nothing attempted to install and the file just downloaded.


I had to omit all of that to post to usenet.

It was an Excel file, I did not ZIP it.


Sent.
joeu2004 replied to Justin on 11-Aug-12 02:19 AM
I do not get a circular reference error.  I do get a #VALUE error.

This is because the years in A2:A6 and A7 are text, not real numbers.

No problem once they are converted to numbers.

One way to convert them to real numbers is to use the Text To Columns
feature.
Justin replied to joeu2004 on 11-Aug-12 02:57 PM
But the years are not involved in the calculation, so why would it complain?