|
Average last 6
hi all
i've had a look through all the other threads but haven't bee able to find the solution i'm looking for, basically i have a column which has a numeric value entered (in each row) according to results in other cells. see example below A 1 l 10 2 l -10 3 l 12 4 l 0 5 l 16 6 l -11 7 l 10 8 l 9 l i need a funchtion that will average ONLY the last 6 results (a2:a7), this column will be updated as new results are added, therefore the range that needs to be averaged will constantly change to so that ONLY the last 6 entries will be averaged. (a1:a50) will be the max range so cells with no entries (blanK) will need to be ignored. can anyone help? |
=AVERAGE(A200:INDEX(A1:A200,SUMPRODUCT(LARGE(ROW(1 :200)*(A1:A200<""),6))))
-- HTH RP (remove nothere from the email address if mailing direct) "Kwanjangnim" wrote in message ... hi all i've had a look through all the other threads but haven't bee able to find the solution i'm looking for, basically i have a column which has a numeric value entered (in each row) according to results in other cells. see example below A 1 l 10 2 l -10 3 l 12 4 l 0 5 l 16 6 l -11 7 l 10 8 l 9 l i need a funchtion that will average ONLY the last 6 results (a2:a7), this column will be updated as new results are added, therefore the range that needs to be averaged will constantly change to so that ONLY the last 6 entries will be averaged. (a1:a50) will be the max range so cells with no entries (blanK) will need to be ignored. can anyone help? |
thanks for the code, it worked well when i tested it in a blank worksheet,
however for some reason it doesn't work when placed in my worksheet, i keep getting a 'divide by zero error' but all i changed from your formula was the col range and the start row and end row no.s =AVERAGE(B76:INDEX(B7:B76,SUMPRODUCT(LARGE(ROW(7:7 6)*(B7:B76<""),6)))) the empty cells that are awaiting results from other cells have been formulated to display blank but i keep getting 'DIV/0' can you sort this for me? "Bob Phillips" wrote: =AVERAGE(A200:INDEX(A1:A200,SUMPRODUCT(LARGE(ROW(1 :200)*(A1:A200<""),6)))) -- HTH RP (remove nothere from the email address if mailing direct) "Kwanjangnim" wrote in message ... hi all i've had a look through all the other threads but haven't bee able to find the solution i'm looking for, basically i have a column which has a numeric value entered (in each row) according to results in other cells. see example below A 1 l 10 2 l -10 3 l 12 4 l 0 5 l 16 6 l -11 7 l 10 8 l 9 l i need a funchtion that will average ONLY the last 6 results (a2:a7), this column will be updated as new results are added, therefore the range that needs to be averaged will constantly change to so that ONLY the last 6 entries will be averaged. (a1:a50) will be the max range so cells with no entries (blanK) will need to be ignored. can anyone help? |
I must say that Bob did a good job with this formula!
However, there's just a coincidence between the range in his example and the actual size of the range itself. Your range is 70 rows, so revise your formula to this: =AVERAGE(B76:INDEX(B7:B76,SUMPRODUCT(LARGE(ROW(1:7 0)*(B7:B76<""),6)))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Kwanjangnim" wrote in message ... thanks for the code, it worked well when i tested it in a blank worksheet, however for some reason it doesn't work when placed in my worksheet, i keep getting a 'divide by zero error' but all i changed from your formula was the col range and the start row and end row no.s =AVERAGE(B76:INDEX(B7:B76,SUMPRODUCT(LARGE(ROW(7:7 6)*(B7:B76<""),6)))) the empty cells that are awaiting results from other cells have been formulated to display blank but i keep getting 'DIV/0' can you sort this for me? "Bob Phillips" wrote: =AVERAGE(A200:INDEX(A1:A200,SUMPRODUCT(LARGE(ROW(1 :200)*(A1:A200<""),6)))) -- HTH RP (remove nothere from the email address if mailing direct) "Kwanjangnim" wrote in message ... hi all i've had a look through all the other threads but haven't bee able to find the solution i'm looking for, basically i have a column which has a numeric value entered (in each row) according to results in other cells. see example below A 1 l 10 2 l -10 3 l 12 4 l 0 5 l 16 6 l -11 7 l 10 8 l 9 l i need a funchtion that will average ONLY the last 6 results (a2:a7), this column will be updated as new results are added, therefore the range that needs to be averaged will constantly change to so that ONLY the last 6 entries will be averaged. (a1:a50) will be the max range so cells with no entries (blanK) will need to be ignored. can anyone help? |
Just curious. How can Bob's formula be tweaked to handle a generic situation where* the last 6 rows in Column A* are always averaged. -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=389720 |
Just curious. How can Bob's formula be tweaked to handle a generic situation where* the last 6 rows in Column A* are always averaged. +-------------------------------------------------------------------+ |Filename: encdcpt.zip | |Download: http://www.excelforum.com/attachment.php?postid=3621 | +-------------------------------------------------------------------+ -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=389720 |
Hi David,
=AVERAGE(A65336:INDEX(A1:A65336,SUMPRODUCT(LARGE(R OW(1:65336)*(A1:A65336<"" ),6)))) although you wont want many of these in your spreadsheet <g -- HTH RP (remove nothere from the email address if mailing direct) "davidm" wrote in message ... Just curious. How can Bob's formula be tweaked to handle a generic situation where* the last 6 rows in Column A* are always averaged. -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=389720 |
I still have the 'Div/0' error that needs fixing any suggestion, please read
previous thread for details "Ragdyer" wrote: I must say that Bob did a good job with this formula! However, there's just a coincidence between the range in his example and the actual size of the range itself. Your range is 70 rows, so revise your formula to this: =AVERAGE(B76:INDEX(B7:B76,SUMPRODUCT(LARGE(ROW(1:7 0)*(B7:B76<""),6)))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Kwanjangnim" wrote in message ... thanks for the code, it worked well when i tested it in a blank worksheet, however for some reason it doesn't work when placed in my worksheet, i keep getting a 'divide by zero error' but all i changed from your formula was the col range and the start row and end row no.s =AVERAGE(B76:INDEX(B7:B76,SUMPRODUCT(LARGE(ROW(7:7 6)*(B7:B76<""),6)))) the empty cells that are awaiting results from other cells have been formulated to display blank but i keep getting 'DIV/0' can you sort this for me? "Bob Phillips" wrote: =AVERAGE(A200:INDEX(A1:A200,SUMPRODUCT(LARGE(ROW(1 :200)*(A1:A200<""),6)))) -- HTH RP (remove nothere from the email address if mailing direct) "Kwanjangnim" wrote in message ... hi all i've had a look through all the other threads but haven't bee able to find the solution i'm looking for, basically i have a column which has a numeric value entered (in each row) according to results in other cells. see example below A 1 l 10 2 l -10 3 l 12 4 l 0 5 l 16 6 l -11 7 l 10 8 l 9 l i need a funchtion that will average ONLY the last 6 results (a2:a7), this column will be updated as new results are added, therefore the range that needs to be averaged will constantly change to so that ONLY the last 6 entries will be averaged. (a1:a50) will be the max range so cells with no entries (blanK) will need to be ignored. can anyone help? |
If you revised your formula as I suggested, and you're still getting that
#DIV/0! error, then I would guess that your numbers are *not numbers*. Now, your OP said that these numbers were the *results* from other cells. Are there formulas in B7:B76? If so, post back with the formulas. Also, you could try this formula to test the contents of Column B: =ISNUMBER(B7)&"-"&LEN(B7) Copy down and make sure that you see "True", and that the number returned matches the visible characters in the formula bar ( i.e formats - $ - don't count) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Kwanjangnim" wrote in message ... I still have the 'Div/0' error that needs fixing any suggestion, please read previous thread for details "Ragdyer" wrote: I must say that Bob did a good job with this formula! However, there's just a coincidence between the range in his example and the actual size of the range itself. Your range is 70 rows, so revise your formula to this: =AVERAGE(B76:INDEX(B7:B76,SUMPRODUCT(LARGE(ROW(1:7 0)*(B7:B76<""),6)))) -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Kwanjangnim" wrote in message ... thanks for the code, it worked well when i tested it in a blank worksheet, however for some reason it doesn't work when placed in my worksheet, i keep getting a 'divide by zero error' but all i changed from your formula was the col range and the start row and end row no.s =AVERAGE(B76:INDEX(B7:B76,SUMPRODUCT(LARGE(ROW(7:7 6)*(B7:B76<""),6)))) the empty cells that are awaiting results from other cells have been formulated to display blank but i keep getting 'DIV/0' can you sort this for me? "Bob Phillips" wrote: =AVERAGE(A200:INDEX(A1:A200,SUMPRODUCT(LARGE(ROW(1 :200)*(A1:A200<""),6)))) -- HTH RP (remove nothere from the email address if mailing direct) "Kwanjangnim" wrote in message ... hi all i've had a look through all the other threads but haven't bee able to find the solution i'm looking for, basically i have a column which has a numeric value entered (in each row) according to results in other cells. see example below A 1 l 10 2 l -10 3 l 12 4 l 0 5 l 16 6 l -11 7 l 10 8 l 9 l i need a funchtion that will average ONLY the last 6 results (a2:a7), this column will be updated as new results are added, therefore the range that needs to be averaged will constantly change to so that ONLY the last 6 entries will be averaged. (a1:a50) will be the max range so cells with no entries (blanK) will need to be ignored. can anyone help? |
yeah there are formulas in b7:b76
"=IF(ISNA(N18+(SUM(I18:M18))),"",N18+(SUM(I18:M18) ))" =AVERAGE(B76:INDEX(B7:B76,SUMPRODUCT(LARGE(ROW(1:7 0)*(B7:B76<""),6)))) is what you revised, but i just wanted to check that "(ROW(1:70)" not ment to be "(ROW(7:76)" it doesn't matter anyway coz i still get "div/0" error will use the "=ISNUMBER(B7)&"-"&LEN(B7)" to validate col b and get back to you thanks for your help so far "RagDyer" wrote: If you revised your formula as I suggested, and you're still getting that #DIV/0! error, then I would guess that your numbers are *not numbers*. Now, your OP said that these numbers were the *results* from other cells. Are there formulas in B7:B76? If so, post back with the formulas. Also, you could try this formula to test the contents of Column B: =ISNUMBER(B7)&"-"&LEN(B7) Copy down and make sure that you see "True", and that the number returned matches the visible characters in the formula bar ( i.e formats - $ - don't count) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Kwanjangnim" wrote in message ... I still have the 'Div/0' error that needs fixing any suggestion, please read previous thread for details "Ragdyer" wrote: I must say that Bob did a good job with this formula! However, there's just a coincidence between the range in his example and the actual size of the range itself. Your range is 70 rows, so revise your formula to this: =AVERAGE(B76:INDEX(B7:B76,SUMPRODUCT(LARGE(ROW(1:7 0)*(B7:B76<""),6)))) -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Kwanjangnim" wrote in message ... thanks for the code, it worked well when i tested it in a blank worksheet, however for some reason it doesn't work when placed in my worksheet, i keep getting a 'divide by zero error' but all i changed from your formula was the col range and the start row and end row no.s =AVERAGE(B76:INDEX(B7:B76,SUMPRODUCT(LARGE(ROW(7:7 6)*(B7:B76<""),6)))) the empty cells that are awaiting results from other cells have been formulated to display blank but i keep getting 'DIV/0' can you sort this for me? "Bob Phillips" wrote: =AVERAGE(A200:INDEX(A1:A200,SUMPRODUCT(LARGE(ROW(1 :200)*(A1:A200<""),6)))) -- HTH RP (remove nothere from the email address if mailing direct) "Kwanjangnim" wrote in message ... hi all i've had a look through all the other threads but haven't bee able to find the solution i'm looking for, basically i have a column which has a numeric value entered (in each row) according to results in other cells. see example below A 1 l 10 2 l -10 3 l 12 4 l 0 5 l 16 6 l -11 7 l 10 8 l 9 l i need a funchtion that will average ONLY the last 6 results (a2:a7), this column will be updated as new results are added, therefore the range that needs to be averaged will constantly change to so that ONLY the last 6 entries will be averaged. (a1:a50) will be the max range so cells with no entries (blanK) will need to be ignored. can anyone help? |
update: not that it matter for the examples i'm testing but in my worksheet
b7:b76 is actually r7:r76 and the validation formula returned "false-0" "=ISNUMBER(r18)&"-"&LEN(r18)" cheers colin "RagDyer" wrote: If you revised your formula as I suggested, and you're still getting that #DIV/0! error, then I would guess that your numbers are *not numbers*. Now, your OP said that these numbers were the *results* from other cells. Are there formulas in B7:B76? If so, post back with the formulas. Also, you could try this formula to test the contents of Column B: =ISNUMBER(B7)&"-"&LEN(B7) Copy down and make sure that you see "True", and that the number returned matches the visible characters in the formula bar ( i.e formats - $ - don't count) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Kwanjangnim" wrote in message ... I still have the 'Div/0' error that needs fixing any suggestion, please read previous thread for details "Ragdyer" wrote: I must say that Bob did a good job with this formula! However, there's just a coincidence between the range in his example and the actual size of the range itself. Your range is 70 rows, so revise your formula to this: =AVERAGE(B76:INDEX(B7:B76,SUMPRODUCT(LARGE(ROW(1:7 0)*(B7:B76<""),6)))) -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Kwanjangnim" wrote in message ... thanks for the code, it worked well when i tested it in a blank worksheet, however for some reason it doesn't work when placed in my worksheet, i keep getting a 'divide by zero error' but all i changed from your formula was the col range and the start row and end row no.s =AVERAGE(B76:INDEX(B7:B76,SUMPRODUCT(LARGE(ROW(7:7 6)*(B7:B76<""),6)))) the empty cells that are awaiting results from other cells have been formulated to display blank but i keep getting 'DIV/0' can you sort this for me? "Bob Phillips" wrote: =AVERAGE(A200:INDEX(A1:A200,SUMPRODUCT(LARGE(ROW(1 :200)*(A1:A200<""),6)))) -- HTH RP (remove nothere from the email address if mailing direct) "Kwanjangnim" wrote in message ... hi all i've had a look through all the other threads but haven't bee able to find the solution i'm looking for, basically i have a column which has a numeric value entered (in each row) according to results in other cells. see example below A 1 l 10 2 l -10 3 l 12 4 l 0 5 l 16 6 l -11 7 l 10 8 l 9 l i need a funchtion that will average ONLY the last 6 results (a2:a7), this column will be updated as new results are added, therefore the range that needs to be averaged will constantly change to so that ONLY the last 6 entries will be averaged. (a1:a50) will be the max range so cells with no entries (blanK) will need to be ignored. can anyone help? |
You got a return of "False-0" for R18?!?!?!
When you just said that those cells contained the formula: "=IF(ISNA(N18+(SUM(I18:M18))),"",N18+(SUM(I18:M18) ))" Does that means that you have a #N/A error in the I18:N18 range, and R18 looks empty? Also, is the entire R6:R76 range populated with the IF() formula? Perhaps you might wish to send me a copy of your sheet. Cut out cutout from my address. Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Kwanjangnim" wrote in message ... update: not that it matter for the examples i'm testing but in my worksheet b7:b76 is actually r7:r76 and the validation formula returned "false-0" "=ISNUMBER(r18)&"-"&LEN(r18)" cheers colin "RagDyer" wrote: If you revised your formula as I suggested, and you're still getting that #DIV/0! error, then I would guess that your numbers are *not numbers*. Now, your OP said that these numbers were the *results* from other cells. Are there formulas in B7:B76? If so, post back with the formulas. Also, you could try this formula to test the contents of Column B: =ISNUMBER(B7)&"-"&LEN(B7) Copy down and make sure that you see "True", and that the number returned matches the visible characters in the formula bar ( i.e formats - $ - don't count) -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Kwanjangnim" wrote in message ... I still have the 'Div/0' error that needs fixing any suggestion, please read previous thread for details "Ragdyer" wrote: I must say that Bob did a good job with this formula! However, there's just a coincidence between the range in his example and the actual size of the range itself. Your range is 70 rows, so revise your formula to this: =AVERAGE(B76:INDEX(B7:B76,SUMPRODUCT(LARGE(ROW(1:7 0)*(B7:B76<""),6)))) -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Kwanjangnim" wrote in message ... thanks for the code, it worked well when i tested it in a blank worksheet, however for some reason it doesn't work when placed in my worksheet, i keep getting a 'divide by zero error' but all i changed from your formula was the col range and the start row and end row no.s =AVERAGE(B76:INDEX(B7:B76,SUMPRODUCT(LARGE(ROW(7:7 6)*(B7:B76<""),6)))) the empty cells that are awaiting results from other cells have been formulated to display blank but i keep getting 'DIV/0' can you sort this for me? "Bob Phillips" wrote: =AVERAGE(A200:INDEX(A1:A200,SUMPRODUCT(LARGE(ROW(1 :200)*(A1:A200<""),6)))) -- HTH RP (remove nothere from the email address if mailing direct) "Kwanjangnim" wrote in message ... hi all i've had a look through all the other threads but haven't bee able to find the solution i'm looking for, basically i have a column which has a numeric value entered (in each row) according to results in other cells. see example below A 1 l 10 2 l -10 3 l 12 4 l 0 5 l 16 6 l -11 7 l 10 8 l 9 l i need a funchtion that will average ONLY the last 6 results (a2:a7), this column will be updated as new results are added, therefore the range that needs to be averaged will constantly change to so that ONLY the last 6 entries will be averaged. (a1:a50) will be the max range so cells with no entries (blanK) will need to be ignored. can anyone help? |
The sheet is on the way back to you.
I didn't change anything in your sheet *except* what was in Column R. In fact, I didn't even change R, but added revised formulas to Column S, right along side. I didn't get into the intricacies of your sheet and formulas, so make sure that the change I made won't screw anything else up that I might not have noticed. It seems that the error message generated by your Sum formula caused all the problem. I *didn't* see that #DIV/0 error that you kept mentioning about in your posts. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "RagDyeR" wrote in message ... You got a return of "False-0" for R18?!?!?! When you just said that those cells contained the formula: "=IF(ISNA(N18+(SUM(I18:M18))),"",N18+(SUM(I18:M18) ))" Does that means that you have a #N/A error in the I18:N18 range, and R18 looks empty? Also, is the entire R6:R76 range populated with the IF() formula? Perhaps you might wish to send me a copy of your sheet. Cut out cutout from my address. Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Kwanjangnim" wrote in message ... update: not that it matter for the examples i'm testing but in my worksheet b7:b76 is actually r7:r76 and the validation formula returned "false-0" "=ISNUMBER(r18)&"-"&LEN(r18)" cheers colin "RagDyer" wrote: If you revised your formula as I suggested, and you're still getting that #DIV/0! error, then I would guess that your numbers are *not numbers*. Now, your OP said that these numbers were the *results* from other cells. Are there formulas in B7:B76? If so, post back with the formulas. Also, you could try this formula to test the contents of Column B: =ISNUMBER(B7)&"-"&LEN(B7) Copy down and make sure that you see "True", and that the number returned matches the visible characters in the formula bar ( i.e formats - $ - don't count) -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Kwanjangnim" wrote in message ... I still have the 'Div/0' error that needs fixing any suggestion, please read previous thread for details "Ragdyer" wrote: I must say that Bob did a good job with this formula! However, there's just a coincidence between the range in his example and the actual size of the range itself. Your range is 70 rows, so revise your formula to this: =AVERAGE(B76:INDEX(B7:B76,SUMPRODUCT(LARGE(ROW(1:7 0)*(B7:B76<""),6)))) -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Kwanjangnim" wrote in message ... thanks for the code, it worked well when i tested it in a blank worksheet, however for some reason it doesn't work when placed in my worksheet, i keep getting a 'divide by zero error' but all i changed from your formula was the col range and the start row and end row no.s =AVERAGE(B76:INDEX(B7:B76,SUMPRODUCT(LARGE(ROW(7:7 6)*(B7:B76<""),6)))) the empty cells that are awaiting results from other cells have been formulated to display blank but i keep getting 'DIV/0' can you sort this for me? "Bob Phillips" wrote: =AVERAGE(A200:INDEX(A1:A200,SUMPRODUCT(LARGE(ROW(1 :200)*(A1:A200<""),6)))) -- HTH RP (remove nothere from the email address if mailing direct) "Kwanjangnim" wrote in message ... hi all i've had a look through all the other threads but haven't bee able to find the solution i'm looking for, basically i have a column which has a numeric value entered (in each row) according to results in other cells. see example below A 1 l 10 2 l -10 3 l 12 4 l 0 5 l 16 6 l -11 7 l 10 8 l 9 l i need a funchtion that will average ONLY the last 6 results (a2:a7), this column will be updated as new results are added, therefore the range that needs to be averaged will constantly change to so that ONLY the last 6 entries will be averaged. (a1:a50) will be the max range so cells with no entries (blanK) will need to be ignored. can anyone help? |
| All times are GMT +1. The time now is 12:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com