If(AND nested statement
I am trying to figure out how to create a nested if/and statement with 5 conditions but I either get an effor telling me it is not a valid equation or it tells me I have too many conditions. Below is the formula.
Essentially I would like the cell to tell me, based on the five conditions, if it is less than or more than fair market rent for the size of unit (0-4 bedrooms) and the corresponding rent standard.
Here is my statement
=IF(AND(H26=0,H25<=591),"Equal to or less than fair market rent",(if(and(H26=1,H25<=642),"Equal to or less than fair market rent", if(and(H26=2,H25<=774),"Equal to or less than fair market rent", if(and(H26=3,H25<=),"Equal to or less than fair market rent", if(and(H26=4,H25<=1268),"Equal to or less than fair market rent"),"Higher than fair market rent"))))
Any advice?
- Tim
- timothy.savage@snhu.edu's blog
- Login or register to post comments
- 2825 reads
Nested statement
Hi,
The problem with your formula is the positioning of some "(" or ")" or their lack.
Here's, the corrected formula:
= IF(AND(H26 = 0, H25 <= 591), "Equal to or less than fair market rent", IF(AND(H26 = 1, H25 <= 642), "Equal to or less than fair market rent", IF(AND(H26 = 2, H25 <= 774), "Equal to or less than fair market rent", IF(AND(H26 = 3, H25 <= 1000), "Equal to or less than fair market rent", IF(AND(H26 = 4, H25 <= 1268), "Equal to or less than fair market rent", "Higher than fair market rent")))))
Please, note that the bolded 1000 is added by me, because in yours post, the value is omitted.
If you have not mind, please, let me to suggest an another solution which do the same like yours, but is shorter and more perspicuous. It is do the same except that I added some check for empty cells, which check you can cut and leave only the second IF statement if you want. Here's, the formula:
= IF(OR(H25 = "", H26 = ""), "", IF(SUM(({0,1,2,3,4} = H26) * ({591,642,774,1000,1268} >= H25)) = 0, "Higher than fair market rent", "Equal to or less than fair market rent"))
Best regards.