Results 1 to 6 of 6

Thread: Exclude blank cells in weekday average formula

  1. #1

    Exclude blank cells in weekday average formula



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

    Hi,

    This is my first thread and im new to excel Im using below formula to result weekday average and its returning with average include blank cells. Im not sure how to add "exclude blank cells" formula in this. But i want it to count the cells contain 0 as value.
    =SUMPRODUCT((WEEKDAY($A$4:$A$26,2)<6)*B4:B26)/SUMPRODUCT((WEEKDAY($A$4:$A$26,2)<6)*1)
    Please help...

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    Hi, and welcome to the forum!

    Can you upload some sample data? I'm not quite sure I follow... (click Go Advanced to attach a workbook)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3

    Smile sample data attached

    I have attached the sample dat with description. hope it helps
    Attached Files Attached Files

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    Ah, okay.

    How's this for C34:

    =AVERAGEIF(C2:C32,"<>0")
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5

    I need weekday average

    Thank you. but you have given me average exclude blanks for entire month. thats not what i wanted Maybe i should say like this:
    using the same sample data, I would like to find "weekday average exclude blank cells" formula. is this much clear?

    For now im using =SUMPRODUCT((WEEKDAY($B$2:$B$32,2)<6)*C2:C32)/SUMPRODUCT((WEEKDAY($B$2:$B$32,2)<6)*1) and this one returning with blanks average

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    Try this and let me know if this works.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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