Formula to work out the average of an array from a changing cell

Hello every one,

I need some help in order to keep going and finish an excel file I current work with but I've got stuck in a formula.

I will try to do my best to explain the formulas and steps to you.

I have a file with 2 spreadsheets, Forecast is the calculation sheet, MNP00XX-BB is a sheet with data.

My last formula is =ADDRESS(4,MATCH(E13,'MNP00XX-BB'!B3:M3,0)+1,2,0,"MNP00XX-BB!") and the result is 'MNP00XX-BB!'!R4C[3], this formula gives me the cell in which it will be my starting point for then next formula (the one I do not know), as you see the formula is done in FORECAST but the data is in MNP00XX-BB. This formula above changes any time I type a different date, however there are more formulas in between, from typing the date and the result ('MNP00XX-BB!'!R4C[3]).

The formula I would like to do is an Average (I think it's better Averagea so that blank cells are not included) of 2 rows the and 3 columns, just one row below from the cell given by('MNP00XX-BB!'!R4C[3]) and three columns to the right including the same column as the formulas says.

My point is that every time I type a date an so the formula (=ADDRESS(4,MATCH(E13,'MNP00XX-BB'!B3:M3,0)+1,2,0,"MNP00XX-BB!")) changes to another selected cell the average should start from such a cell. (instructions as above)

Example:

Date: 15/05/2013

..... some other formulas,

Result: 'MNP00XX-BB!'!R4C[6]

Average should be: AVERAGE(F5:H6)

if Date is 15/07/2013 AVERAGE(H5:J6)

I tried hard to do the formula with AVERAGE(OFFSET(......)) but I couldn't manage.

If you can help me I would be really grateful.

Thanks a lot.

hi,

The second formula is right except that I just want the average for the years 2012 and 2013.

I just need the month number to have a reference to start. Those numbers must not need to be included in the average.

What would be the modification to omit these numbers and do the average of only the data which is the the year's rows.

Thanks you so much.

Sorry I type the formula wrong, your formula works perfect.

Thanks

ok,

Thanks for the information.

I will follow your suggestions.

Best Regards,

Francesc