+ Reply to Thread
Results 1 to 8 of 8

Need help getting average over days

  1. 07-31-2018, 04:12 PM #1
    airhoodz
    airhoodz is offline
    Registered User
    Join Date
    10-09-2014
    Location
    Syracuse
    MS-Off Ver
    Office 2013
    Posts
    43

    Need help getting average over days

    My data is pretty simple, there's a date column with 8 dates. 7/29 occurs once, 7/30 occurs once, and 7/31 occurs 7 times. Then I have a column that says "Closed?" and it's values are either YES/NO.

    The 7/29, 7/30, and 5 of the 7/31's are YES.

    I have one column called "Month" that I use as a filter. It's a field that says "=MONTH([@[Date worked]])

    I have a pivot table that's doing a count, but I want the average of "YES" per whatever filter I have picked. Realistically, it's to tell me the average "YES" per day at the end of the month, or through whatever time period I want filtered by Month.

    My book is attached.
    Attached Files Attached Files
    Last edited by airhoodz; 07-31-2018 at 04:32 PM.
    Reply With QuoteRegister To Reply
  2. 07-31-2018, 04:14 PM #2
    airhoodz
    airhoodz is offline
    Registered User
    Join Date
    10-09-2014
    Location
    Syracuse
    MS-Off Ver
    Office 2013
    Posts
    43

    Re: Need help getting average over days

    I am also filtering by YES too.
    Reply With QuoteRegister To Reply
  3. 07-31-2018, 04:20 PM #3
    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,929

    Re: Need help getting average over days

    Your "month" column is returning a REF error, meaning it cannot find the reference
    What exactly are you finding an average of? You have dates and text?
    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
  4. 07-31-2018, 04:23 PM #4
    airhoodz
    airhoodz is offline
    Registered User
    Join Date
    10-09-2014
    Location
    Syracuse
    MS-Off Ver
    Office 2013
    Posts
    43

    Re: Need help getting average over days

    Quote Originally Posted by FDibbins View Post
    Your "month" column is returning a REF error, meaning it cannot find the reference
    What exactly are you finding an average of? You have dates and text?
    I guess that's where I'm stuck. I get why it's doing that, but how do I get it to do what I want? I'd like the COUNT(YES)/SUM(DATES SHOWN) in the range, and I thought about a calculated field, but then I'd have to change the range.
    Reply With QuoteRegister To Reply
  5. 07-31-2018, 04:24 PM #5
    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,929

    Re: Need help getting average over days

    OK I figured out what the REF error was. You have SHEET1 but are referencing BOOK1 - probably when you put your dummy file together
    Reply With QuoteRegister To Reply
  6. 07-31-2018, 04:30 PM #6
    airhoodz
    airhoodz is offline
    Registered User
    Join Date
    10-09-2014
    Location
    Syracuse
    MS-Off Ver
    Office 2013
    Posts
    43

    Re: Need help getting average over days

    Quote Originally Posted by FDibbins View Post
    OK I figured out what the REF error was. You have SHEET1 but are referencing BOOK1 - probably when you put your dummy file together
    Yeah, wow whoops. I totally misread your last post too and through you were bringing up the DIV/0 and not REF. Didn't even notice. That should be = to the column to the left of it to trim.
    Reply With QuoteRegister To Reply
  7. 07-31-2018, 04:32 PM #7
    airhoodz
    airhoodz is offline
    Registered User
    Join Date
    10-09-2014
    Location
    Syracuse
    MS-Off Ver
    Office 2013
    Posts
    43

    Re: Need help getting average over days

    Quote Originally Posted by FDibbins View Post
    OK I figured out what the REF error was. You have SHEET1 but are referencing BOOK1 - probably when you put your dummy file together
    Fixed the ref error in the original file
    Reply With QuoteRegister To Reply
  8. 07-31-2018, 04:39 PM #8
    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,929

    Re: Need help getting average over days

    OK, so what are you trying to average, and what would some sample answers be?
    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. [SOLVED] Problem with average formula that finds average over last 343 days excluding previous 7
    By Renegade115 in forum Excel General
    Replies: 2
    Last Post: 04-17-2018, 09:45 PM
  2. [SOLVED] Average and Standard over the last 344 days excluding the previous 7 days
    By Renegade115 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-01-2018, 09:16 PM
  3. [SOLVED] Average for the last 8 days.
    By sovietchild in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-20-2017, 05:51 PM
  4. Formula/function for rolling average of last seven days and 28 days previous to that
    By jacksonrh4 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-22-2016, 09:18 AM
  5. Creative way to count days worked or average days worked...
    By bcrockett101 in forum Excel General
    Replies: 1
    Last Post: 08-09-2016, 12:16 PM
  6. Average Score for last 30 days, 60 days, etc.
    By kublaieod in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-02-2013, 05:00 AM
  7. Help on count the number of days in between dates and then average number of days
    By Barbara Excel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-30-2013, 12:13 PM

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