Average of last three rows against another column

[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 ?

 

[Joel]

Use this cusom function. Call with

= Averagelast3(E1:E12) ,this would be in cell E13.

the function will average two or 1 cells if there are less than 3 cells that
contain data.

Function Averagelast3(Target As Range)

Cellcount = 0
Total = 0
LastRow = Target.End(xlDown).Row
For RowCount = LastRow To 1 Step -1
If Not IsEmpty(Cells(RowCount, Target.Column)) Then

Total = Total + Cells(RowCount, Target.Column)
Cellcount = Cellcount + 1
If Cellcount = 3 Then Exit For
End If

Next RowCount

Averagelast3 = Total / Cellcount

End Function

 

[chris.odonoghue]

Use this cusom function. Call with

= Averagelast3(E1:E12) ,this would be in cell E13.

the function will average two or 1 cells if there are less than 3 cells that
contain data.

Function Averagelast3(Target As Range)

Cellcount = 0
Total = 0
LastRow = Target.End(xlDown).Row
For RowCount = LastRow To 1 Step -1
If Not IsEmpty(Cells(RowCount, Target.Column)) Then

Total = Total + Cells(RowCount, Target.Column)
Cellcount = Cellcount + 1
If Cellcount = 3 Then Exit For
End If

Next RowCount

Averagelast3 = Total / Cellcount

End Function









- Show quoted text -

Thanks for that, but I'd prefer to do it without using VBA if
possible ?

 

[Domenic]

Try...

=IF(COUNT(D238)>=3,AVERAGE(N(OFFSET(D238,SMALL(IF(D238<>"",ROW(D2
38)-ROW(D2)),{1,2,3}),0,1))-N(OFFSET($C$2:$C$38,SMALL(IF(D238<>"",ROW(D
238)-ROW(D2)),{1,2,3}),0,1))),0)

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Ron Coderre]

Try this ARRAY FORMULA (committed with CTRL+SHIFT+ENTER, instead of just
ENTER):

=IF(COUNT(D238)>=3,(SUM(D38:INDEX(D238,LARGE(IF(D238<>"",ROW(D238)),3)-1))-SUM(C38:INDEX(C2:C38,LARGE(IF(C2:C38<>"",ROW(C2:C38)),3)-1)))/3,0)

That formula calculates
((sum of the last 3 values in C2:C38) - (sum of the last 3 values in
D238))/3


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Joel]

I believe complicated worksheet formulars a very hard to debug and extremely
hard to maintain (make changes). It is easy to understand VBA code and you
can also add comments to the code. After you get your problem solve, look at
all the solutions and tell me which is more understandable.

I had two computer teachers as an undergraduate in college. They where
bothers and both were bitches. they both emphasized the fact that code had
to be understandable. One brother even took off point s if you had too many
comments and too little comments. He wanted to see that the comments were
just right.

 

[Ron Coderre]

This may be a double-post.....My PC did something "funny" the first time...

Text wrap mangled your posted example, so I'm having trouble decyphering
exactly what you're looking to do.

BUT.....if Domenic is on the right track...

If D238 contains values with interspersed blanks
And
C2:C38 contains values in the same rows as the D238 values

AND...you want to find the last 3 values in D238 and subtract values in
the corresponding cells in C2:C38
Sum those differences THEN divide the total by 3

Try this ARRAY FORMULA

=IF(COUNT(D238)>=3,SUMPRODUCT(N(OFFSET(D2,LARGE(IF(D238<>"",ROW(D238)-ROW(D2)),{1,1;2,2;3,3}),{-1,0;-1,0;-1,0}))*{-1,1;-1,1;-1,1})/3,0)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Domenic]

Similar to Ron's last post...

=IF(COUNT(D238)>=3,SUM(N(OFFSET(C2:C38,SMALL(IF(D238<>"",ROW(D238)-
ROW(D2)),{1;2;3}),{0,1},1))*{-1,1})/3,0)

....confirmed with CONTROL+SHIFT+ENTR. However, assuming that the
formula will be entered at the end of Column D, let's say D40, and
copied across, try the following instead...

=IF(COUNT(D238)>=3,SUM(N(OFFSET($C$2:$C$38,SMALL(IF(D238<>"",ROW(D2
38)-ROW(D2)),{1;2;3}),{0,1}*COLUMNS($D4040),1))*{-1,1})/3,0)

