Understanding syntax to average values

**bubba57** · 02-22-2009, 06:43 PM

Hello All,

I am in need of help to solve a problem in a spreadsheet I have been working on. I need to average a set of values from several columns and from certain cells in those columns, for example................

BA3 BA23 BA45 BA66 BA87 BA108 BA129 BA150 BA171 BA192 BA213

Also the same cells from columns BB BO and so on, and this would continue across other columns and as far down as cell 7663.

I have a formula to average across connected cells that works fine.

Please Login or Register  to view this content.

I just can't wrap my head around the correct way to do the same thing from non connected locations. I also need to be able to have the formula not return the dreaded #Div/0 as not all cells will have a number to return, and the average needs to be correct with cells that have 0s in them.

I would like to be able to attach this file so someone could take a look at it but it at this time is over 123mb in size, so that might not be possible through this forum.

If anyone can point me in the right direction I would be MOST grateful.

In case anyone would need to see this file to fully understand what I am wanting to do I can upload it to the You Send It site and provide a download link.
I began building this file in Excel 2000 until I ran into the 64mb limit, then started using Excel 2007 preserving the xls format.

**shg** · 02-22-2009, 08:12 PM

Assuming you have a typo in your example and BA23 should be BA24, and that you want to average only the numbers greater than zero,

=SUMPRODUCT( (MOD(ROW(BA3:BA7663)-3, 21) = 0) * (BA3:BA7663>0) * BA3:BA7663) / SUMPRODUCT( (MOD(ROW(BA3:BA7663) - 3, 21) = 0) * (BA3:BA7663>0) )

**bubba57** · 02-23-2009, 01:54 AM

Thank you shg for your reply.

Your formula works great, and you are right there was a typo but perhaps I should have been more clear.
I need to include all three columns BA, BB and BO with those cell locations to obtain an overall average for the correct answer.

For example I will need to look at the BA3 group and the BB3 group and the BO3 group for a total over all average of three possible data entries.

Also the end stop would not have to be the 7663, but that is something I can modify as needed. Your formula is below with what I would call the end stop for that reporting location. Or in other words the reference would be contained to the end of weeks or months, hence the BA213,BB213 and BO213 cells would be the total entries for a category over the first two weeks of 2009 Jan 1 to Jan 11.

Please Login or Register  to view this content.

How would I also include the other groups (BB and BO) to find the average of all three for the final answer?
After removing the data in one entry location the #DIV/0! comes into play.
How can I avoid that, because their will be times when no data will be entered and it also cascades down into the other report locations even though the data for that entry is still there.

I will freely admit that I am way in over my head here and I do very much appreciate your help and insight.

**bubba57** · 02-26-2009, 11:37 AM

Update to the problem.
First a special “Thank You” to shg and everyone that gave some thought on to how to solve my issue. After some thinking about what I am wanting to do with this spreadsheet and working with my limited knowledge of Excel I have found a workaround to achieve the desired results, by building another table to reference the data points from.
In my opinion the people that built and designed Excel must have been consumed by crossword puzzles. If you want to do something that is not in line either down or across you are in for less than a fun time.
Excel is capable of doing a lot but only within certain limits, something I am now well aware of. In my attempt to make a “user friendly” data base at least a far as the inputting of the data and not fully understanding how everything works I have several design flaws that require some unique solutions. Next time I will take all of this into consideration.