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

**93*Corvette** · 01-19-2007, 11:13 AM

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** · 01-19-2007, 11:24 AM

Hi,

Attached is a sample ...

**93*Corvette** · 01-20-2007, 02:29 AM

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** · 01-20-2007, 04:25 AM

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** · 01-21-2007, 06:44 AM

Originally Posted by 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 ...

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** · 01-21-2007, 11:55 AM

Hi Craig,

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

**93*Corvette** · 01-22-2007, 02:34 AM

Originally Posted by Carim

Hi Craig,

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

Carim, 1st of all, Thanks! I think you almost got it. I know my explanations are alot to be desired. Sorry about not providing the complete picture...

What I'm not sure of now is suppose you have different price breaks. Suppose I have others at 10,30 or 90 or any other amounts?
Not every item will have the same price break, Not every item will be together (ie 5, 30, 90) items.
The only thing I think that might work is finding all the price break values, then just keep adding them (?) into new columns.
Here is my updated example. What do you think?

**Carim** · 01-22-2007, 03:43 AM

Well ... if price breaks are an issue on their own, I would recommend you create a dedicated sheet "Price Breaks", where you would replicate your product list and only manage price breaks specifically ...
Your initial sheet will, then, lookup from this new sheet ...

**93*Corvette** · 01-22-2007, 04:04 AM

Originally Posted by Carim

Well ... if price breaks are an issue on their own, I would recommend you create a dedicated sheet "Price Breaks", where you would replicate your product list and only manage price breaks specifically ...
Your initial sheet will, then, lookup from this new sheet ...

Thanks... Your data is great... I have already updated my master copy with your formula..... Thanks again for all the help you provided me.....

**Carim** · 01-22-2007, 04:06 AM

You are welcome ...

Thanks for the feedback

**93*Corvette** · 01-22-2007, 04:34 AM

Carim,
Sorry to ask this, but with your formula you made, I will use this to find the total Sum ($$$) of the cost of the items. How do you SUM the column that is truly just formulas??
I though you could say SUM(M3:M14) But since the value in these cells are the results of the formula it doesn't work.... I used the Search button at the top and seached on "Formula SUM", "SUM of a Formula"...etc... I'm not sure the search I would need to do....
Thanks....
Craig

**Carim** · 01-22-2007, 12:01 PM

Hi again,

Do not worry ...
=SUM(M3:M14)
will work fine ...

**93*Corvette** · 01-22-2007, 12:28 PM

Originally Posted by Carim

Hi again,

Do not worry ...
=SUM(M3:M14)
will work fine ...

I tried that, but it doesn't work. It keep coming up with #Value!

I double checked that the cells were "Currency", so it should produce a number. Since the cells are formulas, I think Excel doesn't realize its the data of the formula I need. It appears the cell thinks it's a formula and not the data that it produces.... I made the cell really wide to make sure that the cell was large enough for the number to display.

**Carim** · 01-22-2007, 12:31 PM

See attached...

**mikev08826** · 01-22-2007, 08:04 PM

Hi.

Noob here. Just working on advancing my excel skills.

Question: What does this part circled in read mean???

http://i112.photobucket.com/albums/n...8826/excel.jpg

**93*Corvette** · 01-22-2007, 09:54 PM

Originally Posted by mikev08826

Hi.

Noob here. Just working on advancing my excel skills.

Question: What does this part circled in read mean???

http://i112.photobucket.com/albums/n...8826/excel.jpg

I actually change that statement to:
=IF((OR(C4="",D4="",E4="")),"-----",IF(C4<D4,"Time to order",IF(C4>E4,"Over Stocked","Good Sparing")))

orginally it was:
=IF(C4="","-----",IF(D4="","-----",IF(E4="","-----",IF(C4<D4,"Time to order",IF(C4>E4,"Over Stocked","Good Sparing")))))

I should have use a "OR" statement to begin with, but I was just trying to get the thing to work 1st. The 1st part if C4=''" is looking for a blank, if true, then it will output "-----" in position C4. If false, it goes and looks at cell D4 and checks the same thing, then checks for E4. I will send the whole workbook on next responce since I'm having a problem explaining my sum question.

**93*Corvette** · 01-22-2007, 10:56 PM

Originally Posted by Carim

See attached...

Carim,
I found my mistake. In the last example, column N. This is the price break for 0 items. I didn't have a price for one of my items. In column N, I wrote "Price" to help me remember to fill in the block with the current price.
With Text, instead of a number, I got #VALUE!. I should have put a dummy number in like 99999.99 to show that a number was needed. This is what was causing my SUM not to add....

So.... I think I'm done with my 1st sheet..... Thanks again, I really appreciate your help.