Averaging Cells

[Guest]

I have a simple spreadsheet, in columns A and B are months of the year (from
Jan 90 to present) and the Annual Inflation Rates for those years...

In cell E4, I enter Month X, and in F4 I enter Month Y

I need to work out the average of the corresponding inflation rates BETWEEN
those two months

e.g. if X is Jan 90 and Y is March 90, I need to find out the average of the
inflation rate for Jan Feb and Mar 90.

Anyone think they might be able to help me? I'm completely stumped!

Thanks
Daniel

 

[Fred Smith]

You can define the range that you want to work with by using the Offset
function. Something like:

=average(offset(b1,e4,0,f4-e4+1,1))

This will give you the arithmetic average of the cell range. However, it will
*not* give you the actual inflation rate for that period, because inflation
rates compound just like interest rates.

If you want the actual inflation rate, you need to calculate the future value of
$1 from month e4 to f4. If the actual inflation rate is what you want, post back
if you need more help calculating it.

 

[Guest]

Try something like this array formula:

=AVERAGE(IF((MONTH(A1:A10)>=MONTH(E4))*(YEAR(A1:A10)>=YEAR(E4))*(MONTH(A1:A10)<=MONTH(F4))*(YEAR(A1:A10)<=YEAR(F4)),B1:B10))
enter using Ctrl+Shift+Enter

E4 and F4 must be formatted as dates

HTH
Jean-Guy

 

[Guest]

The first formula I gave you doesn't seem to work across multiple years only
within a specific year, I don't know why so try this one instead:

=AVERAGE(IF((A1:A100>=DATE(YEAR(E4),MONTH(E4),1))*(A1:A100<=DATE(YEAR(F4),MONTH(F4),1)),B1:B100))
or
=AVERAGE(IF((A1:A100>=DATE(YEAR(E4),MONTH(E4),1))*(A1:A100<=DATE(YEAR(F4),MONTH(F4)+1,1)-1),B1:B100))
again enter using Ctrl+Shift+Enter

HTH
Jean-Guy