|
Average of last three rows against another column
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 ? |
Average of last three rows against another column
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 " wrote: 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 ? |
Average of last three rows against another column
On 5 May, 20:58, Joel wrote:
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 " wrote: 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 ?- Hide quoted text - - Show quoted text - Thanks for that, but I'd prefer to do it without using VBA if possible ? |
Average of last three rows against another column
Try this ARRAY FORMULA (committed with CTRL+SHIFT+ENTER, instead of just
ENTER): =IF(COUNT(D2:D38)=3,(SUM(D38:INDEX(D2:D38,LARGE(I F(D2:D38<"",ROW(D2:D38)),3)-1))-SUM(C38:INDEX(C2:C38,LARGE(IF(C2:C38<"",ROW(C2:C3 8)),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 " wrote: 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 ? |
Average of last three rows against another column
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. " wrote: On 5 May, 20:58, Joel wrote: 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 " wrote: 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 ?- Hide quoted text - - Show quoted text - Thanks for that, but I'd prefer to do it without using VBA if possible ? |
Average of last three rows against another column
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 "Ron Coderre" wrote: Try this ARRAY FORMULA (committed with CTRL+SHIFT+ENTER, instead of just ENTER): =IF(COUNT(D2:D38)=3,(SUM(D38:INDEX(D2:D38,LARGE(I F(D2:D38<"",ROW(D2:D38)),3)-1))-SUM(C38:INDEX(C2:C38,LARGE(IF(C2:C38<"",ROW(C2:C3 8)),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 " wrote: 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 ? |
Average of last three rows against another column
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! In article , Domenic wrote: 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! In article .com, wrote: 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,C OU NTA(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 ? |
Average of last three rows against another column
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 |
Average of last three rows against another column
|
Average of last three rows against another column
On 7 May, 22:43, Domenic wrote:
In article .com, 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 |
| All times are GMT +1. The time now is 05:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com