Results 1 to 8 of 8

Thread: Exclude blanks and zeros in formula

  1. #1

    Exclamation Exclude blanks and zeros in formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    is the cell blank, or is there a space in it. Activate A4 and make sure there are no spaces....


  3. #3
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    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 11:35 PM. Reason: updated to check a1 a2 and a3 have numbers.

  4. #4
    "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

  5. #5
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    184
    Articles
    0
    Excel Version
    2010
    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

  6. #6
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    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.

  7. #7
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    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.

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    Maybe, ultimately, you can post a sample workbook showing the issue.... then we can better diagnose the issue.


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •