Formula with IF

[Roxie]

I have a liquor inventory worksheet that I am trying to get to give me an
order amount based on pars. I have this:

On Hand Liquor is E
Par is column B

I currently have this as a formula:

=IF(E36<B36,24,0)

This part works and I order 24. The problem I have is ordering beer. It
comes in cases which are 24 to a case. So, I need to order 24, 48, 96 etc. If
B36 is >24 but <48 I will have to order 48. If it is >48 but <72 I need to
order 72.

Can anyone help me with this?

 

[David Biddulph]

Use the CEILING function.

 

[Billy Liddel]

Try this

=IF(E36<B36,ROUNDUP((B36-E36)/24,0))*24

If you order in cases then remove the *24 at the end

HTH
Peter

 

[Roxie]

Wow, I have no idea what a CEILING function is.

Use the CEILING function.
--
David Biddulph




.

 

[David Biddulph]

I won't say RTFM, but the function is covered in Excel help, just as is
every other Excel function but one. Excel help will tell you the syntax,
give you examples of its use, and there is usually a "See Also" link to show
you related functions and thus perhaps point you at other useful Excel
functions of which you had hitherto been unaware. Do have a look.

 

[Roxie]

Thank you so much! That worked perfectly. I appreciate the help.

 

[David Biddulph]

If that's the functionality you want, then (to follow up your earlier
question about the CEILING function), you could simplify
=IF(E36<B36,ROUNDUP((B36-E36)/24,0))*24
to
=IF(E36<B36,CEILING(B36-E36,24),0)