# Having problem with Par levels, help please !

##### Board Regular
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

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### Peter_SSs

##### MrExcel MVP, Moderator

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

##### Board Regular
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

#### Peter_SSs

##### Board Regular

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.

##### Board Regular
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

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.

##### Board Regular
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 ??

##### Board Regular
sorry , got it , Anchovies were spelt wrong in one of the sheets ))

Thanks for the patience , it should work now

Replies
161
Views
1K
Replies
2
Views
897
Replies
1
Views
90
Replies
0
Views
682
Replies
1
Views
134

1,127,199
Messages
5,623,321
Members
415,966
Latest member
ctorohuamanchumo

### 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.

### Which adblocker are you using?

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

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