Nested Formula ??

[jblair626]

I have three Columns

Type Discount Rate

In the type column It is either "Family" or "Single"
In the discount column it is either "n/a"= no discount or "edu",or "mil" = 10% discount
In the rate column I need a formula that will give me
Family= "100" or 10% off = "90" if mil or edu is in the discount column
Single= "50" or 10% off = "45" if mil or edu is in the discount column

 

[Matty]

Couple of options, perhaps...

Nested ...IF(AND... solution:

=IF(AND(A2="Family",B2="n/a"),100,IF(AND(A2="Family",B2<>"n/a"),90,IF(AND(A2="Single",B2="n/a"),50,45)))

Another way if the Type can only be Family or Single:

=INDEX({100,50},IF(A2="Family",1,2))*IF(B2="n/a",1,0.9)

Matty