Average of last three rows against another column

  • Thread starter chris.odonoghue
  • Start date
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(D2:D38)<3,0,SUM(SUM(INDEX(D2:D38,COUNTA(D2:D38)-2):INDEX(D2:D38,COUNTA(D2:D38)))-
SUM(INDEX($C$2:$C$38,COUNTA(D2:D38)-2):INDEX($C$2:$C
$38,COUNTA(D2:D38))))/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 ?
 
J

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
 
C

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

Domenic

Try...

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

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

Hope this helps!
 
R

Ron Coderre

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

=IF(COUNT(D2:D38)>=3,(SUM(D38:INDEX(D2:D38,LARGE(IF(D2:D38<>"",ROW(D2:D38)),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
D2:D38))/3


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

XL2002, WinXP
 
J

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.
 
R

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 D2:D38 contains values with interspersed blanks
And
C2:C38 contains values in the same rows as the D2:D38 values

AND...you want to find the last 3 values in D2:D38 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(D2:D38)>=3,SUMPRODUCT(N(OFFSET(D2,LARGE(IF(D2:D38<>"",ROW(D2:D38)-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
 
D

Domenic

Similar to Ron's last post...

=IF(COUNT(D2:D38)>=3,SUM(N(OFFSET(C2:C38,SMALL(IF(D2:D38<>"",ROW(D2:D38)-
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(D2:D38)>=3,SUM(N(OFFSET($C$2:$C$38,SMALL(IF(D2:D38<>"",ROW(D2:D
38)-ROW(D2)),{1;2;3}),{0,1}*COLUMNS($D40:D40),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!
 
O

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(D2:D38)>=3,AVERAGE(N(OFFSET(D2:D38,SMALL(IF(D2:D38<>"",ROW(D2:D38)-
ROW(D2)),{1,2,3}),0,1))-N(OFFSET($C$2:$C$38,SMALL(IF
D2:D38<>"",ROW(D2:D38)-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
 
D

Domenic

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

....for each cell in D2:D38 that is not blank, the corresponding number
in the array provided by ROW(D2:D38)-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(D2:D38)>=3,SUM(N(OFFSET(C2:C38,SMALL(IF(D2:D38<>"",ROW(D2:D38)-
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(D2:D38)>=3,SUM(N(OFFSET($C$2:$C$38,SMALL(IF(D2:D38<>"",ROW(D2:D
38)-ROW(D2)),{1;2;3}),{0,1}*COLUMNS($D40:D40),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!
 
O

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top