|
Average Function that Counts
I have a worksheet that is layed out as so:
A B C D E F G H I J Average 1 25 26 13 17 26 2 33 26 32 25 15 65 17 25 3 45 61 33 12 42 What I am needing is a formula that will look at a single row and starting from Right to Left find the last five numbers in my row and average them out. There will be some rows that have blank info but would need it to skip the blank cells to locate the first cell with a numerical entry. Is there such a formual? -- Sandi Gardner |
Average Function that Counts
Try something like this:
For your data in Cells B2:L2 A2: =SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B 2:L2<0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}) ,0))*B2:L2)/5 or If there may be less than 5 entries A2: =SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B 2:L2<0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}) ,0))*B2:L2)/MIN(5,SUMPRODUCT(--(B2:L2<0))) Note: Commit both of those array formulas by holding down the [Ctrl][Shift] keys and press [Enter]. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Sandi" wrote: I have a worksheet that is layed out as so: A B C D E F G H I J Average 1 25 26 13 17 26 2 33 26 32 25 15 65 17 25 3 45 61 33 12 42 What I am needing is a formula that will look at a single row and starting from Right to Left find the last five numbers in my row and average them out. There will be some rows that have blank info but would need it to skip the blank cells to locate the first cell with a numerical entry. Is there such a formual? -- Sandi Gardner |
Average Function that Counts
Hi Ron - Thanks for the reply! I tried what you listed and unfortunately
that didn't work. I keep getting 0's for my answer. I need for excel to read the rows from right to left and find the first 5 numerical entries that it comes to, add those numbers together and take an average of those numbers. Any help would be appreciated. Thanks so much -- Sandi Gardner "Ron Coderre" wrote: Try something like this: For your data in Cells B2:L2 A2: =SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B 2:L2<0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}) ,0))*B2:L2)/5 or If there may be less than 5 entries A2: =SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B 2:L2<0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}) ,0))*B2:L2)/MIN(5,SUMPRODUCT(--(B2:L2<0))) Note: Commit both of those array formulas by holding down the [Ctrl][Shift] keys and press [Enter]. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Sandi" wrote: I have a worksheet that is layed out as so: A B C D E F G H I J Average 1 25 26 13 17 26 2 33 26 32 25 15 65 17 25 3 45 61 33 12 42 What I am needing is a formula that will look at a single row and starting from Right to Left find the last five numbers in my row and average them out. There will be some rows that have blank info but would need it to skip the blank cells to locate the first cell with a numerical entry. Is there such a formual? -- Sandi Gardner |
Average Function that Counts
Try this variation, which is more tailored to the example you posted:
For sproradic values in A1:J1 K1: =SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A 1:J1<0)*COLUMN(A1:J1))+((A1:J1=0)*0),{1,2,3,4,5}) ,0))*A1:J1)/5 Or, again, if there may be less than 5 items and you want the average of the available values: =SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A 1:J1<0)*COLUMN(A1:J1))+((A1:J1=0)*0),{1,2,3,4,5}) ,0))*A1:J1)/MIN(5,SUMPRODUCT(--(A1:J1<0))) Note: Commit those array formulas by holding down the [Ctrl][Shift] keys and press [Enter]. Using the data you posted, those formulas return: K1: 21.4 K2: 29.4 K3: 38.6 Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Sandi" wrote: Hi Ron - Thanks for the reply! I tried what you listed and unfortunately that didn't work. I keep getting 0's for my answer. I need for excel to read the rows from right to left and find the first 5 numerical entries that it comes to, add those numbers together and take an average of those numbers. Any help would be appreciated. Thanks so much -- Sandi Gardner "Ron Coderre" wrote: Try something like this: For your data in Cells B2:L2 A2: =SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B 2:L2<0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}) ,0))*B2:L2)/5 or If there may be less than 5 entries A2: =SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B 2:L2<0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}) ,0))*B2:L2)/MIN(5,SUMPRODUCT(--(B2:L2<0))) Note: Commit both of those array formulas by holding down the [Ctrl][Shift] keys and press [Enter]. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Sandi" wrote: I have a worksheet that is layed out as so: A B C D E F G H I J Average 1 25 26 13 17 26 2 33 26 32 25 15 65 17 25 3 45 61 33 12 42 What I am needing is a formula that will look at a single row and starting from Right to Left find the last five numbers in my row and average them out. There will be some rows that have blank info but would need it to skip the blank cells to locate the first cell with a numerical entry. Is there such a formual? -- Sandi Gardner |
Average Function that Counts
Ron - you're a genious - worked perfectly!!!! Thanks so much!!!!
-- Sandi Gardner "Ron Coderre" wrote: Try this variation, which is more tailored to the example you posted: For sproradic values in A1:J1 K1: =SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A 1:J1<0)*COLUMN(A1:J1))+((A1:J1=0)*0),{1,2,3,4,5}) ,0))*A1:J1)/5 Or, again, if there may be less than 5 items and you want the average of the available values: =SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A 1:J1<0)*COLUMN(A1:J1))+((A1:J1=0)*0),{1,2,3,4,5}) ,0))*A1:J1)/MIN(5,SUMPRODUCT(--(A1:J1<0))) Note: Commit those array formulas by holding down the [Ctrl][Shift] keys and press [Enter]. Using the data you posted, those formulas return: K1: 21.4 K2: 29.4 K3: 38.6 Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Sandi" wrote: Hi Ron - Thanks for the reply! I tried what you listed and unfortunately that didn't work. I keep getting 0's for my answer. I need for excel to read the rows from right to left and find the first 5 numerical entries that it comes to, add those numbers together and take an average of those numbers. Any help would be appreciated. Thanks so much -- Sandi Gardner "Ron Coderre" wrote: Try something like this: For your data in Cells B2:L2 A2: =SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B 2:L2<0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}) ,0))*B2:L2)/5 or If there may be less than 5 entries A2: =SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B 2:L2<0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}) ,0))*B2:L2)/MIN(5,SUMPRODUCT(--(B2:L2<0))) Note: Commit both of those array formulas by holding down the [Ctrl][Shift] keys and press [Enter]. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Sandi" wrote: I have a worksheet that is layed out as so: A B C D E F G H I J Average 1 25 26 13 17 26 2 33 26 32 25 15 65 17 25 3 45 61 33 12 42 What I am needing is a formula that will look at a single row and starting from Right to Left find the last five numbers in my row and average them out. There will be some rows that have blank info but would need it to skip the blank cells to locate the first cell with a numerical entry. Is there such a formual? -- Sandi Gardner |
Average Function that Counts
Thanks for the update.
I'm glad that worked for you, Sandi. *********** Regards, Ron XL2002, WinXP-Pro "Sandi" wrote: Ron - you're a genious - worked perfectly!!!! Thanks so much!!!! -- Sandi Gardner "Ron Coderre" wrote: Try this variation, which is more tailored to the example you posted: For sproradic values in A1:J1 K1: =SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A 1:J1<0)*COLUMN(A1:J1))+((A1:J1=0)*0),{1,2,3,4,5}) ,0))*A1:J1)/5 Or, again, if there may be less than 5 items and you want the average of the available values: =SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A 1:J1<0)*COLUMN(A1:J1))+((A1:J1=0)*0),{1,2,3,4,5}) ,0))*A1:J1)/MIN(5,SUMPRODUCT(--(A1:J1<0))) Note: Commit those array formulas by holding down the [Ctrl][Shift] keys and press [Enter]. Using the data you posted, those formulas return: K1: 21.4 K2: 29.4 K3: 38.6 Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Sandi" wrote: Hi Ron - Thanks for the reply! I tried what you listed and unfortunately that didn't work. I keep getting 0's for my answer. I need for excel to read the rows from right to left and find the first 5 numerical entries that it comes to, add those numbers together and take an average of those numbers. Any help would be appreciated. Thanks so much -- Sandi Gardner "Ron Coderre" wrote: Try something like this: For your data in Cells B2:L2 A2: =SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B 2:L2<0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}) ,0))*B2:L2)/5 or If there may be less than 5 entries A2: =SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B 2:L2<0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}) ,0))*B2:L2)/MIN(5,SUMPRODUCT(--(B2:L2<0))) Note: Commit both of those array formulas by holding down the [Ctrl][Shift] keys and press [Enter]. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Sandi" wrote: I have a worksheet that is layed out as so: A B C D E F G H I J Average 1 25 26 13 17 26 2 33 26 32 25 15 65 17 25 3 45 61 33 12 42 What I am needing is a formula that will look at a single row and starting from Right to Left find the last five numbers in my row and average them out. There will be some rows that have blank info but would need it to skip the blank cells to locate the first cell with a numerical entry. Is there such a formual? -- Sandi Gardner |
Average Function that Counts
A shorter version
=AVERAGE(J1:INDEX(A1:J1,LARGE(IF(A1:J1<"",COLUMN( A1:J1)),5))) array entered, if less than 5 entries =IF(COUNT( A1:J1)<5,AVERAGE(A1:J1),AVERAGE(J1:INDEX(A1:J1,LAR GE(IF(A1:J1<"",COLUMN(A1:J1)),5)))) -- Regards, Peo Sjoblom Portland, Oregon "Ron Coderre" wrote in message ... Thanks for the update. I'm glad that worked for you, Sandi. *********** Regards, Ron XL2002, WinXP-Pro "Sandi" wrote: Ron - you're a genious - worked perfectly!!!! Thanks so much!!!! -- Sandi Gardner "Ron Coderre" wrote: Try this variation, which is more tailored to the example you posted: For sproradic values in A1:J1 K1: =SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A 1:J1<0)*COLUMN(A1:J1))+((A1:J1=0)*0),{1,2,3,4,5}) ,0))*A1:J1)/5 Or, again, if there may be less than 5 items and you want the average of the available values: =SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A 1:J1<0)*COLUMN(A1:J1))+((A1:J1=0)*0),{1,2,3,4,5}) ,0))*A1:J1)/MIN(5,SUMPRODUCT(--(A1:J1<0))) Note: Commit those array formulas by holding down the [Ctrl][Shift] keys and press [Enter]. Using the data you posted, those formulas return: K1: 21.4 K2: 29.4 K3: 38.6 Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Sandi" wrote: Hi Ron - Thanks for the reply! I tried what you listed and unfortunately that didn't work. I keep getting 0's for my answer. I need for excel to read the rows from right to left and find the first 5 numerical entries that it comes to, add those numbers together and take an average of those numbers. Any help would be appreciated. Thanks so much -- Sandi Gardner "Ron Coderre" wrote: Try something like this: For your data in Cells B2:L2 A2: =SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B 2:L2<0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}) ,0))*B2:L2)/5 or If there may be less than 5 entries A2: =SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B 2:L2<0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}) ,0))*B2:L2)/MIN(5,SUMPRODUCT(--(B2:L2<0))) Note: Commit both of those array formulas by holding down the [Ctrl][Shift] keys and press [Enter]. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Sandi" wrote: I have a worksheet that is layed out as so: A B C D E F G H I J Average 1 25 26 13 17 26 2 33 26 32 25 15 65 17 25 3 45 61 33 12 42 What I am needing is a formula that will look at a single row and starting from Right to Left find the last five numbers in my row and average them out. There will be some rows that have blank info but would need it to skip the blank cells to locate the first cell with a numerical entry. Is there such a formual? -- Sandi Gardner |
Average Function that Counts
Thanks, Peo...those are better.
And building on your approach...this one, which includes the option for less than 5 items, is even shorter: =AVERAGE(J1:INDEX(A1:J1,LARGE(IF(A1:J1<"",COLUMN( A1:J1)),MIN(COUNT(A1:J1),5)))) I'll be saving your first one and this one for futue use (until somebody comes up with better ones!) *********** Best Regards, Ron XL2002, WinXP-Pro "Peo Sjoblom" wrote: A shorter version =AVERAGE(J1:INDEX(A1:J1,LARGE(IF(A1:J1<"",COLUMN( A1:J1)),5))) array entered, if less than 5 entries =IF(COUNT( A1:J1)<5,AVERAGE(A1:J1),AVERAGE(J1:INDEX(A1:J1,LAR GE(IF(A1:J1<"",COLUMN(A1:J1)),5)))) -- Regards, Peo Sjoblom Portland, Oregon "Ron Coderre" wrote in message ... Thanks for the update. I'm glad that worked for you, Sandi. *********** Regards, Ron XL2002, WinXP-Pro "Sandi" wrote: Ron - you're a genious - worked perfectly!!!! Thanks so much!!!! -- Sandi Gardner "Ron Coderre" wrote: Try this variation, which is more tailored to the example you posted: For sproradic values in A1:J1 K1: =SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A 1:J1<0)*COLUMN(A1:J1))+((A1:J1=0)*0),{1,2,3,4,5}) ,0))*A1:J1)/5 Or, again, if there may be less than 5 items and you want the average of the available values: =SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A 1:J1<0)*COLUMN(A1:J1))+((A1:J1=0)*0),{1,2,3,4,5}) ,0))*A1:J1)/MIN(5,SUMPRODUCT(--(A1:J1<0))) Note: Commit those array formulas by holding down the [Ctrl][Shift] keys and press [Enter]. Using the data you posted, those formulas return: K1: 21.4 K2: 29.4 K3: 38.6 Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Sandi" wrote: Hi Ron - Thanks for the reply! I tried what you listed and unfortunately that didn't work. I keep getting 0's for my answer. I need for excel to read the rows from right to left and find the first 5 numerical entries that it comes to, add those numbers together and take an average of those numbers. Any help would be appreciated. Thanks so much -- Sandi Gardner "Ron Coderre" wrote: Try something like this: For your data in Cells B2:L2 A2: =SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B 2:L2<0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}) ,0))*B2:L2)/5 or If there may be less than 5 entries A2: =SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:L2),LARGE(((B 2:L2<0)*COLUMN(B2:L2))+((B2:L2=0)*0),{1,2,3,4,5}) ,0))*B2:L2)/MIN(5,SUMPRODUCT(--(B2:L2<0))) Note: Commit both of those array formulas by holding down the [Ctrl][Shift] keys and press [Enter]. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Sandi" wrote: I have a worksheet that is layed out as so: A B C D E F G H I J Average 1 25 26 13 17 26 2 33 26 32 25 15 65 17 25 3 45 61 33 12 42 What I am needing is a formula that will look at a single row and starting from Right to Left find the last five numbers in my row and average them out. There will be some rows that have blank info but would need it to skip the blank cells to locate the first cell with a numerical entry. Is there such a formual? -- Sandi Gardner |
| All times are GMT +1. The time now is 08:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com