+ Reply to Thread
Results 1 to 3 of 3

Calculate variance without including zeros

  1. #1
    Registered User
    Join Date
    06-17-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011 MAc
    Posts
    1

    Calculate variance without including zeros

    I am trying to calculate the variance of a whole column but want to exclude the zeros and empty cells. Excel does not hace a var.s.if like there is an average.if function, so how do I write the formula? please help

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Calculate variance without including zeros

    You get better help if you add an excel file, without confidentional information.

    Please also add the expected result (in the sheet).
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Calculate variance without including zeros

    Try this array formula

    =VAR(IF(A:A<>0,A:A))

    Confirm with Ctrl+Shift+Enter and not just Enter

    Note, VAR like STDEV ignores blank cells while calculating
    Life's a spreadsheet, Excel!
    Say thanks, Click *

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1