+ Reply to Thread
Results 1 to 6 of 6

Averageif again formula is counting blank cells

  1. #1
    Registered User
    Join Date
    07-06-2010
    Location
    Beckenham
    MS-Off Ver
    Excel 2007
    Posts
    84

    Red face Averageif again formula is counting blank cells

    I have another averageif scenario

    the formula I have is as follows;

    =AVERAGE(IF(((Properties!S3:S1002="Y")+(Properties!S3:S1002="A")),Properties!BY3:BY1002))

    Column S may have blank cells but the corresponding cell (same row) in column BY may be populated. I can't get it to not take into account any cell in column BY that is populated but blank in column S.

    Any help would be much appreciated! (PS will read carefully and follow instruction properly this time).
    Last edited by AllenMead; 09-22-2010 at 02:01 AM.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Averageif again formula is counting blank cells

    Hello Allen,

    Apart from an unnecessary set of parentheses (which won't affect the result) that formula looks OK to me.

    It's an "array formula" though which means that you need to confirm with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar.....if you don't do that results may be unpredictable....
    Audere est facere

  3. #3
    Registered User
    Join Date
    07-06-2010
    Location
    Beckenham
    MS-Off Ver
    Excel 2007
    Posts
    84

    Re: Averageif again formula is counting blank cells

    Hi Daddylonglegs,

    that's exactly what I though. I know what the answer should be which is £6,642.43. If I use the formula in post #1, I get £6,709.21 which includes £20k in the average range in column BY which does not have a corresponding Y or A in column S.

    If I make it an array, I get the following £4,305.68 hmmm.

    I have attached a copy of the sheet which I am working on as my testbed. The formula is on sheet "Kitchen Completions" cell ref: P22.

    If you could have a look, that would be great.

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Averageif again formula is counting blank cells

    OK, I see the problem, it's actually the other way round, you have some rows where the column S value is "Y" but the BY value is blank, e.g. rows 6, 7 and 8. In some circumstances (including here) the blanks are interpreted as zeros so they are throwing off the average.

    You can amend the array formula so that it only averages non-blank rows in column BY, i.e.

    =AVERAGE(IF((Properties!S3:S1002="Y")+(Properties!S3:S1002="A"),IF(Properties!BY3:BY1002<>"", Properties!BY3:BY1002)))

    confirmed with CTRL+SHIFT+ENTER

    Note: if you have any zero values in that column (where the column S criteria is also met) then they will be included in the average, if you don't want that then change <>"" to <>0 and that will exclude blanks and zeroes.

    You could also use a "non-array" formula (in Excel 2007 and later) by using a combination of SUMIF and COUNTIFS, i.e.

    =SUM(SUMIF(Properties!S3:S1002,{"Y","A"},Properties!BY3:BY1002))/SUM(COUNTIFS(Properties!S3:S1002,{"Y","A"},Properties!BY3:BY1002,"<>"))

  5. #5
    Registered User
    Join Date
    07-06-2010
    Location
    Beckenham
    MS-Off Ver
    Excel 2007
    Posts
    84

    Re: Averageif again formula is counting blank cells

    Hi Daddylonglegs,

    Genius, pure genius! Absolutely spot on!!!!!!!!! I don't need to worry about zeros in column BY as data is entered here manually once we know what the initial value is, only columns BZ and CA that I have to worry about zeros.

    Thank you for your help, very much appreciated!

  6. #6
    Registered User
    Join Date
    07-06-2010
    Location
    Beckenham
    MS-Off Ver
    Excel 2007
    Posts
    84

    Re: Averageif again formula is counting blank cells

    I thought that I was going to be clever with this and just apply the formula you kindly provided to the following columns BZ and CA

    But nope, it doesn't seem to be that straightforward. Column BZ does need to included blanks and zeros giving a result of £11.72.

    Theoretically, column CA sould be the same as BY with any values from BZ added but i must be doing something wrong as I get £4,313.27 instead of £6,654.14.

    Sorted Column CA just changed "<>" to ">0" still a little stuck on column BZ
    Last edited by AllenMead; 09-22-2010 at 02:48 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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