AVERAGEIFS

Becky Parker

I've looked through the community and think AVG(COLLECT) is the answer to needing two variables to match before averaging. I'm also referencing another sheet, which isn't an obstacle for me any more.

I have a formula that works but need to add a 2nd variable. What I want to do is create a "calculator" that an office leader can use to calculate the profitability of a producer's book of business. The office leader needs to enter as little data as possible, meaning, just 1 person's name. This will be used by multiple office leaders so output will not be saved. The formula I have below averages a percentage (the second part of this formula) when both the producer name match what is entered in a cell on my calculator sheet (the first part of this formula) AND when the effective date of a reference sheet is within the last 12 months / rolling 12 months (not part of this formula). I have a today cell on my calculator sheet so the TBD formula can lookback 12 months from today. How can I change the formula to include the rolling 12 month variable?

=AVERAGEIF({Pricing Resource Tools Tracking Producer Name}, =[Column2]1, {Pricing Resource Tools Tracking Variance to Target})

Paul Newcome

Are you looking to pull in everything that is 367 days in the past, or are you looking to pull anything that is between today and 367 days in the past? The way your formula is currently written, it will only pull rows where the date in the range is equal to today minus 367 days. If you want to include everything in between then you will need to make this adjustment:

=AVG(COLLECT({Pricing Resource Tools Tracking Variance to Target}, {Pricing Resource Tools Tracking Producer Name}, =[Column2]1, {Pricing Resource Tools Tracking Ren/Eff Date}, @cell>= [Date - do not edit]1 - 367))

Paul Newcome

@Becky Parker The syntax for AVG/COLLECT is...

=AVG(COLLECT({Range To Average}, {1st Criteria Range}, 1st Criteria, {2nd Criteria Range}, 2nd Criteria))

Becky Parker

Thank you @Paul Newcome. This is very helpful. I followed the syntax and does not work - because there is something wrong with my rolling 12 variable. I tested the syntax using a different 2nd criteria range & 2nd criteria and it worked great. Now, I'm hopeful you can help me, in as plain English as possible, with my rolling 12 variable.

This is the what I have in row 4, column 2 of my "calculator" sheet:

=AVG(COLLECT({Pricing Resource Tools Tracking Variance to Target}, {Pricing Resource Tools Tracking Producer Name}, =[Column2]1, {Pricing Resource Tools Tracking Ren/Eff Date}, [Date - do not edit]1 - 367)).

My reference sheet is called Pricing Resource Tools Tracking. My "calculator" sheet looks like this:

The Date - do not edit column is set to today =TODAY() I've checked my date columns to ensure they are dates, not text. How do I get the formula to look at [Date - do not edit}1 and count back 367 days?

Other column context - row 2 is "for dummies", row 3 is my original formula with just one variable (without the rolling 12 months), row 5 is the test formula I did with your syntax that worked great.

Paul Newcome

Are you looking to pull in everything that is 367 days in the past, or are you looking to pull anything that is between today and 367 days in the past? The way your formula is currently written, it will only pull rows where the date in the range is equal to today minus 367 days. If you want to include everything in between then you will need to make this adjustment:

=AVG(COLLECT({Pricing Resource Tools Tracking Variance to Target}, {Pricing Resource Tools Tracking Producer Name}, =[Column2]1, {Pricing Resource Tools Tracking Ren/Eff Date}, @cell>= [Date - do not edit]1 - 367))

Becky Parker

Thanks @Paul Newcome and @Amanda Jeske and @Justin Peters. This was a doozy! We couldn't get the last criterion & range to work (from the suggested formula above) so we dumped the original idea of using the [Date - do not edit] column and instead added a new column in the original reference sheet to determine if the Ren/Eff Date was within the last 367 days. It was a challenge but once I figured out we were only returning dates more than 367 days ago rather than within the last 367 days, Justin got the correct formula: =IF(AND([Renewal / Effective Date]@row <= TODAY(), [Renewal / Effective Date]@row >= TODAY(-366)), "Yes", "No"). The original formula @Paul Newcome suggested now looks like =AVG(COLLECT({Pricing Resource Tools Tracking Variance to Target}, {Pricing Resource Tools Tracking Producer Name}, =[Column2]1, {Pricing Resource Tools Tracking Ren/Eff Date 12 mo}, ="Yes")). I never want to have to do this again but now I know the source for the best help if I need it. Thank you all, again.

Paul Newcome

@Becky Parker Happy to help. 👍️