There are occasions when Excel might not add up properly, even though you still expect it to do so. Lets look at a very simple example to illustrate the case. You can reproduce this yourself as you read.
I have a small table of data, four columns wide. There are two row entries.
I want a summary of the data in the table. Specifically I am after a sum of column C where the code in column A is A, and I want the sum of column D where the code in column B is B. In both cases this should give me a result of 200 or -200.
The respective formulae to summarise by A code are =SUMIF(A:C,F2,C:C) and =SUMIF(B:D,F2,D:D). The respective formulae to summarise by B code are =SUMIF(A:C,F3,C:C) and =SUMIF(B:D,F3,D:D).
The results given....
As you can see, Excel incorrectly returns a value of -200 against the B code in the summary of column C. Did you get the same result? This is a very simplistic example to illustrate the need to validate any report or analysis performed with Excel before publishing.
Of course, how you organise your data is also relevant. If the table were instead organised as follows
the correct result is returned.
© Eriginal Ltd 2011, all rights reserved