+ Reply to Thread
Results 1 to 5 of 5

Need to cal. avg of Col N (excl. blank cells) with only cells ref to Col. C as "Y"

  1. #1
    Forum Contributor
    Join Date
    05-01-2008
    Posts
    165

    Smile Need to cal. avg of Col N (excl. blank cells) with only cells ref to Col. C as "Y"

    Hi,

    =SUMIF($C$5:$C$39,"Y",N$5:N$39)/(COUNTIF($C$5:$C$39,"Y")-COUNTBLANK(N$5:N$39))

    In Col C, there are dropdowns for choosing Y or N. In Col N I only want to see the average of the numbers which say "Y" in Col. C. But the formula I have here counts all blank cells in Col. N including those don't say "Y" in Col. C.

    Please help.

  2. #2
    Forum Contributor
    Join Date
    10-14-2004
    Location
    San Diego, CA
    Posts
    213
    Hello Vinnie Chan:

    This is an array formula; enter with the keys, Ctrl+Shift+Enter.
    =AVERAGE(IF($C$5:$C$39="Y",$N$5:$N$39))

    Or

    =SUMPRODUCT(--($C$5:$C$39="Y"),($N$5:$N$39))/SUMPRODUCT(--($C$5:$C$39="Y"))
    Last edited by Flintstone; 05-01-2008 at 05:43 PM.

  3. #3
    Forum Contributor
    Join Date
    05-01-2008
    Posts
    165
    Thanks Flintstone for you reply.

    However these 2 formulas give the same result as the formula I have before. The answer is understated because it includes the blank cell in Column N which doesn't have a "Y" chosen in Column C.

    I am now attaching a file. May be you will understand better what I really looking for. The formula I need in the row highlight in RED. The answer in
    N43 should be 7.5.

    Thank you for your help in advance.
    Attached Files Attached Files

  4. #4
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    This should do it
    =SUMPRODUCT(--(C5:C39="Y"),(N5:N39))/COUNTIF(C5:C39,"Y")
    But it doesn't, it returns 4.5

    your post said that you want the average of those entries that have a matching Y in column C, but your desired average is the average of the non-zero (column N) entries that have a Y in C.

    If that is what you want
    =SUMPRODUCT(--(C5:C39="Y"),(N5:N39))/SUMPRODUCT(--(C5:C39="Y"),--(N5:N39<>0))
    Last edited by mikerickson; 05-01-2008 at 10:09 PM.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  5. #5
    Forum Contributor
    Join Date
    05-01-2008
    Posts
    165
    Thank you mikerickson.

    This is exactly what I want. Works perfectly. I have never used SUMPRODUCT, I need to understand more about formulas.

    You guys are awesome.

    Thanks again.

+ 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