Changing Formula based on what is in the target cell

[5upgraphics]

Hello everyone.

I've been working on this one for a few hours and can't quite get it right.

I am developing a spreadsheet for a fundraising program. I give discounts on amount of products ordered. For example: 1-5 products = full price, 6-10 products = 5% discount, 11-20 products = 10% discount, etc.

I'm using reference cells because of the merged cells I have on spreadsheet where the product count is displayed. I've tried the IF, AND, and SUMPRODUCT formulas but no luck.

Any help would be appreciated!

Regards,

Chris

[etaf]

why post links to what looks like advertisements - rather than to an example spreadsheet

[5upgraphics]

why post links to what looks like advertisements - rather than to an example spreadsheet

My intent was not to advertise anything. It is habit for me being a businessman to have links to my company. I actually tried removing the download file before posting but apparently I can't. It wasn't even meant to be a download but just a .jpg below my name.

Thanks for your help though...

Chris

[etaf]

can you post the sample spreadsheet please

[5upgraphics]

can you post the sample spreadsheet please

Sure. Here is a stripped-down version of what I'm doing. I have more questions on it but will save those until I can get the first conditional stuff fixed.

The "Wholesale Total" is where the problem is (K7). I want to calculate the wholesale price according to how many products are sold. It will be a percentage of retail.

Regards,

Chris

fundraising help file.xlsx

[etaf]

so you are applying the discount to the total order - even if they just by 1 of items - the total quantity gets the discount applied

you use a lookup table , then you can change the amounts easily

you also say etc - so to compile the table need the etc. values
i assume the table in O2 to Q19 is the table of discounts

so in K7
=K6*VLOOKUP(K5,Table1,3,TRUE)

should do the job - just check it out - then we can go onto other questions - or they may need another thread , if different

[5upgraphics]

so you are applying the discount to the total order - even if they just by 1 of items - the total quantity gets the discount applied

you use a lookup table , then you can change the amounts easily

you also say etc - so to compile the table need the etc. values
i assume the table in O2 to Q19 is the table of discounts

so in K7
=K6*VLOOKUP(K5,Table1,3,TRUE)

should do the job - just check it out - then we can go onto other questions - or they may need another thread , if different

Well done! It works perfectly!

This may solve another issue I am anticipating along the same lines. I'll start by deciphering our current formula: =K6*VLOOKUP(K5,Table1,3,TRUE)

I understand everything up to the number three. I'm assuming it is column 3 of the table. Is the "True" statement just to enable the function? Is column 2 even needed in this instance?

I'm going to move the table to another sheet in the document. Is there anything I should know or add (i'm also assuming the sheet number before the table)and if there are more than one table on the sheet (which there will be) how do I discern which table number is assigned to which table?

I'm going to be adding a column for up-charges for plus size products. I'll play around with it using the vlookup function and see what I can come up with before asking how to do it

Reps to you!

Chris

[etaf]

yes the sheet name in front for a range
or table name should be ok, as its across the workbook

3 is the column 3 in the table
and "true" is not looking for an exact match - its looking for the lowest nearest match
"false" would be the vlookup needs to find an exact match

another alternative to vlookup is index/match
quite a few online tutorials