I'm using windows XP (older version) which does not recognize the averageif
formula. I need a formula that calcs the average of four different data
points some of which contain zeros. Example:

A B C D avg
1 5 0 10 15 10

Use this array* formula:

=AVERAGE(IF(A1:D1<0,A1:D1))

*An array formula has to be committed using the key combination of
Ctrl-Shift-Enter (CSE) instead of the usual Enter. If you do this
correctly then Excel will wrap curly braces { } around the formula
when viewed in the formula bar - do not type these yourself. If you
amend the formula you will need to use CSE again.

Hope this helps.

Pete

On Aug 21, 12:43*pm, Help wrote:
I'm using windows XP (older version) which does not recognize the averageif *
formula. *I need a formula that calcs the average of four different data
points some of which contain zeros. Example:

* * *A * *B * C * D * *avg
1 * 5 * *0 * 10 *15 * 10

Use SUMIF/COUNTIF
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Help" wrote in message
...
I'm using windows XP (older version) which does not recognize the
averageif
formula. I need a formula that calcs the average of four different data
points some of which contain zeros. Example:

A B C D avg
1 5 0 10 15 10

Or sumproduct/countif

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Help" wrote in message
...
I'm using windows XP (older version) which does not recognize the
averageif
formula. I need a formula that calcs the average of four different data
points some of which contain zeros. Example:

A B C D avg
1 5 0 10 15 10

Hi,

=SUM(A1:D1)/COUNTIF(A1:D1,"0")

if this helps please click yes, thanks

"Help" wrote:

I'm using windows XP (older version) which does not recognize the averageif
formula. I need a formula that calcs the average of four different data
points some of which contain zeros. Example:

A B C D avg
1 5 0 10 15 10

The formula still gives me an error?
=(Percentages!D24+Percentages!J24+Percentages!P24)/countif(Percentages!D24,Percentages!J24,Percentage s!P24,"0")

"Eduardo" wrote:

Hi,

=SUM(A1:D1)/COUNTIF(A1:D1,"0")

if this helps please click yes, thanks

"Help" wrote:

I'm using windows XP (older version) which does not recognize the averageif
formula. I need a formula that calcs the average of four different data
points some of which contain zeros. Example:

A B C D avg
1 5 0 10 15 10

Countif requires a contiguous range.

=(Percentages!D24+Percentages!J24+Percentages!P24)/SUM(Percentages!D240,Percentages!J240,Percentage s!P240)

Or, if there are only strings or blanks between the cells with the numbers:

=SUM(Percentages!D24:P24)/COUNTIF(Percentages!D24:P24,"0")

HTH,
Bernie
MS Excel MVP


"Help" wrote in message
...
The formula still gives me an error?
=(Percentages!D24+Percentages!J24+Percentages!P24)/countif(Percentages!D24,Percentages!J24,Percentage s!P24,"0")

"Eduardo" wrote:

Hi,

=SUM(A1:D1)/COUNTIF(A1:D1,"0")

if this helps please click yes, thanks

"Help" wrote:

I'm using windows XP (older version) which does not recognize the averageif
formula. I need a formula that calcs the average of four different data
points some of which contain zeros. Example:

A B C D avg
1 5 0 10 15 10