Average function

[Rubix]

I would like to average column C7 to C37 but would like to exclude
cells with just zeros in them.

Ex. AVERAGE(C7:C37)

Each cell from C7 to C37 has a Paste Link to it from another worksheet
so zeros appear in all the cells that have not yet been entered.

I get an incorrect average because its dividing by 31 rather than
cells numbers otherr than zero that have been entered.

Thanks,

Rube

 

[Guest]

Try this:

=SUM(C7:C37)/COUNTIF(C7:C37,"<>0")

OR...this ARRAY FORMULA
=AVERAGE(IF(C7:C37<>0,C7:C37))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Rubix]

On Thu, 4 Jan 2007 18:45:00 -0800, Ron Coderre

Tried it. I got an Value error.

Try this:

=SUM(C7:C37)/COUNTIF(C7:C37,"<>0")

OR...this ARRAY FORMULA
=AVERAGE(IF(C7:C37<>0,C7:C37))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP

I would like to average column C7 to C37 but would like to exclude
cells with just zeros in them.

Ex. AVERAGE(C7:C37)

Each cell from C7 to C37 has a Paste Link to it from another worksheet
so zeros appear in all the cells that have not yet been entered.

I get an incorrect average because its dividing by 31 rather than
cells numbers otherr than zero that have been entered.

Thanks,

Rube

 

[Rubix]

I tried your SUM function formula and it worked. Sorry I should have
explained that I was getting a Value error with the Average function.
Not sure why if it works for you.

Thank you for your help.

Regarding:

Tried it. I got an Value error.

Sometimes less is more....but, in this case
less is definitely not enough.

I tested both formulas with the C7:C37 range containing
numerics (pos, neg, zero), text, and blanks without incident.
As long as the formulas are constructed properly
the problem lies in the contents of C7:C37.
Most likely there is an error value in the range.

Post back if you have more information or questions.

***********
Regards,
Ron

XL2002, WinXP

On Thu, 4 Jan 2007 18:45:00 -0800, Ron Coderre

Tried it. I got an Value error.

Try this:

=SUM(C7:C37)/COUNTIF(C7:C37,"<>0")

OR...this ARRAY FORMULA
=AVERAGE(IF(C7:C37<>0,C7:C37))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP


:

I would like to average column C7 to C37 but would like to exclude
cells with just zeros in them.

Ex. AVERAGE(C7:C37)

Each cell from C7 to C37 has a Paste Link to it from another worksheet
so zeros appear in all the cells that have not yet been entered.

I get an incorrect average because its dividing by 31 rather than
cells numbers otherr than zero that have been entered.

Thanks,

Rube