Formula for rounding to the nearest nickel

[itjr212]

Hello,

Is there a formula that can round numbers to the nearest nickel?

I am trying to get the following:

Numbers Result
11.19 11.20
11.87 11.90
9.72 9.70

Also, is there a way to filter on any numbers that don't end in 0 or 5? I am trying to round off a large number of data and the formulas I am trying are not showing me the result I am looking for. Any help would be greatly appreciated

Thanks,

itjr212

 

[Janb]

Hi,

You can try =ROUND(A1;1) / The formula is in cell B1.

Format of cel B1 is number with two digits.

There's maybe a smarter way, but this works. Don't know the filter question.


A1 11,11 B1 11,10
A2 11,12 B2 11,10 etc.
11,13 11,10
11,14 11,10
11,15 11,20
11,16 11,20
11,17 11,20
11,18 11,20
11,19 11,20
11,2 11,20

 

[popfan]

Well this might be a little convoluted, but it works well.

=IF(0.01*RIGHT(E11,1)< 0.05,E11-(0.01*RIGHT(E11,1)),E11-(0.01*RIGHT(E11,1))+0.05)

If you are filtering in a table the just used the filter under Home - Editing - Sort - Filtering and use the number filters to filter where equal to 0 or 5

 

[Jonmo1]

Actually, you're title/request doesn't match the sample you gave...

The nearest nickel to 11.87 is 11.85 (NOT 11.9)

Your example seems that you want the nearest DIME...


For nearest Nickel
=ROUND(A1/5,2)*5

For nearest Dime
=ROUND(A1/10,2)*10


Hope that helps.

 

[T. Valko]

Hello,

Is there a formula that can round numbers to the nearest nickel?

I am trying to get the following:

Numbers Result
11.19 11.20
11.87 11.90
9.72 9.70

Also, is there a way to filter on any numbers that don't end in 0 or 5? I am trying to round off a large number of data and the formulas I am trying are not showing me the result I am looking for. Any help would be greatly appreciated

Thanks,

itjr212

If wanting to round to the nearest nickel shouldn't 11.87 round down to 11.85?

This will round to the NEAREST nickel:

=ROUND(A2/0.05,0)*0.05

I'm not sure about your filter question. That might be a bit complicated. In Excel a number like 9.70 doesn't actually end with 0. It ends with 7. The 0 is there just as a formatting style and is for display purposes only.

 

[T. Valko]

....

I'm not stalking you today!

 

[Jonmo1]

In Excel a number like 9.70 doesn't actually end with 0. It ends with 7. The 0 is there just as a formatting style and is for display purposes only.

In that context, EVERY number ends with 0...

I'm not stalking you today!

Other days yes, but not today right?