Rounding Up or Down to nearest $10

  • Thread starter Thread starter jeffc4442
  • Start date Start date
J

jeffc4442

I need to round the results of a sum up/down to the nearest $10. i've been
looking in excel help and also online discussions. i've found a lot of
answers, but can't figure out how to make it work. I am running Excel 2003.
I have installed the Analysis ToolPak Add-In.to complicate matters, i'm
adding cells from a number of worksheets (all in the same book) into another
worksheet (still in the same book).

Here is a sample formula from one of my cells:

=SUM(+'T515'!C9+'T516'!C9+'T517'!C9+'T525'!C9+'T526'!C9+'T800'!C9+'T801'!C9+'T802'!C9+'T803'!C9+'T804'!C9+'T805'!C9+'T806'!C9+'T807'!C9+'T820'!C9+'T821'!C9+'T199'!C9+'T199-NOT USED'!C9+'T8293'!C9)

I read that i'm supposed to do something like =ROUND or =ROUNDUP or =CEILING
followed by numbers. but where does this formula go in the above?

Please assume i am a moron when answering! =)

Thanks!!!
 
Hi Jeff

Since you are using the plus sign between each item, you don't need to also
use Sum.

=CEILING('T515'!C9+'T516'!C9+'T517'!C9+'T525'!C9+'T526'!C9+'T800'!C9+'T801'!C9+'T802'!C9+'T803'!C9+'T804'!C9+'T805'!C9+'T806'!C9+'T807'!C9+'T820'!C9+'T821'!C9+'T199'!C9+'T199-NOT
USED'!C9+'T8293'!C9,10)
will round up to nearest $10

Replace CEILING with FLOOR to round down.
 
To round the number in cell A1 you need to enter the following in any cell
=ROUND(A1,-1)

If you want to round your sum then you need to replace A1 in above by your
SUM formula (do not inculde the = sign :-))

So you need to have this in the cell which has your formula

=ROUND(SUM(+'T515'!C9+'T516'!C9+'T517'!C9+'T525'!C9+'T526'!C9+'T800'!C9+'T801'!C9+'T802'!C9+'T803'!C9+'T804'!C9+'T805'!C9+'T806'!C9+'T807'!C9+'T820'!C9+'T821'!C9+'T199'!C9+'T199-NOT USED'!C9+'T8293'!C9) ,-1)
 
Back
Top