+ Reply to Thread
Results 1 to 3 of 3

Finding a Conditional Average on Multiple Sheets

  1. 02-28-2017, 02:00 PM #1
    ljbrown
    ljbrown is offline
    Registered User
    Join Date
    02-20-2017
    Location
    Maine
    MS-Off Ver
    excel 10
    Posts
    22

    Finding a Conditional Average on Multiple Sheets

    First, I apologize if this has been posted before. I have searched the forum and google for many hours before resorting to post on this forum. If it has been posted before, please point me in the right direction. I did find some posts that were similar, but I was unable to apply them as I am a novice.

    I work for a small hospital and I have been recently tasked with calculating statistics with the admission process. One of the statistics that needs to be calculated is what each nurse’s average admission time is between a range of dates (i.e. 1 JAN 2017 to 7 JAN 2017). The data is on two separate worksheet tabs – A3 & D4 (Note: Every worksheet has the same data in the same location). Some nurses work on both floors so it is necessary to include both floors. I can find my answer for each sheet individually per nurse, but not when combined as I receive an error.

    Any and all assistance is greatly appreciated,

    Lance
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-28-2017, 02:26 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,933

    Re: Finding a Conditional Average on Multiple Sheets

    I came up with this monster, it works, but hopefully someone else will have a more elegant way to do this...
    B2=(SUMIFS('D4'!C:C,'D4'!A:A,">="&RNs!$D$3,'D4'!A:A,"<="&RNs!$E$3,'D4'!B:B,RNs!A2)+SUMIFS('A3'!C:C,'A3'!A:A,">="&RNs!$D$3,'A3'!A:A,"<="&RNs!$E$3,'A3'!B:B,RNs!A2))/(COUNTIFS('D4'!A:A,">="&RNs!$D$3,'D4'!A:A,"<="&RNs!$E$3,'D4'!B:B,RNs!A2)+COUNTIFS('A3'!A:A,">="&RNs!$D$3,'A3'!A:A,"<="&RNs!$E$3,'A3'!B:B,RNs!A2))
    copied down
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 02-28-2017, 04:46 PM #3
    ljbrown
    ljbrown is offline
    Registered User
    Join Date
    02-20-2017
    Location
    Maine
    MS-Off Ver
    excel 10
    Posts
    22

    Re: Finding a Conditional Average on Multiple Sheets

    Hi Ford,

    It seemed to work on some rows but not all. Boone, Revere and Dyer all worked (I manually checked them) but all the rest had #DIV/0! errors and I am unsure why. My apologies for my ignorance. I ran a trace but all seems correct.

    Thank you for your help with this.

    My best,

    Lance
    Last edited by ljbrown; 02-28-2017 at 04:59 PM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Average of last five values within multiple condition or finding the first value
    By hassjohn in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-25-2015, 05:36 AM
  2. Finding Average Values over Multiple Intervals
    By elektrotechnik in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-17-2014, 01:47 PM
  3. Finding an Average of multiple ratings
    By ian.bowles in forum Excel General
    Replies: 3
    Last Post: 07-30-2014, 11:31 AM
  4. Excel 2007 : Conditional average across multiple sheets
    By PookeyTim in forum Excel General
    Replies: 5
    Last Post: 07-25-2010, 08:40 PM
  5. Conditional average over multiple sheets?
    By remps in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 05-14-2008, 07:05 PM
  6. Finding an average across multiple worksheets in the same workbook
    By GYG in forum Excel General
    Replies: 8
    Last Post: 03-20-2007, 02:00 PM
  7. Finding the average across multiple sheets
    By quailhunter in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 08-14-2006, 07:13 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1