C
chris.odonoghue
Hi all,
Scratching my head over this one....
What I want is to calculate the average of the last three values in a
column, but compared to the relating three from another column.
My current formula (which works when data is in the last three
populated cells) is as follows:
=IF(COUNTA(D238)<3,0,SUM(SUM(INDEX(D238,COUNTA(D238)-2):INDEX(D238,COUNTA(D238)))-
SUM(INDEX($C$2:$C$38,COUNTA(D238)-2):INDEX($C$2:$C
$38,COUNTA(D238))))/3)
Example:
C
D E
10 15
10 20
20
25
30
25 35
20
30
What I'm after (effectively):
In column D, Sum(Sum(D1,D2,D4) - Sum(C1,C2,C4)) / 3
In column E, the formula I have above works perfectly as the last
three are continuous.
Any ideas ?
Scratching my head over this one....
What I want is to calculate the average of the last three values in a
column, but compared to the relating three from another column.
My current formula (which works when data is in the last three
populated cells) is as follows:
=IF(COUNTA(D238)<3,0,SUM(SUM(INDEX(D238,COUNTA(D238)-2):INDEX(D238,COUNTA(D238)))-
SUM(INDEX($C$2:$C$38,COUNTA(D238)-2):INDEX($C$2:$C
$38,COUNTA(D238))))/3)
Example:
C
D E
10 15
10 20
20
25
30
25 35
20
30
What I'm after (effectively):
In column D, Sum(Sum(D1,D2,D4) - Sum(C1,C2,C4)) / 3
In column E, the formula I have above works perfectly as the last
three are continuous.
Any ideas ?