Round number up to nearest whole 5 or 9

[ChalkyExcel]

I have a sheet with the following numbers:

1012
2037
1083
1080

I would like the numbers above the be rounded up to the nearest 5 or 9. So for the example above the finished result would be:

1015
2039
1085
1079

Any help very much appreciated. Thank you.

 

[Peter_SSs]

Welcome to the MrExcel board!

Your thread title and problem examples do not match. Your thread title asks for numbers to be rounded up, but your final example is rounded down. Please clarify.

 

[AliGW]

This works for your sample data:

=IF(RIGHT(A1,1)="0",A1-1,IF(RIGHT(A1,1)<="4",A1+(5-RIGHT(A1,1)),A1+(9-RIGHT(A1,1))))

 

[ChalkyExcel]

Welcome to the MrExcel board!

Your thread title and problem examples do not match. Your thread title asks for numbers to be rounded up, but your final example is rounded down. Please clarify.

Apologies - yes rounded up to nearest 5 or 9 please.

 

[AliGW]

OK. Try this, then:

=IF(RIGHT(A1,1)<="4",A1+(5-RIGHT(A1,1)),A1+(9-RIGHT(A1,1)))

or perhaps this:

=IF(RIGHT(A1,1)="0",A1,IF(RIGHT(A1,1)<="4",A1+(5-RIGHT(A1,1)),A1+(9-RIGHT(A1,1))))

 

[ChalkyExcel]

OK. Try this, then:

=IF(RIGHT(A1,1)<="4",A1+(5-RIGHT(A1,1)),A1+(9-RIGHT(A1,1)))

This one works a charm thank you very much!

 

[AliGW]

No problem!

 

[Peter_SSs]

This one works a charm thank you very much!

Hmm, I would have thought that a number that already ends with a 5 or a 9 would not be changed - at least that is how 'normal' rounding works. Those formulas do not change a number that ends in 9 but they do change numbers that end in 5.

If you did in fact want to leave such numbers unchanged, one way would be.

=A1+CHOOSE(RIGHT(A1,1)+1,5,4,3,2,1,0,3,2,1,0)

 

[AliGW]

Those formulas do not change a number that ends in 9 but they do change numbers that end in 5.

That's true, but could easily be amended thus:

=IF(RIGHT(A1,1)="5",A1+4,IF(RIGHT(A1,1)<="4",A1+(5-RIGHT(A1,1)),A1+(9-RIGHT(A1,1))))

 

[Peter_SSs]

That's true, but could easily be amended thus:

=IF(RIGHT(A1,1)="5",A1+4,IF(RIGHT(A1,1)<="4",A1+(5-RIGHT(A1,1)),A1+(9-RIGHT(A1,1))))

I'm not sure what is different about that, it still rounds 1085 to 1089.