+ Reply to Thread
Results 1 to 2 of 2

Calculating a percentage of dates within a year

Hybrid View

  1. #1
    Registered User
    Join Date
    06-03-2014
    Posts
    5

    Calculating a percentage of dates within a year

    I am attempting to calculate a percentage of values that are within a year and counting blank values as zero all while ensuring if new rows are created, that cell is then included in the percentage.

    =SUMPRODUCT(--($E$5:$E$200>=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))*(($E$5:E$200)<=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))))/COUNTA($E$5:$E$200)

    but when I use that comes back as 100%. The only way it works is if I input a 0 in the cell in order to change it to 0-Jan-00.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Calculating a percentage of dates within a year

    Try this:
    Formula: copy to clipboard
    =SUMPRODUCT(--($E$5:$E$200>=EDATE(TODAY()-1,-12))*(($E$5:E$200)<=TODAY()))/COUNTA($A$5:$A$200)
    Please add reputation by clicking on the * if I have helped.
    Please mark the thread SOLVED if your issue has been resolved.
    Thanks, Glenn.

+ 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. Auto-calculating spend broken across year by dates
    By Scott_Hall in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-07-2013, 11:16 AM
  2. help calculating year to year commissioned payroll
    By csmith461 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-14-2013, 11:08 PM
  3. Replies: 3
    Last Post: 08-14-2012, 05:14 AM
  4. calculating year over year growth (with a twist)
    By jspinx in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-19-2010, 05:57 PM
  5. percentage of dates within last 1 year
    By dngilb in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-14-2006, 03:41 AM

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