....confirmed with CONTROL+SHIFT+ENTER. Note that in all cases, the
first three values in the column, starting from D2, are averaged. For
the last three values, change SMALL to LARGE.

Hope this helps!

 

[odonoghue.christopher]

hey guys, thanks for the replies.

Joel, I could do this in VBA with my eyes closed (10 years
experience), but I'm trying to use this sheet to learn more about
INDEX, OFFSET, ROW etc (more advanced worksheet formulas). I
appreciate it would be easier to run with VBA, but I'm 95% of the way
there with the sheet using worksheet formulas. I really appreciate
your answers, but I'm really trying to stay away from code on this
one.


Ron, your second formula works perfectly for Column D. However, the
number go a little strange when moving to E, F, G etc

the values in C should be absolute.
I need:
values in D against relating values in C
values in E against relating values in C
etc

Dom, your formula
=IF(COUNT(D238)>=3,AVERAGE(N(OFFSET(D238,SMALL(IF(D238<>"",ROW(D238)-
ROW(D2)),{1,2,3}),0,1))-N(OFFSET($C$2:$C$38,SMALL(IF
D238<>"",ROW(D238)-ROW(D2)),{1,2,3}),0,1))),0)

works great after changing to use Large instead of Small. However, I
don't fully understand it, could you elaborate on the parts of it
please ? (Specifically, the ROW part and the "{1,2,3}"

Many Many Thanks Guys
I really appreciate all the help
Chris

 

[Domenic]

Dom, your formula
=IF(COUNT(D238)>=3,AVERAGE(N(OFFSET(D238,SMALL(IF(D238<>"",ROW(D238)-
ROW(D2)),{1,2,3}),0,1))-N(OFFSET($C$2:$C$38,SMALL(IF
D238<>"",ROW(D238)-ROW(D2)),{1,2,3}),0,1))),0)

works great after changing to use Large instead of Small. However, I
don't fully understand it, could you elaborate on the parts of it
please ? (Specifically, the ROW part and the "{1,2,3}"

ROW(D238)-ROW(D2) returns the following array of values:

ROW(D2)-ROW(D2) -----> 2-2 -----> 0
ROW(D3)-ROW(D2) -----> 3-2 -----> 1
ROW(D4)-ROW(D2) -----> 4-2 -----> 2
ROW(D5)-ROW(D2) -----> 5-2 -----> 3
..
..
..
ROW(D38)-ROW(D2) -----> 38-2 -----> 36

So, if we look at the SMALL function...

SMALL(IF(D238<>"",ROW(D238)-ROW(D2)),{1,2,3})

....for each cell in D238 that is not blank, the corresponding number
in the array provided by ROW(D238)-ROW(D2) is returned, otherwise
FALSE is returned.

Then, the 1st, 2nd, and 3rd smallest values from this array are
returned. In turn, this array of values is used as the row offset from
the starting reference in the first OFFSET formula. In effect, we get
an array of references. The N function makes the values available from
the array of references provided by OFFSET. The same process applies to
second OFFSET function.

Did you notice my last post? To make it easier for you, here it is
again...

===================================================================

Similar to Ron's last post...

=IF(COUNT(D238)>=3,SUM(N(OFFSET(C2:C38,SMALL(IF(D238<>"",ROW(D238)-
ROW(D2)),{1;2;3}),{0,1},1))*{-1,1})/3,0)

....confirmed with CONTROL+SHIFT+ENTR. However, assuming that the
formula will be entered at the end of Column D, let's say D40, and
copied across, try the following instead...

=IF(COUNT(D238)>=3,SUM(N(OFFSET($C$2:$C$38,SMALL(IF(D238<>"",ROW(D2
38)-ROW(D2)),{1;2;3}),{0,1}*COLUMNS($D4040),1))*{-1,1})/3,0)

....confirmed with CONTROL+SHIFT+ENTER. Note that in all cases, the
first three values in the column, starting from D2, are averaged. For
the last three values, change SMALL to LARGE.

Hope this helps!

 

[odonoghue.christopher]

Then, the 1st, 2nd, and 3rd smallest values from this array are
returned. In turn, this array of values is used as the row offset from
the starting reference in the first OFFSET formula. In effect, we get
an array of references. The N function makes the values available from
the array of references provided by OFFSET. The same process applies to
second OFFSET function.

Hope this helps!

This is the part I didn't understand, but I see it now, it's not the
Smallest values, it's the three smallest row numbers ? (or large in my
case)

Cheers
Chris