Floor & Ceiling Problem When Value Is Exactly As Spec'd Category

[Dal123]

Probably easier if I show a picture first:

1.jpg

I have a single number in the height column need to display it's height classification to the nearest 0.50; but the final value (last 2 digits in the B column) should always be the highest & the first 3 digits in column B should never fall into the same classification as column B.

Please Login or Register  to view this content.

Works perfectly in the first 2 rows however it falls on problems in row 4. Anyone have an idea of the most efficient solution?

[Melvosh]

Try:

Please Login or Register  to view this content.

Not sure if it'll work in every situation.

[Roel Jongman]

try:

Please Login or Register  to view this content.

This extra function will add 0,5 if the value is whole number.
you could also put it as a substract in the floor function if you want whole numbers to be the end of the range.

[Dal123]

Thanks Melvosh, where do you think it could fall short?

I just come up with this & I think it may be foolproof?

Please Login or Register  to view this content.

[Bo_Ry]

How about

=FIXED(FLOOR(A2,0.5),2)&" - "&FIXED(CEILING(A2+0.001,0.5),2)

[Dal123]

Thanks Bo_Ry, I think that would push the higher category 0.50 higher. I think I solved it with deducting the floor value. Thanks again guys, it's typical, stuck on it for a couple of hours and as soon as you post you figure something out .