Round numbers based on last digit

[nchiari]

Hi again guys! Thanks to you I'm optimizing a lot my worksheet! I have another question to ask you.

AQ2: =(AO2-AP2). That ecuation gives me a result that I need to round with the next criteria:

• If the result ends with the digits: 2, 3, 4, round it to end with 5 (ie. 102, 103, 104, round to 105)
• If the result ends with the digits: 7, 8, 9, round it to end with the next 0 (ie. 107, 108, 109, round to 110)
• If the result ends with the digit: 1, round it to end with the previous 0 (ie. 101, round to 100)
• If the result ends with the digit: 6, round it to end with the previous 5 (ie. 106, round to 105)

I googled it a bit and found this formula, but I don't know how to change it to work for my case:

=AQ2+CHOOSE(MOD(AQ2;10)+1;-1;-2;-3;1;0;0;3;2;1;0)

Thanks again!!

 

[nchiari]

PS: remember that I also need that the same cell with the round formula first have a (XX-XX) formula to obtain the number who's gonna be rounded.

 

[Jonmo1]

Try

=CEILING((XX-XX)-1,5)

 

[nchiari]

Wow! Work like charm! Thank you very very much!!

 

[XOR LX]

Hi.

=MROUND((XX-XX)+1,5)

Regards

 

[Jonmo1]

Wow! Work like charm! Thank you very very much!!

You're welcome.

=MROUND((XX-XX)+1,5)

Always more than one way to skin a cat.

 

[XOR LX]

Always more than one way to skin a cat.

Indeed!

 

[nchiari]

Lol thanks guys! If I knew about this forum before, my work day would have been a lot easier