Having problem with Par levels, help please !

kadziu

Board Regular
Joined
Jan 2, 2007
Messages
69
Hi,

I have calculated the par levels for weekly ordering and thats fine.
I am trying to figure out a formula which can calculate me the amount to be ordered on the basis of the closing stock on a Sunday night.

For example :

Par level for Anchovies is 35 pkts ( in cell c3)
Closing stock for Anchovies is 24 ( this is on another sheet called "average use")
It would be easy to calculate 35-24 , however the case size for Anchovies is 24 pcs in one box. I cannot order less.
So if the quantity goes below 35 pkts, what formula would give me the figure "1" ( which would mean 1 box) in cell D3 , where I want the "To order" quantity to be.
Makes sense.??

I have a whole list of about 100 items with different case sizes. It would be fantastic , if I could just feed the closing stock and it calculates me what to order, in the particular case size

ANy help would be highly appreciated

Many thanks
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
kadziu

You haven't told us that much about the layout and where the result is to go, but see if this is some use. If it is not what you want, post back with more details and consider posting some screen shots.

Excel Workbook
ABC
1ProductCase SizeClosing Stock
2Anchovies2424
3Lettuce1222
4Peanuts558
average use



Excel Workbook
ABCD
1ProductParOrder
2
3Anchovies351
4Peanuts805
5Lettuce200
Order
 

kadziu

Board Regular
Joined
Jan 2, 2007
Messages
69
Hi Peter ,

Thanks very much for this, I was trying to post a screen shot, but my html maker is not working somehow. Yes I think you got my point and I will try your formula and see if it works

Many thanks

Mike
 

kadziu

Board Regular
Joined
Jan 2, 2007
Messages
69

ADVERTISEMENT

Hi again,

I have exactly the same layout as you have given above, with the same cell numbers, however it is giving me a N/A error?? I cant seem to figure out why.
 

kadziu

Board Regular
Joined
Jan 2, 2007
Messages
69
As far as htmlmaker is concerned, it is VBhtmlmaker, I used to use it without problems, I can see it in add ins , but it does not come up on the menu for some reason
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi again,

I have exactly the same layout as you have given above, with the same cell numbers, however it is giving me a N/A error?? I cant seem to figure out why.
1. Do you also have exactly the same sheet names? The names are shown just above the screen shots. The critical one is "average use" because that is referred to in the formulas.
2. Did you copy and paste my formulas from the board, or type them in manually? If typed, re-check carefully and/or cosider a copy/paste from the board.
 

kadziu

Board Regular
Joined
Jan 2, 2007
Messages
69
I have the exactly the same names and I have copied pasted and double checked twice , everything matches , but still n/A?? Could it have anything to do with the fact that the par level 35 is a result of "=$W$1*'Average use'!H2". Should not make a diff , should it ??
 

kadziu

Board Regular
Joined
Jan 2, 2007
Messages
69
sorry , got it , Anchovies were spelt wrong in one of the sheets :)))

Thanks for the patience , it should work now
 

Watch MrExcel Video

Forum statistics

Threads
1,127,562
Messages
5,625,525
Members
416,116
Latest member
Joemamasuka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top