Inventory max -formula problem ???

[Abillups]

I have 20,000 items on spreedsheet.
I need to find products that are over/heavy stocked.
Columns are
Product / Package size/ amount on hand / Max usage a day.
Broom / 12 / 53 / 22
pan / 400 / 150 / 65
spoon / 1.5 / 2 / 1
etc.
I tried several if functions but they are not taking in all considerations.
the problem is some items have to be ordered in a certain size.
ex. 1.5 paks and have 2 on hand, my formula says were heavy on stock when its not.

I need a formula that will look at amount on hand, compared to package size , and max use a day. then return if product is heavy on stock.

Company likes stores to have no more than max usage a day on hand.
I was doing if formula on just single product and returning ok, or heavy.
but it isn't always being correct.
any ideals? on a formula, or example!
thanks

 

[shemayisroel]

Would you mind explaining a little
on how you set this up
and why you picked those packages sizes. etc.

just want to make sure i understand so i can duplicate
this for other projects.

thanks!

Your welcome.

I'll try and word it. Why I picked those numbers? Um those are just my guess, however you can modify to what you want them to be.

=IF(AND(C2>=100,D2>2,E2<=20),"OK",

Condition 1 If the package size is greater than or equal to 100 AND theStock on hand greater than 2 AND Max daily usage is less than or equal to 20. Then if ALL of those conditions are met display "OK"

IF(D2<=2,"OK"

Condition 2 If Stock on hand is less than or equal to 2 then display "OK"

,"HEAVY"))

Condition 3

Anything else then display "HEAVY"

Does that help?

 

[Abillups]

yes, thanks
I just wasn't sure how that formula worked for the ones that didn't have bottle of 100 etc.
when dragging down the formula.
It seems to work on the others also.
even if the their packag size is not 100.
So that is what i couldn't figure out most?

 

[shemayisroel]

No probs, so is the solution what you were looking for?

 

[Abillups]

yes , thanks