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 |
#11
|
|||
|
|||
"Average If Not Blank" Formula
Thanks, I'll give it a try.
-----Original Message----- 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? . . . |
#12
|
|||
|
|||
"Average If Not Blank" Formula
Thanks, I'll give it a try.
-----Original Message----- 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 . |
|
Thread Tools | |
Display Modes | |
|
|