| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula with IF
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula with IF
Use the CEILING function.
-- David Biddulph "Roxie" wrote in message ... 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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula with IF
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" wrote: 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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula with IF
Wow, I have no idea what a CEILING function is.
"David Biddulph" wrote: Use the CEILING function. -- David Biddulph "Roxie" wrote in message ... 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? . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula with IF
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. -- David Biddulph "Roxie" wrote in message ... Wow, I have no idea what a CEILING function is. "David Biddulph" wrote: Use the CEILING function. -- David Biddulph "Roxie" wrote in message ... 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? . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula with IF
Thank you so much! That worked perfectly. I appreciate the help.
"Billy Liddel" wrote: 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" wrote: 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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula with IF
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) -- David Biddulph "Roxie" wrote in message ... Thank you so much! That worked perfectly. I appreciate the help. "Billy Liddel" wrote: 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" wrote: 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
