Asking for Formula Help

[ChrisHockley]

Guys, I'm always amazed at how much help everyone is here and how experienced those that are here actually are!

I'm trying to round numbers with two decimal places to one decimal place.

Here are the parameters:
1. Anything 3.54 and below should be rounded up or down to the nearest tenth or 0.1
2. Anything between 3.54 and 28.34 should be rounded up or down to the nearest 0.5
3. Anything 28.35 or above should be rounded up or down to the nearest 1.0

Can this be done in one formula so that I don't have to copy and paste multiple versions over thousands of lines?

Thanks in advance!

 

[ChrisHockley]

I've tried the following but anything 28.35 or above returns a 'FALSE'

=IF(D296<=3.54,MROUND(D296,0.1),IF(D296<=28.34,MROUND(D296,0.5),IF(D296>=28.35,MROUND(D296,1))))

 

[Trebor76]

Hi Chris,

This seems to work for me:

=IF(D296<=3.54,MROUND(D296,0.1),IF(AND(D296>3.54,D296<=28.34),MROUND(D296,0.5),MROUND(D296,1)))/CODE]

The last part is for any number that does fall into any of the previous categories of the formula.

 

[ChrisHockley]

=IF(D296<=3.54,MROUND(D296,0.1),IF(AND(D296>3.54,D296<=28.34),MROUND(D296,0.5),IF(D296>=28.35,MROUND(D296,1))))

Maybe it's a formatting issue? My still returns FALSE with your formula...

 

[ChrisHockley]

This is the strangest thing... that same cell that is returning the false has 28.35 as the number. =MROUND(D296,0.1) is rounding to 28.3. Clearly it should round to 28.4.

Is there a way to change the rounding parameter to be anything .5, .05, .005 etc should round up and not down?

 

[Trebor76]

Not sure as it seems to be working for me. Are the calculation options (Calculations tab in the Formulas ribbon) set to Automatic?

 

[ChrisHockley]

Not sure as it seems to be working for me. Are the calculation options (Calculations tab in the Formulas ribbon) set to Automatic?

Yes it is. For some reason its rounding the 28.35 down to 28.3 instead of up to 28.4 as per normal round rules. I love excel at times.... lol

 

[Trebor76]

If you increase the decimal by multiple clicking the left pointing arrow in the Number tab of the Home ribbon what does show?

 

[ChrisHockley]

If you increase the decimal by multiple clicking the left pointing arrow in the Number tab of the Home ribbon what does show?

28.3 - its not rounding up on the 5 here
28.35
28.350 - is seems to have rounded up on the 5 here
28.3495
28.34952

 

[Trebor76]

I think because the limits are to two decimal places we need to round each number by two decimal places like so:

=IF(ROUND(D296,2)<=3.54,MROUND(D296,0.1),IF(AND(ROUND(D296,2)>3.54,ROUND(D296,2)<=28.34),MROUND(D296,0.5),IF(ROUND(D296,2)>=28.35,MROUND(D296,1),0)))