Calculating Sum - when numbers are rounded...

[klee]

Okay, here's the problem... we have a large list of numbers that we have told
excel to round to the nearest 'one'. for example,

1.24753 rounded to 1
3.45879 rounded to 3
5.974531 rounded to 6

Now - we want to total these rounded numbers... in the case of the above
example, we need to have the sum calculate to 10...
however - the SUM is calculating with all of the decimals as well, so we are
ending up with 11.

I know in this example it doesn't seem like a big deal - but in a list of
over 400 numbers - you can imagine the number just doesn't work the way we
need it to...

What do we need to do differently to the formula - in order for it to
calculate the way we need it to?

Thanks!

 

[T. Valko]

**Maybe** this:

=SUMPRODUCT(ROUND(A1:A3,0))

 

[Fred Smith]

How are you rounding? It sounds like you are formatting the number to 0
decimal places, but that doesn't change the underlying number. To round, you
need to use a formula like:

=round(yourformula,0)

Regards,
Fred.