AMI Estimator Formula

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
    Scott Orsey ✭✭✭✭✭
    edited 03/18/24

    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!

    If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!

  • 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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!