Auto Sum not working correctly

I have been conducting a payroll for a company for over a year now. Part of the process is developing an excel worksheet outlining the hours worked and the total gross pay for each employee. The total of this spreadsheet has to balance to my ADP register. Last week the totals would not balance. When I added the excel numbers up manually however, the totals matched. I looked again and noticed that when I selected all of the totals for the employees the sum in the bottom right corner was the same number as my total in ADP and that I calculated manually. Does anyone know why my Auto Sum Cell was not calculating correctly? All of the relevant cells were included in the formula so I am not sure why it would not work. Any suggestions are helpful.

Originally Posted by swalzy

I have been conducting a payroll for a company for over a year now. Part of the process is developing an excel worksheet outlining the hours worked and the total gross pay for each employee. The total of this spreadsheet has to balance to my ADP register. Last week the totals would not balance. When I added the excel numbers up manually however, the totals matched. I looked again and noticed that when I selected all of the totals for the employees the sum in the bottom right corner was the same number as my total in ADP and that I calculated manually. Does anyone know why my Auto Sum Cell was not calculating correctly? All of the relevant cells were included in the formula so I am not sure why it would not work. Any suggestions are helpful.

How far out were the figures?

My guess would be that Excel works right down to a large number of decimal places and it only has to be 0.000000000000000001 different (for example) for two numbers not to match.

If your values are created using formulas that divide one value by another, this can easily happen.

Perhaps look into using the ROUND function to fix things.

Or if you want a more indepth answer, perhaps you could post an example workbook (with no personal information included) for us to have a look at.

S.

On 20/07/2012 12:33 AM, swalzy wrote:
> I have been conducting a payroll for a company for over a year now. Part
> of the process is developing an excel worksheet outlining the hours
> worked and the total gross pay for each employee. The total of this
> spreadsheet has to balance to my ADP register. Last week the totals
> would not balance. When I added the excel numbers up manually however,
> the totals matched. I looked again and noticed that when I selected all
> of the totals for the employees the sum in the bottom right corner was
> the same number as my total in ADP and that I calculated manually. Does
> anyone know why my Auto Sum Cell was not calculating correctly? All of
> the relevant cells were included in the formula so I am not sure why it
> would not work. Any suggestions are helpful.
>
>
>
>
Hi

Just a though, someone may have opened a file that has Auto Calculation
set to manual when opened and does not have it set back to Auto when
closing.

To check this, depending on the version you are using.

2000 - 2007.
Tools | Options | Calculation
Select Automatic.

2010
File | Options | Formulas | Calculation Options
Select Automatic.

Or if you want to keep the sheet as manual, just hit F9 to do an instant
auto-calc.

HTH
Mick.