Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averaging non blanlk cells???
Hi All
I have a sheet that I want to use for production numbers at work ,and I need to be able to average them. The sheet has 3 columns of data for 3 different presses production. The cell range is B1 and B2 are a heading B3 is Blank. The data will be entered in cell. B4 to B38. this is also the same for column C and column D The first entry in each column will not be used to figure average production and the first row in each column may or may not contain an entry. Also there may be an empty cell from one cell to the next as a press may skip a shift of production on a certain job and then continue it the next shift SO. What I need is a formula that will find the 2nd non blaqnk cell in a column as well as find the 2nd non blank cell after there is a gap between cells. Then I need this formula to find the average of all the sum of all the 2nd non blank cells in that column ( see example below) Example B C 1 Pressline Pressline 2 job name jobname 3 4 11 5 35 6 23 24 7 11 12 8 9 16 45 10 18 11 32 12 10 etc etc etc till row 38 in this example the formula would skip A4 and A9 and give the average of the rest which I calculate to be 35+23+11+18=87 87/4= 21.75 Also in column C it would skip A6 A9 and A11 and give the average for the rest which I calculate to be 12+10=22 22/2=11 I was given a formula that does this it is =AVERAGE(IF((B3:B37<"")*(B4:B38<""),B4:B38)) entered as an array it works great as long as there is data in B4 or if there are 2 cells (example) B6- B7 with data. If this is not the case it returns a #div/0! error if you have say just one cell with data in, say cell B8 and then it is blank to say cell B11 I hope I have explained this well enough Thanks Dan N -- crunchnin numbers |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averaging non blanlk cells???
try
=if(iserror(AVERAGE(IF((B3:B37<"")*(B4:B38<""),B 4:B38))),"",AVERAGE(IF((B3:B37<"")*(B4:B38<""),B 4:B38))) this will give a blank cell if there is no avaerage available. "belvy123" wrote: Hi All I have a sheet that I want to use for production numbers at work ,and I need to be able to average them. The sheet has 3 columns of data for 3 different presses production. The cell range is B1 and B2 are a heading B3 is Blank. The data will be entered in cell. B4 to B38. this is also the same for column C and column D The first entry in each column will not be used to figure average production and the first row in each column may or may not contain an entry. Also there may be an empty cell from one cell to the next as a press may skip a shift of production on a certain job and then continue it the next shift SO. What I need is a formula that will find the 2nd non blaqnk cell in a column as well as find the 2nd non blank cell after there is a gap between cells. Then I need this formula to find the average of all the sum of all the 2nd non blank cells in that column ( see example below) Example B C 1 Pressline Pressline 2 job name jobname 3 4 11 5 35 6 23 24 7 11 12 8 9 16 45 10 18 11 32 12 10 etc etc etc till row 38 in this example the formula would skip A4 and A9 and give the average of the rest which I calculate to be 35+23+11+18=87 87/4= 21.75 Also in column C it would skip A6 A9 and A11 and give the average for the rest which I calculate to be 12+10=22 22/2=11 I was given a formula that does this it is =AVERAGE(IF((B3:B37<"")*(B4:B38<""),B4:B38)) entered as an array it works great as long as there is data in B4 or if there are 2 cells (example) B6- B7 with data. If this is not the case it returns a #div/0! error if you have say just one cell with data in, say cell B8 and then it is blank to say cell B11 I hope I have explained this well enough Thanks Dan N -- crunchnin numbers |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averaging non blanlk cells???
Hello BJ
If you were here I would kiss you!!!! LOL Thanks soooo very much. I was thinkin It was a lost cause -- Dan N "bj" wrote: try =if(iserror(AVERAGE(IF((B3:B37<"")*(B4:B38<""),B 4:B38))),"",AVERAGE(IF((B3:B37<"")*(B4:B38<""),B 4:B38))) this will give a blank cell if there is no avaerage available. "belvy123" wrote: Hi All I have a sheet that I want to use for production numbers at work ,and I need to be able to average them. The sheet has 3 columns of data for 3 different presses production. The cell range is B1 and B2 are a heading B3 is Blank. The data will be entered in cell. B4 to B38. this is also the same for column C and column D The first entry in each column will not be used to figure average production and the first row in each column may or may not contain an entry. Also there may be an empty cell from one cell to the next as a press may skip a shift of production on a certain job and then continue it the next shift SO. What I need is a formula that will find the 2nd non blaqnk cell in a column as well as find the 2nd non blank cell after there is a gap between cells. Then I need this formula to find the average of all the sum of all the 2nd non blank cells in that column ( see example below) Example B C 1 Pressline Pressline 2 job name jobname 3 4 11 5 35 6 23 24 7 11 12 8 9 16 45 10 18 11 32 12 10 etc etc etc till row 38 in this example the formula would skip A4 and A9 and give the average of the rest which I calculate to be 35+23+11+18=87 87/4= 21.75 Also in column C it would skip A6 A9 and A11 and give the average for the rest which I calculate to be 12+10=22 22/2=11 I was given a formula that does this it is =AVERAGE(IF((B3:B37<"")*(B4:B38<""),B4:B38)) entered as an array it works great as long as there is data in B4 or if there are 2 cells (example) B6- B7 with data. If this is not the case it returns a #div/0! error if you have say just one cell with data in, say cell B8 and then it is blank to say cell B11 I hope I have explained this well enough Thanks Dan N -- crunchnin numbers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averaging Cells from another workbook | Excel Worksheet Functions | |||
Averaging 14 Cells | Excel Discussion (Misc queries) | |||
Averaging Cells Based On Conditions in Neighboring Cells | Excel Discussion (Misc queries) | |||
Averaging Cells | Excel Discussion (Misc queries) | |||
Averaging cells which contain #DIV/0! | Excel Worksheet Functions |