rounding to the nearest 5 in excel

[hermanntrude]

I couldn't find a function to do this, so I used "ceiling" and "floor" together with "trunc", and some nested "if"'s to do it for me... now this is what I got:

 

=IF((A29-(10*(TRUNC((TRUNC(A29))/10))))<2.5,FLOOR(A29,5),(IF((A29-(10*(TRUNC((TRUNC(A29))/10))))<5,

CEILING(A29,5),(IF((A29-(10*(TRUNC((TRUNC(A29))/10))))<7.5,FLOOR(A29,5),CEILING(A29,5))))))

 

my question is... did I waste my time? Is there an easier way? Ceiling and floor round, but only in one direction, so you need the if's to determine which way to round the number. the nested truncs are just to get rid of the numbers at the beginning.

 

The function is only designed to work on numbers between 0 and 100, for rounding students' grades to the nearest 5.

[D H]

=ROUND(a29/5,0)*5

[hermanntrude]

hehehe that's much easier. but nowhere near as much fun!

[D H]

Perhaps you should submit your code to the International Obfuscated Excel Spreadsheet Contest.

 

Hmmm -- there doesn't seem to be one. That doesn't make sense. Obfuscating Excel is even easier than obfuscating Perl. Or C. Or Ruby.

 

Sometimes it is better to be terse. For example, I authored a simple 19 character C program that will perform every computation known to mankind. I still use this program occasionally, mainly to check C and C++ syntax.