Apr 24 2018
11:26 AM
- last edited on
Jul 25 2018
11:48 AM
by
TechCommunityAP
Apr 24 2018
11:26 AM
- last edited on
Jul 25 2018
11:48 AM
by
TechCommunityAP
Hi,
I am trying to write a formula based on 3 different types of variables. I started by using an extremely long If(and statement and eventually excel wouldn't let me keep going. You can see what I was typing in the sheet I attached.
I believe that I need to use Index / Match for the 3 variables but I'm not sure how to write it. Basically, I want to calculate the Sale price in column CH based on the city (column i), story (column BE), and bedrooms (column R). When these 3 variables match the variables in the assumptions tab I want the value in the assumptions tab to multiply by the square footage (column BA) in the sold tab.
Let me know if this makes sense and how to do this! Thanks
Apr 24 2018 11:53 AM
Hi Brady,
Please test this formula:
=INDEX((assumptions!$C$4:$I$7,assumptions!$C$10:$I$13),MATCH(R2,assumptions!$B$4:$B$7,0),MATCH(I2,assumptions!$C$3:$I$3,0),BE2)*BA2
And find it the attached file.
Regards
Apr 24 2018 11:58 AM
Brady,
=SUMPRODUCT(CHOOSE(BE2,assumptions!$C$4:$I$7,assumptions!$C$10:$I$13)*(assumptions!$B$4:$B$7=R2)*(assumptions!$C$3:$I$3=I2))*BA2