AVERAGEIF Array for multiple variables

[Csterne]

I'm in the process of writing a formula to calculate the average cost from a dataset, based on a number of variables; Distance From a Point, Room Capacity, Whether the location is a Hotel, and whether the cost is based on Delegate Rates.

The variables are all set on a separate page, with manual input of the Distance and Capacity figures, and data validation for a “Yes/No” response for the Hotel and Delegate rate variables.

So far, I’ve been able to use an AVERAGE(IF array function to narrow down the dataset based on distance from a point, and room capacity.

I’m struggling to write the code that would also narrow down the data for the last two variables. Essentially, I want the formula to review each variable separately, and do the following:

If “Include Delegate Rates” is “No”, only choose rates with “No” in the “Delegate Rate” column.
If “Include Delegate Rates” is “Yes”, include all rates and disregard the “Delegate Rate” column


If “Include Hotels” is “No”, only choose rates with “No” in the “Is Hotel?” column.
If “Include Hotels” is “Yes”, include all rates and disregard the “Is Hotel?” column

If anyone could help me with the formula to do this, that would be greatly appreciated.

[ChemistB]

You should be able to use AVERAGEIFS with your version of Excel. Try

=AVERAGEIFS(CompetitionDataTable[Hourly Cost], INDEX(CompetitionDataTable[[Library 1]:[Library 3]],,MATCH(A2,CompetitionDataTable[[#Headers],[Library 1]:[Library 3]],0)), "<=" &ComparisonSheet!$B$1, CompetitionDataTable[RoomCapacity], "<="&ComparisonSheet!$D$2, CompetitionDataTable[Delegate Rate?], IF(ComparisonSheet!$B$2="No", "No","*"),CompetitionDataTable[Is Hotel?],IF(ComparisonSheet!$D$1="No", "<>Hotel","*"))

Does that give you the results you expect? If not, please give a few examples of what you are expecting if calculated manually.