MULTIPLE IF STATEMENT CONDITIONS

Thanks Matt, works a treat.

A variation on a theme, I have to identify a value based on the contents of two cells and can't get this to work either.

Column A          Column B            Column C
1.Refund                10
2.Sale                    10
3.Sale                    10
4.COJ                      5
5.Sale                     0
6.Sale                     0
7.Refund                 0

In column C I want to appy the following condition.

If A1 = SALE and B1 = 10 THEN 6.50
If A1 = COJ   and B1 = 5 THEN 2
ELSE 0

How do I do this?

Hi guys,

Here's another formula approach. let's assume it's in column B:

=A1*(IF(A1>=110,0.98,IF(A1>=90,0.985,IF(A1>=75,0.99,1))))

This calculates the final price, not the discount. To get the discount, simply subtract the final price in column B from the base price in column C:

=A1-B1  'the order of the columns don't matter..

(Late as always :)

Regards,
Justin

Hi Tim,

You should be able to use the formula:

=IF(AND(TRIM(UPPER(A1))="SALE",B1=10),6.5,IF(AND(TRIM(UPPER(A1))="COJ",B1=5),2,0))

For column C to get your desired result (I added the trim and upper in case your column a was "Sale  " or any variation thereof.

If you wanted C to display "6.50" instead of 6.5, you could use

=IF(AND(TRIM(UPPER(A1))="SALE",B1=10),"6.50",IF(AND(TRIM(UPPER(A1))="COJ",B1=5),2,0))

But that will treat the 6.50 as text.  It would be easier to change the number format on that cell to 0.00 or two decimal places to get it to be 6.50 and be a number.

Hope this helps!
Matt

Oops, sorry Justin, didn't see that there.  Second time today I forgot to refresh before posting!

LOL, Matt we're matching each other in late posts, maybe it those contrails.. :)

Hello All
A bit late on this one but the most flexible solution to your intial question is to use the Vlookup function.
Create a table of your conditions like so:
Value   Discount

0          0%
75        1%
90        1.5%
110      2%

then use the following formula:

=vlookup('value_to_lookup', 'range of table above (excluding headings)',2)

where the final 2 indicates that you want the figure in the 2nd column.

Multiply the result of the formula by the original value to get the amount of discount.

You'll find it better than trying to nest lots of IF's! :-)

Tim

Just reading through your later question.
A technique I've found useful here is to combine columns A and B and that makes the logic easier.

For example, in cell C1 enter the formula A1&B1 to give the result Refund10
Copy this formula down and then do your If-check on column C (you can always hide the column if you think it makes the presentation cluttered)

Cheers

Tony