Exclude blanks and zeros in formula

[curly]

I have following data
A1: 2
A2: 2
A3: 3
A4: blank/zero

Calculation is following:

(SUM(A1^COUNT(A1:A4);A2^COUNT(A1:A4);A3^COUNT(A1:A4); A4^COUNT(A1:A4))/COUNT(A1:A4))^(1/COUNT(A1:A4))

Formula returns #VALUE! because of 0 in cell A4.

Could anyone help me on fixing this?

[NBVC]

is the cell blank, or is there a space in it. Activate A4 and make sure there are no spaces....

[Simi]

another thing to check is, A4 contains a formula that results in a blank or a zero, there is still something to count in the cell.
on a side note it calculates for me if I put a 0 in the cell, but it gives the #value error for a blank, if the blank is from say A4 = B4 and B4 is blank.
If there is no data or formulas in A4 this would work. You can try using a countif.
replace any , with ; if you need to.
=SUM(IF(ISNUMBER(A1),A1^COUNTIF(A1:A4,">0"),0),IF(ISNUMBER(A2),A2^COUNTIF(A1:A4,">0"),0),IF(ISNUMBER(A3),A3^COUNTIF(A1:A4,">0"),0), IF(ISNUMBER(A4),A4^COUNTIF(A1:A4,">0"),0))/COUNTIF(A1:A4,">0"))^(1/COUNTIF(A1:A4,">0"))

[curly]

"another thing to check is, A4 contains a formula that results in a blank or a zero"

Actually yes - cell A4 contains IF formula which results in blank. Tried you offered formula, but came to wierd figure

[JoePublic]

Maybe try:
=(SUMPRODUCT(POWER(IF(A1:A4<>"",A1:A4,0),COUNT(A1:A4)))/COUNT(A1:A4))^(1/COUNT(A1:A4))

[Simi]

i'm not sure what a wierd figure is. if you did not get the expected result, try replacing the ">0" with ">=0" and see if those are the values your looking for.

[Simi]

I guess with the values you provided what is the answer you are looking for,

I have come up with 4 variations.
if the data is
a1: 2
a2: 2
a3: 3
a4: 0

I get any of the following, 29.81453 or 79.90307 or 2.429121 or 2.305444
if a4: is blank
I get, 29.81453 with 2 formulas, or 2.429121 with 2 formulas.

It all stems from where the ( ) are in the formula and if we evaluate for 0 being a number to use in the count or not.
=SUM(IF(ISNUMBER(A1),A1^COUNTIF(A1:A4,">0"),0),IF(ISNUMBER(A2),A2^COUNTIF(A1:A4,">0"),0),IF(ISNUMBER(A3),A3^COUNTIF(A1:A4,">0"),0), IF(ISNUMBER(A4),A4^COUNTIF(A1:A4,">0"),0))/COUNTIF(A1:A4,">0")^(1/COUNTIF(A1:A4,">0"))

=SUM(IF(ISNUMBER(A1),A1^COUNTIF(A1:A4,">=0"),0),IF(ISNUMBER(A2),A2^COUNTIF(A1:A4,">=0"),0),IF(ISNUMBER(A3),A3^COUNTIF(A1:A4,">=0"),0), IF(ISNUMBER(A4),A4^COUNTIF(A1:A4,">=0"),0))/COUNTIF(A1:A4,">=0")^(1/COUNTIF(A1:A4,">=0"))

=(SUM(IF(ISNUMBER(A1),A1^COUNTIF(A1:A4,">0"),0),IF(ISNUMBER(A2),A2^COUNTIF(A1:A4,">0"),0),IF(ISNUMBER(A3),A3^COUNTIF(A1:A4,">0"),0), IF(ISNUMBER(A4),A4^COUNTIF(A1:A4,">0"),0))/COUNTIF(A1:A4,">0"))^(1/COUNTIF(A1:A4,">0"))

=(SUM(IF(ISNUMBER(A1),A1^COUNTIF(A1:A4,">=0"),0),IF(ISNUMBER(A2),A2^COUNTIF(A1:A4,">=0"),0),IF(ISNUMBER(A3),A3^COUNTIF(A1:A4,">=0"),0), IF(ISNUMBER(A4),A4^COUNTIF(A1:A4,">=0"),0))/COUNTIF(A1:A4,">=0"))^(1/COUNTIF(A1:A4,">=0"))

The thing to remember from this is: you need to specify what to look for with a count, if the range you are counting contains formulas.

[NBVC]

Maybe, ultimately, you can post a sample workbook showing the issue.... then we can better diagnose the issue.