Average based on two rows and two criterea?

Caution: I have a tendency to go to San Fran by way of
Miami. With that disclaimer...

In FIRST column to right of Grand Total, enter: =IF
(e10,1,0)
NEXT column to right, enter: =IF(j10,1,0)
NEXT column to right, enter: =IF(o10,1,0)
CONTINUE this pattern until all 'sub-totals' are sample by
similar IF statement.
NEXT column to right, enter: =SUM(?1:??1) [This will sum
all IF statement values for the row!]
COPY the above entries into every data row of your
spreadsheet. [Same columns]
Sum all the =SUM's to get total number of non-zero 'Total'
cells.

Perhaps I did not understand your goal. If this is not
what you are looking for, maybe this will spark another
idea.

One of the gurus monitoring this list may put you onto a
quicker VBA or Excel solution. For those of us who do not
have the VB background, we have to find other ways to do
things.

GL

gary b





-----Original Message-----
Hi All,

I'm new on this forum, so if a solution to this question
has been given
already please let me know.

Here's the situation:

In the first row of data a number of reoccurring column
labels appear
with a subtotal:

A - B - C - D - Total - A - B - C - D - Total - A -
etc... ...- Grand
Total

The sum of the "Total" columns per row is a simple sumif
function which
works fine.

Now I want to count all the non-zero values in
the "Total" columns. It
sounds so simple, but I can't figure out a formula to do
it..

So the count is based on the label row and an underlying
data row with
each a criterea in the same column.

Is this a VB case and if so, could anyone give me a lead
to get on?

I've been trying all kinds of combined formulas with
LOOKUPs, OFFSETs,
ANDs, IFs, COUNTIFs and what not...

With this count an average would be simple to calculate.

Many thanks!

Exceller


---
Message posted from http://www.ExcelForum.com/

.

Anonymous wrote:
[b]Caution: I have a tendency to go to San Fran by way of
Miami. With that disclaimer...


Haha, I do the same thing...

What I have used in the mean time as the non-"total" fields are not
populated by zeros is a count of all "total" labels minus the count of
the zeros in the rows below.

Sample plus error trapment:

=IF(ISERROR(SUMIF($F$5:$GC$42,"Total",F7:HN7)/(COUNTIF($F$5:$GC$5,"total")-(COUNTIF(F7:GC7,0)))-1),"-",SUMIF($F$5:$GC$42,"Total",F7:HN7)/(COUNTIF($F$5:$GC$5,"total")-(COUNTIF(F7:GC7,0))))

The length of this spreadsheet is constantly changing.

You have given me a good idea though, every new idea is welcome!


---
Message posted from http://www.ExcelForum.com/

If you insist on having a formula without auxiliary computations...

=AVERAGE(IF((MOD(COLUMN($A2:$O2)-CELL("Col",$A2)+1,5)=0)*$A2:$O2,$A2:$O2))

which must be confirmed with control+shift+enter instead of just with enter.

"exceller" wrote in message
...
I have the solution:

Too much to explain, so check the attached file.

Attachment filename: example.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=380918
---
Message posted from http://www.ExcelForum.com/