Round Totals

[KLAUS]

Can not figure out the formula to perform the following function

If my total equals between $90.00 and $92.50 round it off to $90.0
If my total equals between $92.50 and $95.00round it off to $95.0
If my total equals between $95.00 and $97.50 round it off to $95.0
If my total equals between $97.50 and $100.00 round it off to $100.0
If my total equals between $100.00 and $102.50 round it off to $100.0
If my total equals between $102.50 and $105.00 round it off to $105.0
and kepp going throut the thousands with the same conlusio
Thank you
KLAU

 

[Dreamboat]

Looks like John Walkenbach has your solution here: http://www.j-walk.com/ss/excel/usertips/tip011.ht

Basically

=round(a1/2.5,0)*2.

<-*-><-*-><-*-><-*-><-*-><-*-><-*-><-*-
Hope this helps
Anne Tro
Author: Dreamboat on Wor
Email: Dreamboat*at*Piersontech.co
Web: www.TheOfficeExperts.com

 

[Klaus]

Iwill try it and let you know

 

[KLAUS]

Works great, except like if my total is $212.41 than it goes to $212.50 and not $200.00

 

[KLAUS]

Is there anyway I can email you my worksheet so you can see what I am trying to do?

 

[Vasant Nanavati]

I think Dreamboat meant:

=ROUND(A1/5,0)*5

And I think you meant $210.00; not $200.00.

 

[Dreamboat]

Hi, Klaus. I understand what you want perfectly, I believe

What doesn't make sense is you want to round
$102.50 and $105.00 round it off to $105.0
(Round to the nearest $2.50

But now you want $212.41 to round all the way down to $200??
(Round to the nearest... ??

And my email is in my signature..

<-*-><-*-><-*-><-*-><-*-><-*-><-*-><-*-
Hope this helps
Anne Tro
Author: Dreamboat on Wor
Email: Dreamboat*at*Piersontech.co
Web: www.TheOfficeExperts.co

 

[Dreamboat]

LOL, Vasant!

Thanks so much. You're perfectly right

Sorry, Klaus

<-*-><-*-><-*-><-*-><-*-><-*-><-*-><-*-
Hope this helps
Anne Tro
Author: Dreamboat on Wor
Email: Dreamboat*at*Piersontech.co
Web: www.TheOfficeExperts.com

 

[shasta]

isn't all this a bit complicated why cn't u just do it form the forma
cells options?:

 

[Alex]

I use the roundup and rounddown funcion alot and find it
very useful. See below

=ROUNDDOWN(K4*M23,0)
you should be able to just replace my "K4*M23" with a sum
formula. Good luck.