dhansen_
asked on
Excel Formula Help - Benchmark Subset of Array Against Another Subset in Same Array
Trying to take the average difference between market values in one metro versus the national average. Both national data and the metro data are in the same data set, so I have been attempting to use multiple criteria in an "AVERAGE(IF(..." format. Cannot use VBA. Current formula is:
{=AVERAGE(IF((MSA="METRO NAME)*(YYYYQ>=20001)*(YYYY Q<=20161)= 1,MVperSF_ SF_Wtd-(MS A="NATION" )*(YYYYQ>= 20001)*(YY YYQ<=20161 )*MVperSF_ SF_Wtd,FAL SE))}
Where "MVperSF_SF_Wtd" is the value-weighted market value per square foot for an apartment building. The results are just wrong, so looking for some help.
I have attached the file for further clarity. Please see column "R".
Thanks for the help!!!
2016Q1-NCREIF-Value-psf---Apt.xlsx
{=AVERAGE(IF((MSA="METRO NAME)*(YYYYQ>=20001)*(YYYY
Where "MVperSF_SF_Wtd" is the value-weighted market value per square foot for an apartment building. The results are just wrong, so looking for some help.
I have attached the file for further clarity. Please see column "R".
Thanks for the help!!!
2016Q1-NCREIF-Value-psf---Apt.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi dhansen_
If that solution worked, please don't forget to mark it as the solution and close the question.
Thanks,
Brian (TBone2K)
Topic Advisor
If that solution worked, please don't forget to mark it as the solution and close the question.
Thanks,
Brian (TBone2K)
Topic Advisor
ASKER
This worked well. I did do some side math, and found I could further simplify the formula by taking the difference of two AVERAGEIFS functions, market less nation. Yields the same results.
ASKER