EXCEL: Sum of Values adding incorrectly 1

[Excelerate2004]

Hello all,

I think this is a relatively easy thing to solve but so far its perplexed me.

I have an application whereby NUMEROUS calculations are carried out on various numbers over 5 tab sheets.

I get down to my very last step and my numbers are sometimes off by 1. I should mention that there is a ROUND function in a few places, however removing that does nothing.

As an example I get the following numbers:
11279,55379,48373,465,7012,3361,0,0, 3080

These numbers should add to 128,949 but I'm getting 128,950!

I've used the VALUE function to see whats actually in the cell and that just gives me the correct #'s, what else can I do? Its very frustrating.

Thanks for any help I can get

 

[tchatzi]

Right click to the cell that holds the number 128.95 and choose 'Format Cells'
Choose 'Number' from the list , at 'Decimal places' choose '3'
and uncheck the 'Use 1000 Seperator(,)'
Then try to right the number you want with a dot '.' and not with a comma ','

Good luck

 

[01Patrik]

The issue does probably relate to the rounding of the numbers. If you use ROUND in your summary formula, then it summarises the numbers that you see (without decimals) rather than the exact numbers behind the format, which the SUM function does per default.

=sum(round(XXX),round(XXX),round(XXX))


// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[doxiepup]

FORMAT just changes what is displayed (the actual value remains unchanged).

ROUND actually changes the value stored in the cell.

 

[Gavona]

The issue does probably relate to the rounding of the numbers." (1.4 + 1.3 = 2.7 but when formated to display as integers this shows 1 + 1 = 3)

What I do if this happens is one of:
(i)Change the formating temporarily to show one or two decimal places
(ii)Starting with the final result and working backwards identify the specific culprit cells (non-integer) to resolve. Highlight the cell, press F2 then F9. The formula bar then shows the exact result of the calculation. Press Esc to ensure you don't inadvertently convert the cell from formula to values.
(iii) Insert a column to the right of the data being summed. Assuming the new column is B then the formula would be:
=round(A1,0)=A1
This would return False whenever the cell in A1 was numeric but not an integer. Autofilter often helps at this point... and it is usually either that someone has miskeyed an entry or a calulation that has been done but not rounded.

Thanks,

Gavin

 

[Excelerate2004]

I wont get a chance to look at this until later in the day, I will repost wheb I try the solutions mentioned.

I am aware of the formatting of the cells.

Thansk for all the help so far!

 

[Excelerate2004]

Perluserpengo:

I followed your tip and removed the "," seperator from the required Sum cells and it seems to have done the trick...for now, I'll have to do further testing of course!

It boggles me how that simple formatting could mess up my values??

Its really strange!

 

[tchatzi]

Any time man!

 

[Excelerate2004]

Oh yeah..thanks to all who helped!

 

[01Patrik]

The format didn't mess up your values - The format worked perfect.

1,8 --> 2
1,6 --> 2
1,7 --> 2
1,7 --> 2

Summarise the numbers above. It will always show

6,8 --> 7

...even though one might have thougt it to be 8 if the number of decimals set were 0, because of rounding. Simple math.

You even have a couple of buttons in the format toolbar, who increase / decreases the number of decimals on the selected range.

Summary: If you want 128,950 - then you need to summarise rounded values. Otherwise Excel is summarising the actual values, no matter which format is on top.

(Same thing goes with dates - the values are just numbers with a format on top. Decimals represent time values - 1/24 = 1 hour.)


// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.