AMI Estimator Formula

haileyleek

Hello! Has anyone set up a formula in Smartsheet to calculate the Area Media Income (AMI) Percent? I would like to enter an amount into a row and set up a formula that calculates what percentage of AMI an applicant is. I think it would be a long formula that might include all the different amounts of AMI income. Thank you for your insight!

Scott Orsey

Hi, Do you have a table that lists the AMI for each zip code, MSA or other geographic information? I'm wondering how you know which AMI to use for reference when you are comparing an individual's income to AMI to calculate the percentage. In addition to having a listing of AMI for each area, you would need to know which area each individual resides within. If you know this, the the process would be to

1) Set up a reference sheet listing MSA/geographic identifier in one column, and the current AMI in a second column.

2) Create a column in your main sheet that holds the MSA/geographic identifier.

3) Use INDEX(MATCH) functions to look up the MSA for each row of your main sheet in the reference sheet and return the appropriate AMI.

4) Finally, divide the individual's Income by the AMI to get the percent.

Does this make sense with your situation?

Be well!

haileyleek

Hey Scott! Thank you so much for your fast response. Very helpful! Setting up a reference sheet is a great idea. So if I follow you correctly, the reference sheet and main sheet should have the below table, and then use the match function to find the income limits. Finally, divide the individual's income in the main sheet by the AMI percentage.

Appreciate your insight on this!