|
Complex Averages
Hello, I need to design a spreadsheet that averages the last 5 and last 10
numbers in a row of up to 40 numbers. There will be various random spaces between the entered numbers. So for example, if there are 15 numbers spread over 40 rows, I would need to average the last 5 which could each have a few spaces between them. There will be no 0's or negatives in the data. Thanks |
Complex Averages
What should happen if there aren't 5 or 10 numbers to average?
-- Biff Microsoft Excel MVP "kjal" wrote in message ... Hello, I need to design a spreadsheet that averages the last 5 and last 10 numbers in a row of up to 40 numbers. There will be various random spaces between the entered numbers. So for example, if there are 15 numbers spread over 40 rows, I would need to average the last 5 which could each have a few spaces between them. There will be no 0's or negatives in the data. Thanks |
Complex Averages
Hey there. If there are only 5 numbers at some point, the 5 and 10 would be
the same. I hope that helps. Basically, over the course of 3 or so months, these numbers will begin to be added to the rows and by the end, there will be somewhere around 30 numbers out of 40 potential leaving 10 random spaces in each column assuming 40 max. So the last 5 and last 10 averages will develop and deviate as more data is entered. Thanks "T. Valko" wrote: What should happen if there aren't 5 or 10 numbers to average? -- Biff Microsoft Excel MVP "kjal" wrote in message ... Hello, I need to design a spreadsheet that averages the last 5 and last 10 numbers in a row of up to 40 numbers. There will be various random spaces between the entered numbers. So for example, if there are 15 numbers spread over 40 rows, I would need to average the last 5 which could each have a few spaces between them. There will be no 0's or negatives in the data. Thanks |
Complex Averages
But if you are putting numbers into random cells in the range, how
would you know which were the last 5 or last 10 entered? Do you not mean last by date of entry, but rather last in the sequence of data? Pete On Sep 25, 9:32 pm, kjal wrote: Hey there. If there are only 5 numbers at some point, the 5 and 10 would be the same. I hope that helps. Basically, over the course of 3 or so months, these numbers will begin to be added to the rows and by the end, there will be somewhere around 30 numbers out of 40 potential leaving 10 random spaces in each column assuming 40 max. So the last 5 and last 10 averages will develop and deviate as more data is entered. Thanks "T. Valko" wrote: What should happen if there aren't 5 or 10 numbers to average? -- Biff Microsoft Excel MVP "kjal" wrote in message ... Hello, I need to design a spreadsheet that averages the last 5 and last 10 numbers in a row of up to 40 numbers. There will be various random spaces between the entered numbers. So for example, if there are 15 numbers spread over 40 rows, I would need to average the last 5 which could each have a few spaces between them. There will be no 0's or negatives in the data. Thanks- Hide quoted text - - Show quoted text - |
Complex Averages
Hmmm...
Well, let's start with this and go from there. This will average the last 5 numbers in the range A1:J1. Array entered** : =AVERAGE(J1:INDEX(A1:J1,LARGE(IF(ISNUMBER(A1:J1),C OLUMN(A1:J1)-MIN(COLUMN(A1:J1))+1),5))) If there are less than 5 numbers the formula will return a #NUM! error. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "kjal" wrote in message ... Hey there. If there are only 5 numbers at some point, the 5 and 10 would be the same. I hope that helps. Basically, over the course of 3 or so months, these numbers will begin to be added to the rows and by the end, there will be somewhere around 30 numbers out of 40 potential leaving 10 random spaces in each column assuming 40 max. So the last 5 and last 10 averages will develop and deviate as more data is entered. Thanks "T. Valko" wrote: What should happen if there aren't 5 or 10 numbers to average? -- Biff Microsoft Excel MVP "kjal" wrote in message ... Hello, I need to design a spreadsheet that averages the last 5 and last 10 numbers in a row of up to 40 numbers. There will be various random spaces between the entered numbers. So for example, if there are 15 numbers spread over 40 rows, I would need to average the last 5 which could each have a few spaces between them. There will be no 0's or negatives in the data. Thanks |
Complex Averages
Maybe I am not explaining well or maybe it is not possible to average the
last 5 or whatever numbers of a list of numbers that has spaces but no zeros. The range in the avg formula will be c2:c42, and the numbers will ultimatley fill 3/4 of the fields with random spaces for the balance, So the formula, hopefully, will avg the last 5 NUMBERS in the row, ignoring the spaces. So the numbers in the avg of the last 5 may be spread over 7 or 8 spaces. "Pete_UK" wrote: But if you are putting numbers into random cells in the range, how would you know which were the last 5 or last 10 entered? Do you not mean last by date of entry, but rather last in the sequence of data? Pete On Sep 25, 9:32 pm, kjal wrote: Hey there. If there are only 5 numbers at some point, the 5 and 10 would be the same. I hope that helps. Basically, over the course of 3 or so months, these numbers will begin to be added to the rows and by the end, there will be somewhere around 30 numbers out of 40 potential leaving 10 random spaces in each column assuming 40 max. So the last 5 and last 10 averages will develop and deviate as more data is entered. Thanks "T. Valko" wrote: What should happen if there aren't 5 or 10 numbers to average? -- Biff Microsoft Excel MVP "kjal" wrote in message ... Hello, I need to design a spreadsheet that averages the last 5 and last 10 numbers in a row of up to 40 numbers. There will be various random spaces between the entered numbers. So for example, if there are 15 numbers spread over 40 rows, I would need to average the last 5 which could each have a few spaces between them. There will be no 0's or negatives in the data. Thanks- Hide quoted text - - Show quoted text - |
Complex Averages
Try this array formula** :
=AVERAGE(C42:INDEX(C2:C42,LARGE(IF(ISNUMBER(C2:C42 ),ROW(C2:C42)-MIN(ROW(C2:C42))+1),5))) If there are less than 5 numbers the formula will return a #NUM! error. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "kjal" wrote in message ... Maybe I am not explaining well or maybe it is not possible to average the last 5 or whatever numbers of a list of numbers that has spaces but no zeros. The range in the avg formula will be c2:c42, and the numbers will ultimatley fill 3/4 of the fields with random spaces for the balance, So the formula, hopefully, will avg the last 5 NUMBERS in the row, ignoring the spaces. So the numbers in the avg of the last 5 may be spread over 7 or 8 spaces. "Pete_UK" wrote: But if you are putting numbers into random cells in the range, how would you know which were the last 5 or last 10 entered? Do you not mean last by date of entry, but rather last in the sequence of data? Pete On Sep 25, 9:32 pm, kjal wrote: Hey there. If there are only 5 numbers at some point, the 5 and 10 would be the same. I hope that helps. Basically, over the course of 3 or so months, these numbers will begin to be added to the rows and by the end, there will be somewhere around 30 numbers out of 40 potential leaving 10 random spaces in each column assuming 40 max. So the last 5 and last 10 averages will develop and deviate as more data is entered. Thanks "T. Valko" wrote: What should happen if there aren't 5 or 10 numbers to average? -- Biff Microsoft Excel MVP "kjal" wrote in message ... Hello, I need to design a spreadsheet that averages the last 5 and last 10 numbers in a row of up to 40 numbers. There will be various random spaces between the entered numbers. So for example, if there are 15 numbers spread over 40 rows, I would need to average the last 5 which could each have a few spaces between them. There will be no 0's or negatives in the data. Thanks- Hide quoted text - - Show quoted text - |
Complex Averages
So, I can now see that you are talking about a column of data - in
your first post you said the numbers were in a row !! When you talk about "spaces" do you really mean just empty cells, or will there be a <space in those cells? Some people use a <space to "delete" an entry. Pete On Sep 25, 10:02 pm, kjal wrote: Maybe I am not explaining well or maybe it is not possible to average the last 5 or whatever numbers of a list of numbers that has spaces but no zeros. The range in the avg formula will be c2:c42, and the numbers will ultimatley fill 3/4 of the fields with random spaces for the balance, So the formula, hopefully, will avg the last 5 NUMBERS in the row, ignoring the spaces. So the numbers in the avg of the last 5 may be spread over 7 or 8 spaces. "Pete_UK" wrote: But if you are putting numbers into random cells in the range, how would you know which were the last 5 or last 10 entered? Do you not mean last by date of entry, but rather last in the sequence of data? Pete On Sep 25, 9:32 pm, kjal wrote: Hey there. If there are only 5 numbers at some point, the 5 and 10 would be the same. I hope that helps. Basically, over the course of 3 or so months, these numbers will begin to be added to the rows and by the end, there will be somewhere around 30 numbers out of 40 potential leaving 10 random spaces in each column assuming 40 max. So the last 5 and last 10 averages will develop and deviate as more data is entered. Thanks "T. Valko" wrote: What should happen if there aren't 5 or 10 numbers to average? -- Biff Microsoft Excel MVP "kjal" wrote in message ... Hello, I need to design a spreadsheet that averages the last 5 and last 10 numbers in a row of up to 40 numbers. There will be various random spaces between the entered numbers. So for example, if there are 15 numbers spread over 40 rows, I would need to average the last 5 which could each have a few spaces between them. There will be no 0's or negatives in the data. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Complex Averages
Sorry, I thought Biff had given up, but I can now see that he's back
on the scent so I'll duck out here. Pete On Sep 25, 10:24 pm, Pete_UK wrote: So, I can now see that you are talking about a column of data - in your first post you said the numbers were in a row !! When you talk about "spaces" do you really mean just empty cells, or will there be a <space in those cells? Some people use a <space to "delete" an entry. Pete On Sep 25, 10:02 pm, kjal wrote: Maybe I am not explaining well or maybe it is not possible to average the last 5 or whatever numbers of a list of numbers that has spaces but no zeros. The range in the avg formula will be c2:c42, and the numbers will ultimatley fill 3/4 of the fields with random spaces for the balance, So the formula, hopefully, will avg the last 5 NUMBERS in the row, ignoring the spaces. So the numbers in the avg of the last 5 may be spread over 7 or 8 spaces. "Pete_UK" wrote: But if you are putting numbers into random cells in the range, how would you know which were the last 5 or last 10 entered? Do you not mean last by date of entry, but rather last in the sequence of data? Pete On Sep 25, 9:32 pm, kjal wrote: Hey there. If there are only 5 numbers at some point, the 5 and 10 would be the same. I hope that helps. Basically, over the course of 3 or so months, these numbers will begin to be added to the rows and by the end, there will be somewhere around 30 numbers out of 40 potential leaving 10 random spaces in each column assuming 40 max. So the last 5 and last 10 averages will develop and deviate as more data is entered. Thanks "T. Valko" wrote: What should happen if there aren't 5 or 10 numbers to average? -- Biff Microsoft Excel MVP "kjal" wrote in message ... Hello, I need to design a spreadsheet that averages the last 5 and last 10 numbers in a row of up to 40 numbers. There will be various random spaces between the entered numbers. So for example, if there are 15 numbers spread over 40 rows, I would need to average the last 5 which could each have a few spaces between them. There will be no 0's or negatives in the data. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Complex Averages
Would it matter if it was a row or a column? I don't think it would. I was
tying to help Biff visualize what I was talking about, and I think columns are better for that. The cells without numbers will be empty, not touched, no data, no text. Thanks for trying. KJ "Pete_UK" wrote: So, I can now see that you are talking about a column of data - in your first post you said the numbers were in a row !! When you talk about "spaces" do you really mean just empty cells, or will there be a <space in those cells? Some people use a <space to "delete" an entry. Pete On Sep 25, 10:02 pm, kjal wrote: Maybe I am not explaining well or maybe it is not possible to average the last 5 or whatever numbers of a list of numbers that has spaces but no zeros. The range in the avg formula will be c2:c42, and the numbers will ultimatley fill 3/4 of the fields with random spaces for the balance, So the formula, hopefully, will avg the last 5 NUMBERS in the row, ignoring the spaces. So the numbers in the avg of the last 5 may be spread over 7 or 8 spaces. "Pete_UK" wrote: But if you are putting numbers into random cells in the range, how would you know which were the last 5 or last 10 entered? Do you not mean last by date of entry, but rather last in the sequence of data? Pete On Sep 25, 9:32 pm, kjal wrote: Hey there. If there are only 5 numbers at some point, the 5 and 10 would be the same. I hope that helps. Basically, over the course of 3 or so months, these numbers will begin to be added to the rows and by the end, there will be somewhere around 30 numbers out of 40 potential leaving 10 random spaces in each column assuming 40 max. So the last 5 and last 10 averages will develop and deviate as more data is entered. Thanks "T. Valko" wrote: What should happen if there aren't 5 or 10 numbers to average? -- Biff Microsoft Excel MVP "kjal" wrote in message ... Hello, I need to design a spreadsheet that averages the last 5 and last 10 numbers in a row of up to 40 numbers. There will be various random spaces between the entered numbers. So for example, if there are 15 numbers spread over 40 rows, I would need to average the last 5 which could each have a few spaces between them. There will be no 0's or negatives in the data. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Complex Averages
It would matter
-- Regards, Peo Sjoblom "kjal" wrote in message ... Would it matter if it was a row or a column? I don't think it would. I was tying to help Biff visualize what I was talking about, and I think columns are better for that. The cells without numbers will be empty, not touched, no data, no text. Thanks for trying. KJ "Pete_UK" wrote: So, I can now see that you are talking about a column of data - in your first post you said the numbers were in a row !! When you talk about "spaces" do you really mean just empty cells, or will there be a <space in those cells? Some people use a <space to "delete" an entry. Pete On Sep 25, 10:02 pm, kjal wrote: Maybe I am not explaining well or maybe it is not possible to average the last 5 or whatever numbers of a list of numbers that has spaces but no zeros. The range in the avg formula will be c2:c42, and the numbers will ultimatley fill 3/4 of the fields with random spaces for the balance, So the formula, hopefully, will avg the last 5 NUMBERS in the row, ignoring the spaces. So the numbers in the avg of the last 5 may be spread over 7 or 8 spaces. "Pete_UK" wrote: But if you are putting numbers into random cells in the range, how would you know which were the last 5 or last 10 entered? Do you not mean last by date of entry, but rather last in the sequence of data? Pete On Sep 25, 9:32 pm, kjal wrote: Hey there. If there are only 5 numbers at some point, the 5 and 10 would be the same. I hope that helps. Basically, over the course of 3 or so months, these numbers will begin to be added to the rows and by the end, there will be somewhere around 30 numbers out of 40 potential leaving 10 random spaces in each column assuming 40 max. So the last 5 and last 10 averages will develop and deviate as more data is entered. Thanks "T. Valko" wrote: What should happen if there aren't 5 or 10 numbers to average? -- Biff Microsoft Excel MVP "kjal" wrote in message ... Hello, I need to design a spreadsheet that averages the last 5 and last 10 numbers in a row of up to 40 numbers. There will be various random spaces between the entered numbers. So for example, if there are 15 numbers spread over 40 rows, I would need to average the last 5 which could each have a few spaces between them. There will be no 0's or negatives in the data. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Complex Averages
Would it matter if it was a row or a column?
Yes. I've now posted examples for both! -- Biff Microsoft Excel MVP "kjal" wrote in message ... Would it matter if it was a row or a column? I don't think it would. I was tying to help Biff visualize what I was talking about, and I think columns are better for that. The cells without numbers will be empty, not touched, no data, no text. Thanks for trying. KJ "Pete_UK" wrote: So, I can now see that you are talking about a column of data - in your first post you said the numbers were in a row !! When you talk about "spaces" do you really mean just empty cells, or will there be a <space in those cells? Some people use a <space to "delete" an entry. Pete On Sep 25, 10:02 pm, kjal wrote: Maybe I am not explaining well or maybe it is not possible to average the last 5 or whatever numbers of a list of numbers that has spaces but no zeros. The range in the avg formula will be c2:c42, and the numbers will ultimatley fill 3/4 of the fields with random spaces for the balance, So the formula, hopefully, will avg the last 5 NUMBERS in the row, ignoring the spaces. So the numbers in the avg of the last 5 may be spread over 7 or 8 spaces. "Pete_UK" wrote: But if you are putting numbers into random cells in the range, how would you know which were the last 5 or last 10 entered? Do you not mean last by date of entry, but rather last in the sequence of data? Pete On Sep 25, 9:32 pm, kjal wrote: Hey there. If there are only 5 numbers at some point, the 5 and 10 would be the same. I hope that helps. Basically, over the course of 3 or so months, these numbers will begin to be added to the rows and by the end, there will be somewhere around 30 numbers out of 40 potential leaving 10 random spaces in each column assuming 40 max. So the last 5 and last 10 averages will develop and deviate as more data is entered. Thanks "T. Valko" wrote: What should happen if there aren't 5 or 10 numbers to average? -- Biff Microsoft Excel MVP "kjal" wrote in message ... Hello, I need to design a spreadsheet that averages the last 5 and last 10 numbers in a row of up to 40 numbers. There will be various random spaces between the entered numbers. So for example, if there are 15 numbers spread over 40 rows, I would need to average the last 5 which could each have a few spaces between them. There will be no 0's or negatives in the data. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Complex Averages
FWIW,
This *array* formula will average the last 5 numbers in the range C2 to C42, And if there are *less* then 5 numbers, it will average *any* that are the =AVERAGE(C42:INDEX(C2:C42,LARGE(ROW(1:41)*(C2:C42< ""),5))) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "kjal" wrote in message ... Would it matter if it was a row or a column? I don't think it would. I was tying to help Biff visualize what I was talking about, and I think columns are better for that. The cells without numbers will be empty, not touched, no data, no text. Thanks for trying. KJ "Pete_UK" wrote: So, I can now see that you are talking about a column of data - in your first post you said the numbers were in a row !! When you talk about "spaces" do you really mean just empty cells, or will there be a <space in those cells? Some people use a <space to "delete" an entry. Pete On Sep 25, 10:02 pm, kjal wrote: Maybe I am not explaining well or maybe it is not possible to average the last 5 or whatever numbers of a list of numbers that has spaces but no zeros. The range in the avg formula will be c2:c42, and the numbers will ultimatley fill 3/4 of the fields with random spaces for the balance, So the formula, hopefully, will avg the last 5 NUMBERS in the row, ignoring the spaces. So the numbers in the avg of the last 5 may be spread over 7 or 8 spaces. "Pete_UK" wrote: But if you are putting numbers into random cells in the range, how would you know which were the last 5 or last 10 entered? Do you not mean last by date of entry, but rather last in the sequence of data? Pete On Sep 25, 9:32 pm, kjal wrote: Hey there. If there are only 5 numbers at some point, the 5 and 10 would be the same. I hope that helps. Basically, over the course of 3 or so months, these numbers will begin to be added to the rows and by the end, there will be somewhere around 30 numbers out of 40 potential leaving 10 random spaces in each column assuming 40 max. So the last 5 and last 10 averages will develop and deviate as more data is entered. Thanks "T. Valko" wrote: What should happen if there aren't 5 or 10 numbers to average? -- Biff Microsoft Excel MVP "kjal" wrote in message ... Hello, I need to design a spreadsheet that averages the last 5 and last 10 numbers in a row of up to 40 numbers. There will be various random spaces between the entered numbers. So for example, if there are 15 numbers spread over 40 rows, I would need to average the last 5 which could each have a few spaces between them. There will be no 0's or negatives in the data. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Complex Averages
Thanks Biff, that worked great. I appreciate your time on this.
KJ "T. Valko" wrote: Hmmm... Well, let's start with this and go from there. This will average the last 5 numbers in the range A1:J1. Array entered** : =AVERAGE(J1:INDEX(A1:J1,LARGE(IF(ISNUMBER(A1:J1),C OLUMN(A1:J1)-MIN(COLUMN(A1:J1))+1),5))) If there are less than 5 numbers the formula will return a #NUM! error. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "kjal" wrote in message ... Hey there. If there are only 5 numbers at some point, the 5 and 10 would be the same. I hope that helps. Basically, over the course of 3 or so months, these numbers will begin to be added to the rows and by the end, there will be somewhere around 30 numbers out of 40 potential leaving 10 random spaces in each column assuming 40 max. So the last 5 and last 10 averages will develop and deviate as more data is entered. Thanks "T. Valko" wrote: What should happen if there aren't 5 or 10 numbers to average? -- Biff Microsoft Excel MVP "kjal" wrote in message ... Hello, I need to design a spreadsheet that averages the last 5 and last 10 numbers in a row of up to 40 numbers. There will be various random spaces between the entered numbers. So for example, if there are 15 numbers spread over 40 rows, I would need to average the last 5 which could each have a few spaces between them. There will be no 0's or negatives in the data. Thanks |
Complex Averages
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "kjal" wrote in message ... Thanks Biff, that worked great. I appreciate your time on this. KJ "T. Valko" wrote: Hmmm... Well, let's start with this and go from there. This will average the last 5 numbers in the range A1:J1. Array entered** : =AVERAGE(J1:INDEX(A1:J1,LARGE(IF(ISNUMBER(A1:J1),C OLUMN(A1:J1)-MIN(COLUMN(A1:J1))+1),5))) If there are less than 5 numbers the formula will return a #NUM! error. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "kjal" wrote in message ... Hey there. If there are only 5 numbers at some point, the 5 and 10 would be the same. I hope that helps. Basically, over the course of 3 or so months, these numbers will begin to be added to the rows and by the end, there will be somewhere around 30 numbers out of 40 potential leaving 10 random spaces in each column assuming 40 max. So the last 5 and last 10 averages will develop and deviate as more data is entered. Thanks "T. Valko" wrote: What should happen if there aren't 5 or 10 numbers to average? -- Biff Microsoft Excel MVP "kjal" wrote in message ... Hello, I need to design a spreadsheet that averages the last 5 and last 10 numbers in a row of up to 40 numbers. There will be various random spaces between the entered numbers. So for example, if there are 15 numbers spread over 40 rows, I would need to average the last 5 which could each have a few spaces between them. There will be no 0's or negatives in the data. Thanks |
| All times are GMT +1. The time now is 12:56 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com