Need a formula for a simple inventory worksheet...

Hello. New to forum, but not to excel. This should be pretty easy for you brainiaks out there...

I created a simple inventory spreadsheet for our 50 some items that we keep on our shelves. It contains the following columns: "Inventory ID", "Name", and "Description". No brainer thus far.

The next columns contain "Qty. in Stock (cell D3)", "Par (cell E3)", "Shipping Qty. (cell F3)", "Shipping Units (cell G3)", & "Qty. to Reorder (cell H3)", and this is where I'm getting stuck.

Lets say that the "Qty. in Stock (cell D3)" is 5ea. Then lets say that the "Par (cell E3)" is 12ea. Easy math says that I need to order 7ea. to bring our stock up to par. But lets say that the item's "Shipping Qty. (cell F3)" is a case of 12ea. In that case I could either order a case now, or wait until next week to order. The formula that I am using currently for this is:

Cell J3:

Please Login or Register  to view this content.

...and
Cell K3:

Please Login or Register  to view this content.

The first line of code (cell J3) tells me that if the "Qty. in Stock" is less than "Par", order 1cs.. If it is not, than order 0cs.. (Obviously there's a problem here because if the "Qty. in Stock" is 0ea., and the "Shipping Qty." changes to 6ea., then we should be ordering 2cs., and NOT 1cs.)

The second line of code (cell K3) shows me the difference between the "Par" and the "Shipping Qty.". So if "Par" is 12ea., and the "Shipping Qty." is 6ea., it would show an order of 2cs.

The last bit of formula I am using is this:

Cell H3:

Please Login or Register  to view this content.

This line of code (cell H3) multiplies cells J3 (formula #1) and K3 (formula #2). (This also seems to be a problem because it is not doing what I thought that it would. For example, if the "Qty. in Stock" is 0ea., and the "Par" is 12ea., and the "Shipping Qty." is 6ea., this would tell me to order 2cs.)

The problem is as I change the numbers around in the different columns, they just aren't working right. The math isn't working right.

Can anyone please tell me what I am doing wrong here??? I think that my brain is just fried from the weekend!!!

Thanks in advance for the help,

DG

OK, over lunch I was thinking about this. What if I added two more columns... "Shelf Full at..." & "Reorder Level". That way we could see how many MAX the shelf would hold, and the second would show the lowest we could go before "having to" reorder.

Thoughts...

DG