Rounding a formula result?

[rudyeb]

Here is the cell formula currently: =sum(e5/5)
Question: What do I add to the formula to have the result always roundup to
the nearest number divisible by 5?

The formula takes a room area and divides it by 5 and this will determine
how many 5 gal. pails of paint I need to paint the room. Since I can only
buy the paint in 5 gal. pails, I want the result to always round up to a
number divisible by 5. Example; 8.3 would round up to 10
Thank you,
Rudy

 

[Jerry W. Lewis]

=E5/5 is sufficient, you don't need to wrap it in the SUM() function.

With the Analysis ToolPak installed,
=MROUND(E5/5,5)
will give what you want.

Alternately, you could use
=ROUND( (E5/5)/5, 0)*5
or more simply
=ROUND(E5/25,0)*5
which do not require the Analysis ToolPak

Jerry

 

[Gerrit-Jan Linker]

Hi,

I would use int(e5/5) + if(mod(e5,5)=0,0,1)

Explanation:

Divide by 5 and through away the decimals.
Add nothing if there were not any decimals otherwise add 1

e5 = 5: 1
e5 = 8: 2

Best regards, Gerrit-Jan Linker
Developer of LITlib: Excel Power Functions Library
www.oraxcel.com/projects/litlib