Need help with a formula

[dbconn]

I am looking for a formula that will compute an average of a number of non
contiguous cells such as G8, G16, G24, G36, etc. Each of these cells has a
formula which computes an average of a range of cells. With the helpm of
this forum, I have been able to find a formula which does that AND uses
values only when they are greater than zero and does not display #DIV/0!.
But I cannot fin a fromula that will do the next step- Take an average of
those specific cells AND use only the ones where the cell is >0, Example
G8=100, G16=85, G24 is blank, G36=75, then this formula would calculate the
average 100+85+75/3.

All help greatly appreciated

 

[Lars-Åke Aspelin]

I am looking for a formula that will compute an average of a number of non
contiguous cells such as G8, G16, G24, G36, etc. Each of these cells has a
formula which computes an average of a range of cells. With the helpm of
this forum, I have been able to find a formula which does that AND uses
values only when they are greater than zero and does not display #DIV/0!.
But I cannot fin a fromula that will do the next step- Take an average of
those specific cells AND use only the ones where the cell is >0, Example
G8=100, G16=85, G24 is blank, G36=75, then this formula would calculate the
average 100+85+75/3.

All help greatly appreciated

For you example, try this formula:

=SUM(G8,G16,G24,G36)/SUM(G8>0,G16>0,G24>0,G36>0)

If your data is more regular, say every 8th row, ie G32 rather than
G36, you may try something like this:

=SUMPRODUCT((G8:G32)*(MOD(ROW(G8:G32),8)=0))/SUMPRODUCT((G8:G32>0)*(MOD(ROW(G8:G32),8)=0))

This is a longer formula if you only have a few data, but if you have
data in every 8th row from cell G8 to say cell G400, then this is
shorter.

Hope this helps / Lars-Åke

 

[David Biddulph]

I hope that you want (100+85+75)/3 rather than 100+85+75/3 ?

If G8=100, G16=85, G24 is blank, G36=75, the formula
=AVERAGE(G8, G16, G24, G36)
will give (100+85+75)/3, and you can avoid a #DIV/0! error by using
=IF(COUNT(G8, G16, G24, G36)=0,"",AVERAGE(G8, G16, G24, G36))

You say:
"I have been able to find a formula which does that AND uses values only
when they are greater than zero and does not display #DIV/0!. "
so it might have been handy if you told us what that formula is, and how
that fails to meet your requirement of:
"Take an average of those specific cells AND use only the ones where the
cell is >0".

Are you really trying to exclude values <=0, or merely trying to exclude
blank cells [or text strings such as "" which your other formulae may be
returning] (which the AVERAGE function does anyway)?

 

[dbconn]

The formula that I have created that works uses a range of cells like G8:G18.
When i try the same formula for the random cells, it does not work. This
will work.

Thanks much
--
dbconn

I hope that you want (100+85+75)/3 rather than 100+85+75/3 ?

If G8=100, G16=85, G24 is blank, G36=75, the formula
=AVERAGE(G8, G16, G24, G36)
will give (100+85+75)/3, and you can avoid a #DIV/0! error by using
=IF(COUNT(G8, G16, G24, G36)=0,"",AVERAGE(G8, G16, G24, G36))

You say:
"I have been able to find a formula which does that AND uses values only
when they are greater than zero and does not display #DIV/0!. "
so it might have been handy if you told us what that formula is, and how
that fails to meet your requirement of:
"Take an average of those specific cells AND use only the ones where the
cell is >0".

Are you really trying to exclude values <=0, or merely trying to exclude
blank cells [or text strings such as "" which your other formulae may be
returning] (which the AVERAGE function does anyway)?
--
David Biddulph

I am looking for a formula that will compute an average of a number of non
contiguous cells such as G8, G16, G24, G36, etc. Each of these cells has
a
formula which computes an average of a range of cells. With the helpm of
this forum, I have been able to find a formula which does that AND uses
values only when they are greater than zero and does not display #DIV/0!.
But I cannot fin a fromula that will do the next step- Take an average of
those specific cells AND use only the ones where the cell is >0, Example
G8=100, G16=85, G24 is blank, G36=75, then this formula would calculate
the
average 100+85+75/3.

All help greatly appreciated

.

 

[dbconn]

Thanks so much
--
dbconn

For you example, try this formula:

=SUM(G8,G16,G24,G36)/SUM(G8>0,G16>0,G24>0,G36>0)

If your data is more regular, say every 8th row, ie G32 rather than
G36, you may try something like this:

=SUMPRODUCT((G8:G32)*(MOD(ROW(G8:G32),8)=0))/SUMPRODUCT((G8:G32>0)*(MOD(ROW(G8:G32),8)=0))

This is a longer formula if you only have a few data, but if you have
data in every 8th row from cell G8 to say cell G400, then this is
shorter.

Hope this helps / Lars-Ã…ke
.