Share via

EXCEL formula calculates correctly but returns 0 into cell

Anonymous
2014-05-29T20:50:30+00:00

I'm having a problem where I write an equation in EXCEL - the first time it calculates correctly and puts in the correct answer. an examples equation is

=SQRT((U6-U$4*W6)^2+(V6-V$4*W6)^2).

When I edit the formula to change the U$4 and V$4 to U$2,V$2 respectively, the answer changes to 0 regardless of what the correct answer is. When I use the evaluate function tool, it gives the correct data right up to the end, and then enters 0.

Once the cell starts returning 0, nothing I can do will fix it.

Another problem in the same column then  becomes this: When I copy an equation, such as the one above, which has relative cells. The new equation handles the relative cells correctly - but the value is the same as the original cell. The example below shows a 0 returned for D1. D2 is the correct answer using A2 andB2. Then D2 equation was copied to D3:D6 and the values are now all the same. Below are the equations.

I've never seen anything like this. Its like there is a bug. It doesn't seem to be a particular function, (I'm using a lot of trig,) or how I edit, or how I copy. I tried saving as an older version, but that didn't seem to help either.

Here is the file. the Problem is highlighted in yellow in column X.   https://dl.dropboxusercontent.com/u/38252655/GPS%20data%20analysis%20example.xlsx

Help would be appreciated.

0.566334 10.12963 1 0.0
0.162986 10.37519 1 10.4
0.056249 8.056944 1 10.4
-0.34397 7.878207 1 10.4
-0.36129 7.661049 1 10.4
-1.41451 6.176085 1 10.4
=COS(I3*PI()/180)*P3 =SIN(I3*PI()/180)*P3 1 =SQRT((U3-U$2*W3)^2+(V3-V$2*W3)^2)
=COS(I4*PI()/180)*P4 =SIN(I4*PI()/180)*P4 1 =SQRT((U4-U$2*W4)^2+(V4-V$2*W4)^2)
=COS(I5*PI()/180)*P5 =SIN(I5*PI()/180)*P5 1 =SQRT((U5-U$2*W5)^2+(V5-V$2*W5)^2)
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 102K Reputation points Volunteer Moderator
    2014-05-29T23:12:30+00:00

    Hi,

    That is happening because of the circular reference in cell P4.  In cell P4, the formula is =O4*P$4.  While in a cell, you should not refer to that very cell in the formula.  You will notice that is you type 1 in cell P4 or write any other formula in cell P4 which does not refer to P4, then you will get numbers other than 0 in column X.

    Hope this helps.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Ashish Mathur 102K Reputation points Volunteer Moderator
    2014-05-30T01:41:12+00:00

    You are welcome.  If my reply helped, please mark it as Answer.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2014-05-30T00:26:40+00:00

    Thank you! I knew it kept giving the error message, but I couldn't figure out which cell was the problem.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2014-05-29T22:22:00+00:00

    Thanks. I edited the post and put a link to my dropbox folder.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2014-05-29T21:58:56+00:00

    Unless someone gets lucky with a wild guess, the quickest way to resolve this is to upload an example Excel file to a file-sharing website and post the "shared" URL in a response here.  The following is a list of some free file-sharing websites; or use your own.

    Dropbox: http://dropbox.com

    Box.Net: http://www.box.net/files

    MediaFire: http://www.mediafire.com

    FileFactory: http://www.filefactory.com

    FileSavr: http://www.filesavr.com

    RapidShare: http://www.rapidshare.com

    Windows OneDrive: http://onedrive.live.com

    Was this answer helpful?

    0 comments