+ Reply to Thread
Results 1 to 2 of 2

Average cells based on data in adjacent cells.

  1. #1
    Registered User
    Join Date
    06-25-2013
    Location
    Idaho, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Average cells based on data in adjacent cells.

    This one is killing me. I know I'm close, but I can't figure out where to go from here. I'm breaking data down into progressivly smaller fields (time of day, day of week, etc.) using =SUMPRODUCT and I need to add something to average the affected cells excluding zeros and blank cells to it. The formula I'm using now is "=SUMPRODUCT((Detail!$W$2:$W$4019>TIME(5,59,0))*($W$2:$W$4019<TIME(10,0,))*($Z$2:$Z$4019="Monday")*($Q$2:$Q$4019="Y"))". Now I need to average the times listed in row "L" for all of the rows that formula is sorting out.
    I hope that makes sense. I would attach a copy of my spreadsheet, but it's got some potentially sensative infromation in it that could get me fired.
    I appreciate any ideas or suggestions.

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Average cells based on data in adjacent cells.

    Hi,

    "Now I need to average the times listed in row "L" for all of the rows that formula is sorting out". Assume you mean column L? Also, you don't say what range of cells in that column, so I'll suppose that it's L2:L4019 similar to the others.

    Try this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER):

    =AVERAGE(IF((Detail!$W$2:$W$4019>TIME(5,59,0))*($W$2:$W$4019<TIME(10,0,))*($Z$2:$Z$4019="Monday")*($Q$2:$Q$4019="Y"),$L$2:$L$4019))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

+ 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