Help - Average consecutive numbers

[DEH]

Hi

I am trying to set up a formula which will calculate the highest average of 3 consecutive salaries using only the last 13 year salary history - from a table consisting of 2 columns a) Year b) Salary.

Any help will be appreciated.

 

[erik.van.geit]

Hi, DEH
Welcome to the Board !!!!!
this part is not clear to me

using only the last 13 year salary history

but this could be a start
{=MAX(A2:A7+A3:A8+A4:A9)/3}
(confirm this array-formula with SHIFT-CONTROL-ENTER)

Map1.xls
	A	B	C	D
1		42
2	16
3	16
4	4
5	44
6	38
7	44
8	4
9	4
data (2)

kind regards,
Erik

 

[Domenic]

Try the following array formulas that need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=MAX(SUBTOTAL(1,OFFSET(B1:B20,ROWS(B1:B20)-ROW(INDIRECT("1:"&13-2)),0,-3)))

or

=MAX(SUBTOTAL(1,OFFSET(B1:B20,ROWS(B1:B20)-ROW(INDIRECT("1:"&D1-2)),0,-3)))

...where D1 contains the number of years of interest, such as 13. Adjust the range accordingly.

Hope this helps!