Averaging Indirect 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

 

[Ron Rosenfeld]

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

In general, you could use the FVSCHEDULE function for this. The formula would
be something like:

=-1+FVSCHEDULE(1,monthly_returns)

But I don't understand from your description exactly how you've got your data
sheet set up, so I cannot advise you further.


--ron

 

[vezerid]

I assume that your months are entered as text, in the manner appearing
in your message. Then, the following *array* formula (needs to be
committed with Shift+Ctrl+Enter) should do:

=AVERAGE(IF(AND(DATEVALUE("1 "&A1:A100)>=DATE(1990,1,1),DATEVALUE("1
"&A1:A100)<=DATE(1990,3,1)),B1:B100))

HTH
Kostis Vezerides