Help please - need formula to sum and average data using multiple vlookup (s)

Hi Guys,

Need help please!

Refer to the attached file, "Credit Dashboard - Monthly - Yearly (Excel Submission).

Refer to sheet, "Dashboard".

There are two filters in cells I2 and I3 which control 12 charts on this page. If you change each filter in cells I2 and I3 you will notice that the charts update accordingly which is perfect.

What i am trying to achieve is the following;

In cell C13 highlighted in green - the current formula returns data based on a vlookup of the filter in cell I2 from the corresponding sheet, "Dashboard Data" for the Actual in column D. This is great for single values, however, what i need is for the data in cell C13, (highlighted in green) to return the average value from the range of both filters from I2 to I3.

Example;

Based on the current filters selected on the sheet which are;

I2 = Adriano-Jul11
I3 = Adriano-Sep11

I need to average the values from column D, (Actual) on sheet, "Dashboard Data" for the data range from I2 = Adriano-Jul11 to I3 = Adriano-Sep11. In addition, I need a formula that will work when the filters in I2 & I3 are changed to any selection, (from the filters in the list) which correspond to the data from cell B5 (Filter - From / to) to the maximum cell in column B (65,536 etc.)

The average for the following filter selections would be calculated as follows;

I2 = Adriano-Jul11
I3 = Adriano-Sep11

Adriano-Jul11 = Actual is D5 from sheet, "Dashboard Data" = 110%
Adriano-Aug11 = Actual is D6 from sheet, "Dashboard Data" = 112%
Adriano-Sep11 = Actual is D7 from sheet, "Dashboard Data" = 108%

Answer = average of 110% + 112% + 108% = 330%/3 = 110%

I will be using the same formula to calculate Exposure in C14, DSO in C15 etc. Some of the formulas will need to be an average of the range (I2 to I3 filters) as outlined above and some formulas will be a sum. I am hoping once one of the formulas works i can use the same method and just change from average to sum for the given range etc? If this is incorrect, please provide both formulas that would provide an average and a sum for the above example.

If someone can please help me urgently this would be greatly appreciated!!!

Owe you one!

Originally Posted by vlady

can you try this one????

Formula:

Please Login or Register  to view this content.

Wow - that was absolutely amazing!!!

All i had to do was change the absolute range in B17 to B10000 or as far as i wanted.

Thank you so much for your help and assistance - you have made my day!!!