Howdy All,

I have a spreadsheet that contains a couple instances of a formula similar
to this one:

=IF(SUMPRODUCT(--($C$4:C4=C4))1,"",SUMPRODUCT(--(C4:$C$501=C4),H4:$H$501))

The formula works great, with one exception.

It leaves a 0 value in the cell below the last instance.

This interferes with an AVERAGE formula that I am running on a column.

Any ideas on how to either eliminate the 0 value, or modify my AVERAGE
formula to not count the 0 value?

Thanks,
Brian

Leave out the cell below "last instance" (what ever that means) when
computing the average?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Brian" wrote in message
.. .
Howdy All,

I have a spreadsheet that contains a couple instances of a formula similar
to this one:

=IF(SUMPRODUCT(--($C$4:C4=C4))1,"",SUMPRODUCT(--(C4:$C$501=C4),H4:$H$501))

The formula works great, with one exception.

It leaves a 0 value in the cell below the last instance.

This interferes with an AVERAGE formula that I am running on a column.

Any ideas on how to either eliminate the 0 value, or modify my AVERAGE
formula to not count the 0 value?

Thanks,
Brian

Sorry for the confusion.

But this spreadsheet has info continually added to it.
And in the row below the last entry, the value of that formula displays a 0.
And I average the entire column because the end row is undefined (always
increasing).



"Bernard Liengme" wrote in message
...
Leave out the cell below "last instance" (what ever that means) when
computing the average?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Brian" wrote in message
.. .
Howdy All,

I have a spreadsheet that contains a couple instances of a formula
similar to this one:

=IF(SUMPRODUCT(--($C$4:C4=C4))1,"",SUMPRODUCT(--(C4:$C$501=C4),H4:$H$501))

The formula works great, with one exception.

It leaves a 0 value in the cell below the last instance.

This interferes with an AVERAGE formula that I am running on a column.

Any ideas on how to either eliminate the 0 value, or modify my AVERAGE
formula to not count the 0 value?

Thanks,
Brian

Try something like this, array-entered (press CTRL+SHIFT+ENTER):
=AVERAGE(IF(H4:H5010,H4:H501))

We can't use entire col references.
If necessary, use something close, like this:
=AVERAGE(IF(H4:H655360,H4:H65536))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Brian" wrote in message
...
Sorry for the confusion.

But this spreadsheet has info continually added to it.
And in the row below the last entry, the value of that formula displays a
0.
And I average the entire column because the end row is undefined (always
increasing).

=IF( OR( C4 = "", COUNTIF( $C$4:C4, C4 ) 1 ), "", SUMIF( C4:$C$501, C4,
H4:$H$501 ) )

--
Regards,
Luc.

"Festina Lente"


"Brian" wrote:

Howdy All,

I have a spreadsheet that contains a couple instances of a formula similar
to this one:

=IF(SUMPRODUCT(--($C$4:C4=C4))1,"",SUMPRODUCT(--(C4:$C$501=C4),H4:$H$501))

The formula works great, with one exception.

It leaves a 0 value in the cell below the last instance.

This interferes with an AVERAGE formula that I am running on a column.

Any ideas on how to either eliminate the 0 value, or modify my AVERAGE
formula to not count the 0 value?

Thanks,
Brian

The formula

=IF(SUMPRODUCT(--($C$4:C4=C4))1,"",SUMPRODUCT(--(C4:$C$501=C4),H4:$H$501))

is too expensive. Invoke instead:

=SUMIF(C4:$C$501,C4,H4:$H$501)

If necessary:

=IF(COUNTIF($C$4:C4,C4)1,"",SUMIF(C4:$C$501,C4,H4 :$H$501))

The average formula can be modified to excluse 0's...

=AVERAGE(IF(Range0,Range)

confirmed with control+shift+enter.

Brian wrote:
Howdy All,

I have a spreadsheet that contains a couple instances of a formula similar
to this one:

=IF(SUMPRODUCT(--($C$4:C4=C4))1,"",SUMPRODUCT(--(C4:$C$501=C4),H4:$H$501))

The formula works great, with one exception.

It leaves a 0 value in the cell below the last instance.

This interferes with an AVERAGE formula that I am running on a column.

Any ideas on how to either eliminate the 0 value, or modify my AVERAGE
formula to not count the 0 value?

Thanks,
Brian