• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Simple/obvious function (divide) gives wrong result - formatting issue?

A

akuku

New Member
Hi!

I have a really weird problem with simple function. I want to divide result of one function by another function's result. Let's say cell:
A1's value is 2
A2's value is 3

Below I have a function "=A1/A2" and it gives wrong result instead of simply 2/3.
What's more interesting, if I type the same function in different cell in the same tab I get the proper value of 2/3.

In my case I want to divide 139,805,345 by 99,571 and in the broken cell I get 334.1 instead of 1,404, which is correct answer.

Can anybody help me on that, becuase I try everything (clear all in broken cell, different formatting) and nothing works.

Thank you very much for help in advance!
 
Hi, akuku!
Checked yet if calculation option is set to automatic? If set it so and try again.
Regards!
 
Could you post your workbook, or an example workbook showing this error? I am unable to duplicate in a fresh workbook on my machine.
 
is there data in the surrounding cells?
if not, you could try deleting just that cell... or insert a new row, try the formula there.
 
Ok guys, so this is really weird. The problem is caused by a wrong sum calculation in one cell. I will try to describe the issue as specific as possible, but please excuse my english.
Since the document is huge I tried to move and copy the broken tab to new blank document to present you the problem. After I've done that I discovered that one of the cells which were driving the broken formula changed its value. Please see it below:
http://www32.speedyshare.com/6BjZn/download/1.png (yellow highlighted cell is a formula which divides 139.7 mln by 99,571 and gives wrong result and blue fonted 1,403 is hardcoded number - proper result of division)

After moving the tab 6,258,796 turned into blue highlighted number of 108,614,318. What is really weird, that now the blue highlighted formula show us correct result from previous calculation (139,7mln/99,571 = 1,403 and you can see that the 139,7mln changed into 37,6 mln so 1,403 is not yet correct, but it's shown as a formula result in yellow highlighted cell).

Cell with 108mln is simple sum function which adds the results from different tabs. You can see in the next print screen that the outcome of the function shouldn't be 108mln: http://www32.speedyshare.com/Pygpv/download/2.png (-6,009,702 -249,094 is not equal to 108 mln)

What's more, after I save the this copied document with 108 mln and close both the original file, (from which I copied the tab) and the broken tab with 108 mln and then I open one again this broken document I can see that 108 mln turns into proper number of -6,258k.

I am not sure if you understood me well, but I think my question would be how is that possible that simple adding numbers is broken and result in such a huge miscalculation. It's too complex model to simply move the tab to different file and forget about the problem, so I would really appreciate any help.
Thanks!
 
I have an answer. I checked the very last functions which were then aggreagated and driving the final result. It turned out that those functions are calculated wrongly as well as this abovementioned example with 108 mln.
So simple sum functions were adding numbers and providing the wrong result. What I had to do was relinking them once again (manually choosing e.g. cell A1 to add to A2) and it worked. I can't understand though why excel got confused and was spitting the wrong result of a formula like in the second print screen I posted in the last message. Something must have gone wrong after my adjustments in the model by adding additional columns and rows.
So I don't have an explanation for miscalculation but relinking the source cells which were aggreagted to give the final result worked.
 
Luke M said:
Could you post your workbook, or an example workbook showing this error? I am unable to duplicate in a fresh workbook on my machine.
Click to expand...
akuku said:
Since the document is huge I tried to move and copy the broken tab to new blank document to present you the problem.
Click to expand...
Hi, akuku!
And what about posting the requested sample file instead of 2 screenshots? Help people helping you instead of making things more difficult for them.
Regards!
 
You must log in or register to reply here.
Back
Top