Need to Average the prices for the year

[Hazra Hadee]

Hello,

I am working on a dashboard to extract 2 different vendor information from the data sheet to show:

When a city is selected:

- what is the best average adult rate and average child rate for that vendor for the year

I have done the pivot, but I want to also do this in formula. Appreciate all inputs. Attaching a sample file to show what I've done thus far. Thank you

 

[Hui]

Please see attached file:

Please check solutions

 

[Hazra Hadee]

Thanks a million! Yes I should of thought of array. Appreciate the help.

 

[Hazra Hadee]

Hello, Hui,

I am getting the #Div/0! error for the cells where I have copied the formula down just in the Average Adult and child columns. How do I hide them? Thank you

Best

 

[Hui]

=iferror(existing formula, 0)

 

[Hazra Hadee]

Thank you that worked