Rounding to non 10's i.e round to either 35, 45, 55 or 65

[stuartsjg]

Hi,

Not sure entirely how to describe my problem, its probably easiest to show what i have - i have the following data, for example:

33.2
37.2
41.8
42.6
46.1
48.4
53.4
56.9
61.5
65.2

and i want to round it such that result is either 35, 45, 55 or 65:

33.2 - 35
37.2 - 35
41.8 - 45
42.6 - 45
46.1 - 45
48.4 - 45
53.4 - 55
56.9 - 55
61.5 - 65
65.2 - 65

Normal rounding methods will give me in between numbers such as 30, 40, 50 or 60 which i don't want.

I can do a nested IF statement if that the only option, but im sure there must be a rounding equation which can do this. There are 18,000 data points so doing it by hand is out of the question!

Any ideas anybody?

Thanks
Stuart

[daddylonglegs]

For your sample this formula should work

=ROUND(A1+5,-1)-5

[stuartsjg]

Ah - thats brilliant, and very simple too. Appears to work on the full set of data.

Many thanks

[Armando Montes]

Option: =FLOOR(A1,10)+5