Multiple price for items 1 = $25, 5-15=$22, 16 or more=$20????

[93*Corvette]

Hi all,
Just found this forum. Thanks in advance for any advise you can provide now or in the future.

The problem:
I'm working on a supply database. I have seperate cells titles "What I need to Order", "On-hand Qty", "Low Limit", and "High Limit".
The on-hand qty will be entered, and

• If it below the low limit it will inform you with "Order now".

• If the On-hand is higher than the High Limit you will be informed "Over Stocked".

• If the On-hand is between the Low and High Limit it will show "Good Sparing".

I can get it to do everything to this point. I can subtact the On-hand from the High Limit to tell me "What I need" cell.
Now the problem, I want it to calcualate the cost. Suppose my item is 1=$25, 5-15=$22, 16 or more=$20.
I want my "What I need" to compare prices for the price breaks. i.e. If I need 10 items then it would be (10 * $22=$220), or (20 * $20=$400).

Last hitch... My items are not a set percentage. They are just different prices for all the items. I want the formula to be universal with all the cells. i.e. have seperate cells showing the diffent price breaks so a non-excel expert (my kids) can just fool around with the data in the cells and not with the formula. P.S. By no mean, do I call myself an expert. I'm just a novice too.
Make sense?

[Carim]

Hi,

Attached is a sample ...

[93*Corvette]

After I saw your post, I realized that I forgot one of the most important items. I can't use a VLOOKUP (or should I say, I don't think I can).... I need my various prices in one row only. I currently have only 1 price....
I have added the worksheet here....

[Carim]

Hi,

I am afraid your new spreadsheet is not clear enough, at least for me ...
I do not see iis relationship with your initial request ...
Would you mind explaining again your objective ...

[93*Corvette]

Hi,

I am afraid your new spreadsheet is not clear enough, at least for me ...
I do not see iis relationship with your initial request ...
Would you mind explaining again your objective ...

Sorry about the confusion. Yes the relationship remains the same, I'm just having a hard time explaining it because I don't know the proper programming terms. Here it is again,
In my example I have in cell A4 I have 3 items I need to order. I will multiply cell A4 (3) by cell M4 ($26.95) to get my extended total. This should be $80.85 but I have created this yet because this is where I'm stuck.

Suppose the item in forth row has a price break. Suppose you could order 1 item for $26.95 (Cell M4), then the guy says "If you buy 2, I'll sell two for $22.00 each, and if you decide to buy 3, I'll let them go for $20.00 each". I want to be able to have these price breaks located in the same line as the item (example row four)... I can't use VLOOKUP since it is using multiple cell to accomplish the task (I think).
Price breaks will be associated to all rows/items.
Make sense? Thanks for any help....

[Carim]

Hi Craig,

May be this attempt is closer to your objective ...