Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
complex averaging
Hi All
I have a sheet with production numbers for work there are 3 columns of 35 rows. the fields are B3 - B37 C3-C37 D3-D37 in each of these columns the first entry may or may not be in the first row and there may be a gap from one entry in a column to the next. What I want to be able to do is have a formula that will do the following. find the second non blank cell in a column. And then also if there is gap in between one cell and the next to look at the second non blank cell after that and continuw to do this through row #37 of each column and then find the average of sum of those numbers. Example A 1 15 2 27 3 4 35 6 23 7 11 8 9 16 10 18 etc etc etc till row 37 in this example the formula would skip A1 A4 and A9 and give the average of the rest which I calculate to be 27+23+11+18=79 79/4= 19.75 Tom Ogilvy answered the question for me with this formula =AVERAGE(IF((B2:B36<"")*(B3:B37<""),B3:B37)) Entered with Ctrl+Shift+Enter This works very very well except in one scenario.Say for instance you have data in the first row of the column "B" and then you have data in the second row of column "C" and the second row of column "D". Until I add data in another row in column "C" and "D" it give me a #DIV/0! error at the bottom of column "C" and "D" B C D 1 100 2 250 220 3 4 5 #DIV/0! #DIV/0! My question is.... how do I use the formula and not have an error show when there is no data in the first row and there is not more than one item of data in the column Thanks for all the help it is appreciated Dan N -- crunchnin numbers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
More Complex averaging | Excel Discussion (Misc queries) | |||
complex averaging | Excel Discussion (Misc queries) | |||
complex averaging | Excel Discussion (Misc queries) | |||
complex averaging | Excel Discussion (Misc queries) | |||
Complex Averaging | Excel Worksheet Functions |