Formulars

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

I am trying to create an inventory spreadsheet and have
all the formulars complete except one which I am having
problems with.

Here's an example...

We stock Coke and Diet Coke in cans. We purchase them by
the flat of 24 cans. When we do our weekly inventory, I
would like our stores to count the number of cans and put
that number in the inventory column on the spreadsheet. I
then have a column that is our par of what we want to have
in the store. Then the par column substracts the
inventory column and puts the answer in the Order column.
But, it's not apples to apples. We count cans, let's say
we have 30 in the store. The par is 72. We need to order
42 cans but only order them by the case of 24. So I want
an IF formular that takes the results and divides it by 24
and then rounds up or down according. i.e. if we need 42
cans that is almost 2 cases so the results should be 2
cases. I would like to say that if we need 12 or more
cans that it rounds up to 1 case of 24 and if we need less
than 12 cans it rounds down. Any help?
 
Peter,

If the number of cans needed is in cell A1, the number of cases to
order can be found with

=MROUND(A1,24)/24

Note that MROUND is part of the Analysis Tool Pack, so you'll need
this add-in loaded. Go to the Tools menu, choose Add-Ins and put a
check next to 'Analysis Tool Pak'.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
If this is just for ordering purposes and not going to be linked to
other documents, this will give you the visual that you want.


If you don't want to get too complicated:

Format your target cell as a number with no decimal places


A1 = On hand
B1 = Par
C1 = Cases needed.

In C1 enter ---> =(B1-A1)/24
 
=ROUND((B1-(A1/24)),0) worked best for me.. Where B1 is the Par, and A1 is
the number of Cans.
HTH
 
This formula didn't really work. The first response gave
me the exact answer I was looking for. Just wanted you to
know!

Thanks though.

P
 
Try this:
=IF(OR(ISBLANK(A7),ISBLANK(B7)),"",IF(ROUNDUP((B7-A7)/24,0)<0,0,ROUNDUP((B7-
A7)/24,0)))

HTH.
 
Back
Top