Rounding

[stevetton]

if I have columns of numbers
201
202
203
204
205
206
207
208
209
210
ongoing.
I wish to be able to round the last digit to three specifications.. if it is 1 or a 2 then it goes back to 9 i.e. 211 and 212 revert to 209, then if they are 3 , 4, 5, 6 they are rounded to a 5, then if they are 7 or 8 or 9 they become a 9
Can anyone come up with a way of enabling this, where thousands of rows are involved?
Thank you.

[Tony Valko]

Are they ALWAYS 3 digit numbers?

[dluhut]

For 201, is it supposed to be 199?

[dluhut]

Try this formula.

Please Login or Register  to view this content.

[dluhut]

Try this formula.

Assuming your data starts in A1

Please Login or Register  to view this content.

[stevetton]

no they can vary from 2 to 5

[stevetton]

Absolutely Fab

Thank you so much..

Much appreciated