Dynamic Average Calculation

[msharma864512]

Hi,

I want to calculate dynamic average from a range of cell. For e.g. If I select 1 from a drop down, it should show the data for the latest period only, and I select 2 from the dropdown, it should calculate the average of the latest year, and the prior year data. If I select 3 from the dropdown, the average should be for three years.

Details are in the attached spreadsheet.

Regards,
Manish

 

[GraH - Guido]

Without excel for the moment so I can't look at the workbook.
One way would be to use 3 helper cells in which you perform the 3 calculations.
Then via the drop down you can use index(rangeofcells, selection).

 

[msharma864512]

Without excel for the moment so I can't look at the workbook.
One way would be to use 3 helper cells in which you perform the 3 calculations.
Then via the drop down you can use index(rangeofcells, selection).

I tho8 of using the range of cells only, but not sure how to use the same

 

[GraH - Guido]

Try =AVERAGEIFS($5:$5,$2:$2,$A13,$1:$1,"<="&$A$9)