Need Help with Formula for Rolling Metrics

Vivien ChongVivien Chong ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions
08/05/19 Edited 12/09/19

I am working on a 6 months rolling metrics calculation for a point system.

A simple data involved will be as follows:

1) Date column

2) Points column (0-10)

3) Name column

 

I need to calculate a 6 months rolling sum of points, for each person.

I couldn't figure out how to calculate the sum of points for each person in 6 months rolling?

(When in the month of August, it will be a sum from March - August, when in the month of September, it will be a sum from April - September).

 

Anybody can advice on this?

Comments

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    edited 08/05/19

    EDITED to account for the rest of the criteria. Initial formula only looked at dates.

    .

    Try something along the lines of this...

    .

    =SUMIFS([Points Column]:[Points Column], [Date Column]:[Date Column], AND(@cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, @cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1))), [Name Column]:[Name Column], "John Smith")

    thinkspi.com

  • Vivien ChongVivien Chong ✭✭✭✭✭

    Thanks Paul.  Looking at your formula, I really couldn't understand it. :D. But I'll give it a try and try to understand how it works.  

    Appreciate your feedback.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! yes

     

    I was running short on time, otherwise I would have been more descriptive in the original post, but here it is (better late than never).

     

    =SUMIFS([Points Column]:[Points Column], [Date Column]:[Date Column], AND(@cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, @cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1))), [Name Column]:[Name Column], "John Smith")

    .

    So the easy part is knowing that we need a SUMIFS because we are wanting to sum a data set based off of multiple sets of criteria (date and name).

    We know that the syntax requires our sum range first, so we start with

     

    =SUMIFS([Points Column]:[Points Column],

    .

    Next we need to establish our first criteria range. In this formula, I started with the dates.

     

    =SUMIFS([Points Column]:[Points Column], [Date Column]:[Date Column],

    .

    Now we establish our date criteria (this is the tricky part, but I'll break it down step by step).

    We know we have two sets of criteria for the same range, so we use an AND statement.

     

    AND(logical statement 1, logical statement 2)

    .

    We know that the high end of our range will be TODAY(), but that would require opening the sheet daily to activate the TODAY() function just to capture any new entries (if any).

     

    So how can we just go ahead and capture the entire current month? Easy right?

     

    MONTH(TODAY())

     

    Except if you have data across multiple years, you're going to be including all of that. We have two options on how to account for this. Honestly... now that I am spelling it out, I realize I chose the harder of the two. Lol.

     

    But it maintains a consistent format for both sets of criteria so we'll stick with it.

    .

    Since the number of days in the current month changes with each month, we don't want to have to manually update the formula every single month and hope we didn't accidentally mess something up. So the easiest way to do it is to say we want to look at everything that is less than or equal to the day before the first of next month.

     

    This will account for 28, 29, 30, or 30 days without us having to regularly edit anything.

    .

    So the first of next month would look something like this...

     

    DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1)

    .

    That's all fine and dandy until we hit December. There is no 13th month. So what do we do? We use an IFERROR to establish that.

     

    IFERROR(the normal function to run, what to do in the event of an error)

    IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), what to do in the event of an error)

    .

    That first part is where we put our first DATE function. Simply drop it in, and that part is done. 

     

    So if the month is December and we want to go to January 1 of next year, we would say this...

     

    DATE(YEAR(TODAY()) + 1, 1, 1)

     

    This gives us Jan 1 of next year, so we will just drop that into the second portion of our IFERROR.

    .

    IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1))

    .

    Now we have the first of next month established. Subtracting 1 from that will give us the last day of the current month.

    IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1

    .

    Now we have our high range and can use an @cell reference to turn it into a logical statement then drop that into the first portion of our AND statement.

    @cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1

     

    AND(@cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, logical statement 2)

    .

    On to the low end of our range...

     

    We want to go 6 months in the past. Again... because of the differing number of days in each month, this exact number will vary, so the most consistent way to automate this is to just subtract 6 months and use the first day of whatever that month is.

     

    DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1)

    .

    But what if this month is March? There is no month of negative 3, so we use another IFERROR statement. But how do we go back a year and establish which month?

     

    Well let's break this step down just a little further...

     

    We want a result of -6 months.

    If we subtract 1 year (12 months) then add 6 months, that will do the trick.

     

    So we say

     

    DATE(YEAR(TODAY()) - 1, 

     

    to subtract the 12 months.

     

    DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1)

     

    to add six months and bring us back up to -6 months. Use 1 for the day, string it all together the same way we did the first IFERROR

     

    IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1))

     

    turn it into a logical statement

     

    @cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1))

     

    and drop that into the second portion of our AND statement.

     

    AND(@cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, logical statement 2)

     

    AND(@cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, @cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1)))

    .

    So now lets roll on back to our SUMIFS.

     

    We have established our sum range, our first criteria range and our first criteria set.

     

    =SUMIFS([Points Column]:[Points Column], [Date Column]:[Date Column], AND(@cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, @cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1))​​​​​​​)

    .

    All that is left now is adding in the name using the same syntax as above:

     

    criteria range, criteria

    [Name Column]:[Name Column], "John Smith"

    .

    Since that's our last set of criteria, we can throw it in and close out the SUMIFS...

     

    =SUMIFS([Points Column]:[Points Column], [Date Column]:[Date Column], AND(@cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, @cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1))), [Name Column]:[Name Column], "John Smith")

    .

    And there you have it. The breakdown on how to use an AND statement to combine multiple sets of criteria for the same range within a SUMIFS (also works in a COUNTIFS) as well as a step by step walk through of how to work with months using an IFERROR statement.

    .

    Does this breakdown work? I'd be more than happy to explain further if you'd like to ask any questions or anything.

    thinkspi.com

  • Vivien ChongVivien Chong ✭✭✭✭✭

    Hi Paul

    The formula works, and I was trying to figure out every part of it and was about to ask you if you don't mind to explain further.  But you shared on how you derived at the formula before I even asked.

    I will read through and try to understand what does each part of the formula means.

    Truly appreciate your assistance and help. 

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! yes

    thinkspi.com

  • Vivien ChongVivien Chong ✭✭✭✭✭

    Hi Paul

     

    I was working on this formula currently and would like to have the AVG instead of SUM for certain items.

     

    I thought I can replace the "SUM" with "AVG", but it doesn't work.

     

    Do you have any idea, how I can get the average instead of sum?

     

    Thanks Paul.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    We are actually using a SUMIFS. Note the S on the end. That is going to be the biggest factor as to how we go about finding the average.

     

    If it was just a simple SUM, we could replace it with AVG. If it was a SUMIF, we could replace it with an AVERAGEIF. But alas... There is no AVERAGEIFS (I've already requested this feature, but it wouldn't hurt if you also submitted a Product Enhancement Request for it as well). Instead we will need to use an AVG/COLLECT. Fortunately that ends up having the same syntax as a SUMIFS, so there is very little to change.

     

    Just replace SUMIFS with AVG(COLLECT( and throw an extra closing parenthesis on the end of the formula. This will give you the average of the same exact numbers you are currently summing.

    thinkspi.com

  • Vivien ChongVivien Chong ✭✭✭✭✭

    Thanks Paul.

    Alright I will also submit a request for the Product Enhancement.

    You are a great help!

    Have a blessed day!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! yes

    thinkspi.com

  • Would this solution work with the COUNTIFS() function in place of the SUMIFS()?

Sign In or Register to comment.