Round Numbers To The Nearest Half Based on 3 Specific Range of Decimals

[HobbesIsReal]

Rounding traditionally to the nearest half is easy.....But I need to round numbers to the nearest half based on a specific range of decimals in the original number. For example if the number in D6 is:


28.0 to 28.399 then rounds DOWN to 28

28.4 to 28.799 then rounds to 28.5

28.8 to 28.99 then rounds UP to 29


I need to do this without VBA....


Thank you for any help and insights!

 

[HobbesIsReal]

Got the answer from a helpful poster in another forum.....


=int(a1) + lookup(round(mod(a1,1), 6), {0,4,8}/10, {0,5,10}/10)

 

[bobhc]

Good day HobbesIsReal


Pleased you have the answer you want..............but more importantly the forum will be pleased you took the time and trouble to tell and show your answer

 

[NARAYANK991]

Hi ,


That is the answer of an expert.


For a layman's answer , try this :


=IF(MOD(A1,1)<0.4,INT(A1),IF(MOD(A1,1)<0.8,INT(A1)+0.5,ROUNDUP(A1,0)))


Note : Both the formula which has been mentioned in the other forum , and the one I have posted , do not work to the specification for negative numbers.


Narayan