+ Reply to Thread
Results 1 to 3 of 3

Averageifs Returns #DIV/0! when Referencing Formula as Time

  1. #1
    Registered User
    Join Date
    08-29-2017
    Location
    Oregon
    MS-Off Ver
    2016
    Posts
    26

    Averageifs Returns #DIV/0! when Referencing Formula as Time

    Hello!

    I have slammed my head on this one for a while now! This formula should work, but there is clearly a detail I am missing, as it is pretty straight forward.

    The attached sheet is edited (removed sensitive data), but has the essentials. Passdown tab contains the formula with the error, with the other tabs feeding data across the sheet.

    Referencing the Report tab, Passdown tab is trying to get an average of Turnaround time for samples a technician (from Personnel tab) has processed, based on the request priority level, and number of samples processed (see attachment). I am using:
    =AVERAGEIFS(Report!$H:$H,Report!$C:$C,Personnel!$A2:$A30,Report!$D:$D,$C6,Report!$G:$G,D$5,Report!$H:$H,"")

    It should return the time based on previous experience using the code with other reports, but it is returning #Div/0! instead. I need it to return the average time based on the parameters referenced.

    Anyone notice something that I missed?
    Attached Files Attached Files
    Last edited by sansai; 09-11-2019 at 01:06 AM. Reason: Corrected Attachment

  2. #2
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,912

    Re: Averageifs Returns #DIV/0! when Referencing Formula as Time

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  3. #3
    Registered User
    Join Date
    08-29-2017
    Location
    Oregon
    MS-Off Ver
    2016
    Posts
    26

    Re: Averageifs Returns #DIV/0! when Referencing Formula as Time

    Quote Originally Posted by avk View Post
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    While this is not the answer, I figured out what I had done wrong. I missed a detail in Criteria3. I wrote 'Report!$H:$H,""', and it should read 'Report!$H:$H,"<>"'

    False alarm, and sorry for wasting any time! I will clear out the other #DIV/0 by using IFERROR now.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 8
    Last Post: 08-18-2018, 11:51 PM
  2. SUMIFS - returns a 0 Value when referencing a cell with TIME
    By Jny4man in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-02-2014, 05:42 PM
  3. Replies: 3
    Last Post: 06-07-2013, 02:28 PM
  4. Averageifs returns #DIV/0! - replace with 0?
    By CP1278 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-14-2012, 10:16 AM
  5. Replies: 4
    Last Post: 10-21-2011, 09:22 AM
  6. Replies: 4
    Last Post: 07-29-2010, 07:46 AM
  7. Replies: 1
    Last Post: 03-09-2006, 04:00 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1