Round up and down in same formula

[harrygill]

Hello everyone
Does anyone know if this rounding is possible within same formula:
If my numbers are
1.62 round down to 1.60
1.65 keep same 1.65
1.67 round up to 1.7

Thanks for help !!

[CK76]

What if it's 1.66?

Typically you'd use MROUND to round to nearest multiple. But in this case you can't use it, since it will round down 1.67 to nearest multiple of 0.05 (i.e. 1.65).

It's best, if you can give multiple example and expected return that captures your logic.

[harrygill]

Thank you for reply, here is what exactly I am hoping for
if 1.61 = round to 1.60
1.62 = 1.60
1.63 = 1.60
1.64 = 1.60
1.65 = 1.65
1.66 = 1.70
1.67 = 1.70
1.68 = 1.70
1.69 = 1.70

Hope this helps!!

[CK76]

May be...
=IF(RIGHT(TEXT(A2,"0.00"),1)="5",A2,ROUND(A2,1))

[harrygill]

It works,
You are Genius!!
Thank you very much for your help.
Appreciate it.

[CK76]

You are welcome.

Just a note, text conversion was used here, as decimal conversion often causes floating point error and will result in inconsistent result.

If you are satisfied with the solution provided. Please mark the thread as solved by using thread tools found at top of your initial post.

[harrygill]

Thank you again,
last question on this
What will be formula for this
if 1.61 = round to 1.65
1.62 = 1.65
1.63 = 1.65
1.64 = 1.65
1.65 = 1.65
1.66 = 1.70
1.67 = 1.70
1.68 = 1.70
1.69 = 1.70

[harrygill]

And formula for
if 1.61 = round to 1.60
1.62 = 1.60
1.63 = 1.60
1.64 = 1.60
1.65 = 1.65
1.66 = 1.65
1.67 = 1.65
1.68 = 1.65
1.69 = 1.65
Really appreciate your help

[CK76]

For that you can just use Ceiling.Math function.

Ex:
=CEILING.MATH(A2,1/20)

EDIT: Oh wait. I misread. Should be FLOOR.MATH
=FLOOR.MATH(A2,1/20)

[MrShorty]

The first looks like a CEILING() function. =CEILING(number,0.05)

The second looks like a FLOOR() function. =FLOOR(number,0.05).

[CK76]

@MrShorty

Ya, I realized that when I was looking at his second question

Edit: Oh wait, didn't see another post he made. That confused me :p

[harrygill]

Thank you both of you.
Is there a way I can manipulate this formula you gave me earlier to get following?
Formula = =IF(RIGHT(TEXT(A2,"0.00"),1)="5",A2,ROUND(A2,1))

Result I expect is
1.61= 1.60
1.62= 1.60
1.63= 1.60
1.64= 1.60
1.65= 1.65
1.66= 1.65
1.67= 1.65
1.68= 1.65
1.69= 1.65
1.70= 1.70

[CK76]

Isn't that same as your post #8?

For post#7 use CEILING.MATH
For post#8 use FLOOR.MATH

Only reason I had to use Text function was because of special treatment of x.x5 in your original question.

[harrygill]

And it worked perfectly fine with my first scenario.

[harrygill]

For some reason its not working not sure what am I doing wrong
my value is
1.649 and to two decimal places it is 1.50 and I apply formula =floor.math(A1,0.05) and result it gives me is 1.60 instead it should be 1.65

[CK76]

1.649 is less than 1.65 so it will evaluate to 1.60 using FLOOR.MATH

You need to first ROUND(Value,2) then nest it in FLOOR.MATH.

But, I'd recommend adding helper column. Rather than doing it in single cell. To visually check that intermediate value is indeed what you expected.

[ChemistB]

Sounds like you want to round first to 2 decimals and then do your custom rounding. SO

=FLOOR.MATH(ROUND(A2,2),1/20)

[harrygill]

You guys are awesome, thank you very much for all the help.
I got everything working now.
I will mark the thread solved.
Cheers!!