Multiple conditional statements in one cell

[mkester]

Hello! I am using Excel 2010 and I am currently using the formula

IF([@[HH SIZE]]=1, IF([@[Summary Total_Monthly Income]=]=>4452, "High", IF([@[Summary Total_Monthly Income]=]=>2971, "Moderate", IF([@[Summary Total_Monthly Income]=]=>4, "Low", "NA"))))

to determine whether an individual has "High" "Moderate" or "Low" income based on household size. Each household size has different income levels and this formula only pertains to HH sizes of 1. How can I include HH sizes of 2,3 etc with different income limits into a one cell formula. Currently I just manually change the formula by replacing HH size of 1 with 2 and income limits of 4452 and 2971 to 5088 and 3396, respectively and am wondering if I can consolidate it all into one formula.

 

[skorpionkz]

nesting? Just change the income limits as required.


IF([@[HH SIZE]]=1,IF([@[Summary Total_Monthly Income]=]=>4452, "High", IF([@[Summary Total_Monthly Income]=]=>2971, "Moderate", IF([@[Summary Total_Monthly Income]=]=>4, "Low", "NA"))),IF([@[HH SIZE]]=2,IF([@[Summary Total_Monthly Income]=]=>4452, "High", IF([@[Summary Total_Monthly Income]=]=>2971, "Moderate", IF([@[Summary Total_Monthly Income]=]=>4, "Low", "NA"))),IF([@[HH SIZE]]=3,IF([@[Summary Total_Monthly Income]=]=>4452, "High", IF([@[Summary Total_Monthly Income]=]=>2971, "Moderate", IF([@[Summary Total_Monthly Income]=]=>4, "Low", "NA"))))))