How to do a multiple expression if statement???

  • Thread starter Thread starter Windowgirl
  • Start date Start date
W

Windowgirl

I need help on how to set up a function that looks at a single cell
(square footage) and based on that determines a price.

If the square footage is less than X the price is X1
If the square footage is more than X but less than Y the price is Y1
If the square footage is more than Y but less than Z the price is Z1
If the square footage is more than R the price is R1

I understand how to do a simple If statement, but I don't know how to
set it up for multiple conditions could someone help me?

Thanks
Window Girl!
 
You can experiment with it, but IF statements inside other if statements are
evaluated in order.

So you can use;

=IF(A1<2,1,IF(A1<3,2,IF(A1<4,3..........etc etc.

When A1 is 1.5, it satisfies the first condition so will result in 1. If
it's 2.2 (between 2 and the next if statement which uses 3), you get 2, so
on. In other words it will keep looking for additional IFs until it has
checked them all. If it satisfies the first, it does not bother checking the
2nd or 7th.

So for "more than x but less than y" -- x would be the value attached to the
first IF statement, and y would be the value attached to the next one.
 
Assuming the square footage is in cell F1:
=IF(F1>RR,R1,IF(AND(F1>Y,F1<Z),Z1,IF(AND(F1>X,F1<Y),Y1,IF(F1<X,X1))))
(I used RR because Excel doesn't like just R (means "row" to Excel)

Bob Umlas
Excel MVP
 
Back
Top