If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"Average If Not Blank" Formula
I have 10 columns and 5 rows of cells. All cells do not
contain a number and the cells which DO contain numbers are sometimes 1, 2, or 3 digits. I need to enter a formula to the right of the grid of cells to average the 2 most right column's cells. For instance, in row 1, if cells A1, A2, and A3 contained numbers 2, 33, 40 respectively, I need to have the formula skip looking at cells A4 through A10 and average the cells containing numbers. Any ideas? |
#2
|
|||
|
|||
"Average If Not Blank" Formula
Have you trie the AVERAGE function?
-- Don Guillett SalesAid Software "CindyA" wrote in message ... I have 10 columns and 5 rows of cells. All cells do not contain a number and the cells which DO contain numbers are sometimes 1, 2, or 3 digits. I need to enter a formula to the right of the grid of cells to average the 2 most right column's cells. For instance, in row 1, if cells A1, A2, and A3 contained numbers 2, 33, 40 respectively, I need to have the formula skip looking at cells A4 through A10 and average the cells containing numbers. Any ideas? |
#3
|
|||
|
|||
"Average If Not Blank" Formula
Yes, but I only need the LAST 2 right-most cells in the
range of 10 cells. -----Original Message----- Have you trie the AVERAGE function? -- Don Guillett SalesAid Software "CindyA" wrote in message ... I have 10 columns and 5 rows of cells. All cells do not contain a number and the cells which DO contain numbers are sometimes 1, 2, or 3 digits. I need to enter a formula to the right of the grid of cells to average the 2 most right column's cells. For instance, in row 1, if cells A1, A2, and A3 contained numbers 2, 33, 40 respectively, I need to have the formula skip looking at cells A4 through A10 and average the cells containing numbers. Any ideas? . |
#4
|
|||
|
|||
"Average If Not Blank" Formula
P.S. In addition, the cells which contain numbers may be
different on every row. Thanks. -----Original Message----- Yes, but I only need the LAST 2 right-most cells in the range of 10 cells. -----Original Message----- Have you trie the AVERAGE function? -- Don Guillett SalesAid Software "CindyA" wrote in message ... I have 10 columns and 5 rows of cells. All cells do not contain a number and the cells which DO contain numbers are sometimes 1, 2, or 3 digits. I need to enter a formula to the right of the grid of cells to average the 2 most right column's cells. For instance, in row 1, if cells A1, A2, and A3 contained numbers 2, 33, 40 respectively, I need to have the formula skip looking at cells A4 through A10 and average the cells containing numbers. Any ideas? . . |
#5
|
|||
|
|||
"Average If Not Blank" Formula
Hi
do you want the average for a column or for a row. Looking at your other post I assume you want the average per column (e.g. from A1:J1) BUT also considering to take only the last to filled cells. I assume that you don't have blank cells in between per row (e.g. if D1 is filled A1:C1 is filled also) Try the following formula in cell K1 =AVERAGE(OFFSET($A1,COUNTA($A1:$J1)-1,1,-2)) and copy this down -----Original Message----- I have 10 columns and 5 rows of cells. All cells do not contain a number and the cells which DO contain numbers are sometimes 1, 2, or 3 digits. I need to enter a formula to the right of the grid of cells to average the 2 most right column's cells. For instance, in row 1, if cells A1, A2, and A3 contained numbers 2, 33, 40 respectively, I need to have the formula skip looking at cells A4 through A10 and average the cells containing numbers. Any ideas? . |
#6
|
|||
|
|||
"Average If Not Blank" Formula
Cindy,
The average function will only calculate cells that contain data. In your example A1, A2, and A3 contained numbers 2, 33, 40. If you use =AVERAGE(A1:E10) the return will be 25. If the cells contain a "0" Zero the "0" is considered in the calculation. Cells that are truly empty will not figure in the calculation. If this is not what you are looking for perhaps you could post some sample data. Charlie O'Neill -----Original Message----- P.S. In addition, the cells which contain numbers may be different on every row. Thanks. -----Original Message----- Yes, but I only need the LAST 2 right-most cells in the range of 10 cells. -----Original Message----- Have you trie the AVERAGE function? -- Don Guillett SalesAid Software "CindyA" wrote in message .. . I have 10 columns and 5 rows of cells. All cells do not contain a number and the cells which DO contain numbers are sometimes 1, 2, or 3 digits. I need to enter a formula to the right of the grid of cells to average the 2 most right column's cells. For instance, in row 1, if cells A1, A2, and A3 contained numbers 2, 33, 40 respectively, I need to have the formula skip looking at cells A4 through A10 and average the cells containing numbers. Any ideas? . . . |
#7
|
|||
|
|||
"Average If Not Blank" Formula
Here is a sample. Thanks for taking the time to look at
this. A1=10 B1=8 C1=22 D1=4 E1=55 Cells F1 through J1 are blank. In cell K1 I want the formula to look left, determine which are the last TWO cells to contain numbers and average just those two cells. So in this example, only Cells D1 and E1 would be considered. Cells A1, B1, C1, as well as F1 through J1 would be ignored. -----Original Message----- Cindy, The average function will only calculate cells that contain data. In your example A1, A2, and A3 contained numbers 2, 33, 40. If you use =AVERAGE(A1:E10) the return will be 25. If the cells contain a "0" Zero the "0" is considered in the calculation. Cells that are truly empty will not figure in the calculation. If this is not what you are looking for perhaps you could post some sample data. Charlie O'Neill -----Original Message----- P.S. In addition, the cells which contain numbers may be different on every row. Thanks. -----Original Message----- Yes, but I only need the LAST 2 right-most cells in the range of 10 cells. -----Original Message----- Have you trie the AVERAGE function? -- Don Guillett SalesAid Software "CindyA" wrote in message . .. I have 10 columns and 5 rows of cells. All cells do not contain a number and the cells which DO contain numbers are sometimes 1, 2, or 3 digits. I need to enter a formula to the right of the grid of cells to average the 2 most right column's cells. For instance, in row 1, if cells A1, A2, and A3 contained numbers 2, 33, 40 respectively, I need to have the formula skip looking at cells A4 through A10 and average the cells containing numbers. Any ideas? . . . . |
#8
|
|||
|
|||
"Average If Not Blank" Formula
Works great if all cells are filled in, but unfortunately,
I periodically will have a blank cell in the mix. Any solution to this? Thanks in advance. -----Original Message----- Hi do you want the average for a column or for a row. Looking at your other post I assume you want the average per column (e.g. from A1:J1) BUT also considering to take only the last to filled cells. I assume that you don't have blank cells in between per row (e.g. if D1 is filled A1:C1 is filled also) Try the following formula in cell K1 =AVERAGE(OFFSET($A1,COUNTA($A1:$J1)-1,1,-2)) and copy this down -----Original Message----- I have 10 columns and 5 rows of cells. All cells do not contain a number and the cells which DO contain numbers are sometimes 1, 2, or 3 digits. I need to enter a formula to the right of the grid of cells to average the 2 most right column's cells. For instance, in row 1, if cells A1, A2, and A3 contained numbers 2, 33, 40 respectively, I need to have the formula skip looking at cells A4 through A10 and average the cells containing numbers. Any ideas? . . |
#9
|
|||
|
|||
"Average If Not Blank" Formula
On Tue, 4 May 2004 08:17:59 -0700, "CindyA"
wrote: Here is a sample. Thanks for taking the time to look at this. A1=10 B1=8 C1=22 D1=4 E1=55 Cells F1 through J1 are blank. In cell K1 I want the formula to look left, determine which are the last TWO cells to contain numbers and average just those two cells. So in this example, only Cells D1 and E1 would be considered. Cells A1, B1, C1, as well as F1 through J1 would be ignored. This *array-entered* formula will do what you want, I think: =AVERAGE(TRANSPOSE(OFFSET(A1,0,LARGE(ISNUMBER(A1:J 1)*COLUMN(A1:J1),{1,2})-1))) To *array-enter* a formula, after typing or pasting the formula, hold down ctrlshift while hitting enter. Excel will place braces {...} around the formula if you did this correctly. You can array-enter the formula in K1, and then drag it down as many rows as you have. --ron |
#10
|
|||
|
|||
"Average If Not Blank" Formula
Hi
sure :-) try the following array formula (entered with CTRL+SHIFT+ENTER =AVERAGE(OFFSET($J1,0,0,1,-(COLUMN($J1)-LARGE(IF($A1:$J1"",COLUMN($A1 :$J1)),2)+1))) -- Regards Frank Kabel Frankfurt, Germany CindyA wrote: Works great if all cells are filled in, but unfortunately, I periodically will have a blank cell in the mix. Any solution to this? Thanks in advance. -----Original Message----- Hi do you want the average for a column or for a row. Looking at your other post I assume you want the average per column (e.g. from A1:J1) BUT also considering to take only the last to filled cells. I assume that you don't have blank cells in between per row (e.g. if D1 is filled A1:C1 is filled also) Try the following formula in cell K1 =AVERAGE(OFFSET($A1,COUNTA($A1:$J1)-1,1,-2)) and copy this down -----Original Message----- I have 10 columns and 5 rows of cells. All cells do not contain a number and the cells which DO contain numbers are sometimes 1, 2, or 3 digits. I need to enter a formula to the right of the grid of cells to average the 2 most right column's cells. For instance, in row 1, if cells A1, A2, and A3 contained numbers 2, 33, 40 respectively, I need to have the formula skip looking at cells A4 through A10 and average the cells containing numbers. Any ideas? . . |
|
Thread Tools | |
Display Modes | |
|
|