Excel knowledge needed...

[Mithrandir]

I've never been any good with excel and I need something which is (to me at least) quite complicated...

I need a stylesheet to determine the price of something, but I want to give people a discount if they order multiple products.

I'd like to have several colums, 3 with prices (1 price for 1 to 3 products, 1 price for 3 to 6 products and 1 price for above 6 products), 1 with the name of the product and one with the number of products they want to buy (which they can fill in themselves).

If they fill in 5, the sheet has to calculate 5x2nd price colum.

I hope you can 1. make sense of the post above and 2. help me .

[Whacker]

Do you want columns or rows? This doesn't sound impossible, but can I trouble you for some kind of screenshot or mockup drawing of what it is you want, some kind of example? Having trouble visualizing what it is you need, if you can show me then hopefully I can help.

[Togakure]

Hi Mithrandir. I made an example spreadsheet for you, which I can attach and e-mail. If you wish, e-mail me at funkymonk002@hotmail.com and I will send it. I've tested it. It works.



This is the key formula (blue PRICE column):

=IF(AND(B5>0,B5<4),B5*E5,IF(AND(B5>3,B5<7),B5*F5,B5*G5))

Where:

B5 is the quantity
E5, F5, and G5 contain the prices for the three ranges specified.

Your requirements did not provide discrete ranges (1-3 and 3-6 ... which price is in effect if they order 3?), so I adjusted it, guessing at what you really meant. The ranges here are 1-3, 4-6, and 7 or more.

This formula will fail if they enter an integer less than zero (it will use the biggest discount price, G5). I can trap this too, but I didn't in the example. I can if you need it, or maybe you can figure it using the above formula as a reference.

You can paste this down multiple rows to allow for multiple products, but you will have to specify the prices in E-, F-, and G-columns for each product on each row. The customer would simply fill in the quantity.

I didn't bother with totaling as you probably know how to do that. I can demonstrate that too, if you wish.

I hope this is useful.

(I don't know what I was on but somehow the pic had looked oversized to me. Absolutely my bad. Apologies, Masamune and other fellows. Re-inserted the pic into the post - LEN)

[Mithrandir]

You've got mail .

[Togakure]

Back atcha.

[Mithrandir]

Thanks a lot Masamune .