Rounding Problem

[Ride The Lightning]

Hi

I have a set of data that contains values and I have the values rounded to 2 decimal places. However, I need a more expansive way of rounding the data. For example, if the value was 2.23, I'd want the data rounded to the nearest 0.02, so in this case it would be 2.24. I would categorise the rounding values as:

between 1.01 and 1.99, round to nearest 0.01
if it is between 2 and 2.98, round to nearest 0.02
if it is between 3 and 3.95, round to nearest 0.05.

Any help would be appreciated.

Regards

RTL

 

[barry houdini]

You could put that into an MROUND formula, e.g. with data in A2 use this formula in B2

=MROUND(A2,IF(A2>=3,5,IF(A2>=2,2,1))/100)

MROUND is a built-in function in Excel 2007 and later, in earlier versions it's an Analysis ToolPak add-in function

 

[Ride The Lightning]

Thank you, works fine