+ 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

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



    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
    San Diego, CA
    Hello Vinnie Chan:

    This is an array formula; enter with the keys, Ctrl+Shift+Enter.


    Last edited by Flintstone; 05-01-2008 at 05:43 PM.

  3. #3
    Forum Contributor
    Join Date
    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 Guru mikerickson's Avatar
    Join Date
    Davis CA
    MS-Off Ver
    Excel 2011
    This should do it
    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
    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
    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)


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