Using Multiple Array Criteria - Determine Average Result

[Guest]

I have a data table with three colums;
State / City / Days to Complete Training

I want to provide a report (table) that shows the average days to complete
for each city.

I assume it will be a function that would include some use of sumproduct but
very unsure how to include the "average" result portion.

Thanks!!!
Scott

 

[Roger Govier]

Hi Scott

Use a Pivot table.
Place your cursor in the data table, Data>Pivot Tables>Finish
On the PT skeleton that appears on the new tab,
Drag State to the Row area
Drag City to the Row area
Drag Days to the Data area
Double click on the Sum of Days field that appears, and select Average from
the dropdown list

 

[Guest]

Hi Roger,

Everything has worked until the last step. First off, thank you for your
great suggestion. I must not be double clicking on the right spot because
when I do no drop down appears rather a new worksheet appears with data in
it. Any thoughts?

 

[Roger Govier]

Hi Scott

Try
Right click on PT>PT Wizard>Layout>double click on the Days field in the
Data area>select average from dropdown>OK>Finish