I am trying to average a range of numbers (F35:F38,F42:F45,F48:F54) that have
numeric zeros in some of the cells. However, I would like to exclude them,
and the cells from the calculation.

Why does this array not work?

=AVERAGE(IF((F35:F38,F42:F45,F48:F54)<0,(F35:F38, F42:F45,F48:F54)))

Try...

=AVERAGE(IF((1-ISNUMBER(MATCH(ROW(F35:F54),{39,40,41,46,47},0)))* (F35:F54
0),F35:F54))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
"Coal Miner" <Coal wrote:

I am trying to average a range of numbers (F35:F38,F42:F45,F48:F54) that have
numeric zeros in some of the cells. However, I would like to exclude them,
and the cells from the calculation.

Why does this array not work?

=AVERAGE(IF((F35:F38,F42:F45,F48:F54)<0,(F35:F38, F42:F45,F48:F54)))

OK! That worked great on some of the data rows but for some reason some of
the data rows are returning the #DIV/0! error. What the heck?!?!

"Domenic" wrote:

Try...

=AVERAGE(IF((1-ISNUMBER(MATCH(ROW(F35:F54),{39,40,41,46,47},0)))* (F35:F54
0),F35:F54))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
"Coal Miner" <Coal wrote:

I am trying to average a range of numbers (F35:F38,F42:F45,F48:F54) that have
numeric zeros in some of the cells. However, I would like to exclude them,
and the cells from the calculation.

Why does this array not work?

=AVERAGE(IF((F35:F38,F42:F45,F48:F54)<0,(F35:F38, F42:F45,F48:F54)))

You'll get that error if none of the target cells contain a value
greater than zero. In this case, the following formula will return a
blank...

=IF(INDEX(FREQUENCY((F35:F38,F42:F45,F48:F54),0),2 ),AVERAGE(IF((1-ISNUMBE
R(MATCH(ROW(F35:F54),{39,40,41,46,47},0)))*(F35:F5 40),F35:F54)),"")

Hope this helps!

In article ,
"Coal Miner" wrote:

OK! That worked great on some of the data rows but for some reason some of
the data rows are returning the #DIV/0! error. What the heck?!?!

"Domenic" wrote:

Try...

=AVERAGE(IF((1-ISNUMBER(MATCH(ROW(F35:F54),{39,40,41,46,47},0)))* (F35:F54
0),F35:F54))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
"Coal Miner" <Coal wrote:

I am trying to average a range of numbers (F35:F38,F42:F45,F48:F54) that
have
numeric zeros in some of the cells. However, I would like to exclude
them,
and the cells from the calculation.

Why does this array not work?

=AVERAGE(IF((F35:F38,F42:F45,F48:F54)<0,(F35:F38, F42:F45,F48:F54)))

Domenic,

Thanks alot. That did fix the problem when none of the target cells
contained a value greater than zero. However, I am still getting the #DIV/0!
error in some cells. I checked to make sure formatting was correct. I
removed the "1-" portion of the formula (located immediately prior to
ISNUMBER) and this allows the formula to work in the cells that were
returning the #DIV/0!. But, that formula will now return the #DIV/0! error
in the other cells that were working correctly. What is the purpose of the
"1-" text within the formula. Maybe if I understand that I could repair
myself.

Please help as this is frustrating.

"Domenic" wrote:

You'll get that error if none of the target cells contain a value
greater than zero. In this case, the following formula will return a
blank...

=IF(INDEX(FREQUENCY((F35:F38,F42:F45,F48:F54),0),2 ),AVERAGE(IF((1-ISNUMBE
R(MATCH(ROW(F35:F54),{39,40,41,46,47},0)))*(F35:F5 40),F35:F54)),"")

Hope this helps!

In article ,
"Coal Miner" wrote:

OK! That worked great on some of the data rows but for some reason some of
the data rows are returning the #DIV/0! error. What the heck?!?!

"Domenic" wrote:

Try...

=AVERAGE(IF((1-ISNUMBER(MATCH(ROW(F35:F54),{39,40,41,46,47},0)))* (F35:F54
0),F35:F54))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
"Coal Miner" <Coal wrote:

I am trying to average a range of numbers (F35:F38,F42:F45,F48:F54) that
have
numeric zeros in some of the cells. However, I would like to exclude
them,
and the cells from the calculation.

Why does this array not work?

=AVERAGE(IF((F35:F38,F42:F45,F48:F54)<0,(F35:F38, F42:F45,F48:F54)))

Try the following formula instead...

=IF(INDEX(FREQUENCY((F35:F38,F42:F45,F48:F54),0),2 ),AVERAGE(IF((1-ISNUMBE
R(MATCH(ROW(F35:F54)-ROW(F35)+1,{5,6,7,12,13},0)))*(F35:F540),F35:F54) ),
"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Does this help?

In article ,
"Coal Miner" wrote:

Domenic,

Thanks alot. That did fix the problem when none of the target cells
contained a value greater than zero. However, I am still getting the #DIV/0!
error in some cells. I checked to make sure formatting was correct. I
removed the "1-" portion of the formula (located immediately prior to
ISNUMBER) and this allows the formula to work in the cells that were
returning the #DIV/0!. But, that formula will now return the #DIV/0! error
in the other cells that were working correctly. What is the purpose of the
"1-" text within the formula. Maybe if I understand that I could repair
myself.

Please help as this is frustrating.