is the cell blank, or is there a space in it. Activate A4 and make sure there are no spaces....
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?
is the cell blank, or is there a space in it. Activate A4 and make sure there are no spaces....
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"))
Last edited by Simi; 2013-04-24 at 10:35 PM. Reason: updated to check a1 a2 and a3 have numbers.
"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
Maybe try:
=(SUMPRODUCT(POWER(IF(A1:A4<>"",A1:A4,0),COUNT(A1:A4)))/COUNT(A1:A4))^(1/COUNT(A1:A4))
Circumference of a circle = 2πr²
²the circle's radius
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.
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.
Maybe, ultimately, you can post a sample workbook showing the issue.... then we can better diagnose the issue.
Bookmarks