+ Reply to Thread
Results 1 to 4 of 4

average formula for non contiguous range and eliminate blank cells

  1. #1
    Registered User
    Join Date
    03-27-2013
    Location
    us
    MS-Off Ver
    Excel 2010
    Posts
    2

    average formula for non contiguous range and eliminate blank cells

    I am trying to take the average of certain cells in four different sheets. For example, each sheet represents a week and the summary sheet totals the four weeks. The sheets are identical. One sheet takes an average of each day of the week, however, my columns are not contiguous for the range I want to average. Therefore, I need the formula to average the week. Then I need the formula on the summary sheet to average all four weeks. I do not want to average blank cells. I have researched this issue on the web for a couple of hours and have tried various different formulas. I can figure out one sheet and one week but not how to average the four weeks. Any help???

  2. #2
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: average formula for non contiguous range and eliminate blank cells

    A sample workbook with mock data would be helpful, showing what you want the formula to return for the mock data.
    Please click the * icon below if I have helped.

  3. #3
    Registered User
    Join Date
    03-27-2013
    Location
    us
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: average formula for non contiguous range and eliminate blank cells

    Thank you! Attached is the workbook. You will see 4 sheets named "Week 1, week 2, etc". I need the total average for a guest for each week. My first issue is that some servers don't work each day so I need to take an average not including blank cells. Then on the tab PTD "Period to date",which is defined as a 4-week period, I need the guest average of all 4 week totals (again, not including blank cells). I was using just a simple average formula but realized this was including all cells and therefore penalizing the servers that were not scheduled to work. I hope this is helpful? Im dumbfounded by how difficult this became today haha

  4. #4
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: average formula for non contiguous range and eliminate blank cells

    I'm a little confused. If I make cell E4 in week #1 blank, the average stays at 5, both on that sheet and the PTD Sheet, and i believe this is what you want.
    If you change cell E4 to "0", then the average is affected, and becomes $4.29 for the week, and $4.82 for the PTD. The average formula does not include blank cells, so you should be fine.

    OKay... I figured it out. The issue arises when someone doesn't work an ENTIRE WEEK, because your formula in column AN returns a "0" if they don't work the week. You have to make that column return "" in the IFERROR part. That way it sees the cell as blank and ignores it in the average.

+ 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