i'm either a genius or a time-waster or both

[hermanntrude]

My work requires me to round my students' grades to the nearest 5. Excel didn't seem to have a function which could do this, so I set out to do it for myself. After an hour or so of experimenting, I came up with this function:

=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))))))

where A29 is the cell which contains the grade to be rounded.

My question to you is "was there an easier way?"

for your information, "ceiling" returns a specified number rounded UP to a certain significance (5 in this case), and "floor" rounds down in the same manner. "Trunc" returns a number without any decimals.

 

[darkbeaver]

I don't think so it seems quite straight forward.:smile:

 

[hermanntrude]

i found the easy way:

=round(A29/5,0)*5

 

[darkbeaver]

yes it is shorter isn't it

 

[hermanntrude]

much shorter. but not nearly as much fun

 

[eh1eh]

The first one is the Microsoft solution. The other, Linux. LOL. Metaphorically speaking.

 

[Risus]

Hermanntrude, I would forget the genius aspect....

 

[lone wolf]

Lemme guess ... you've forgotten how to use that "mind's eye" thing?

Woof!