Quarter Differentials

Tony Fronza

Hello! I need to create a formula showing the differentials between quarters for two types of data - the total users and total shares for social media posts entered in the sheet. I created fields for Q2 User Differential and Q2 Total Share Differential and assume I'll need Sum and IF logic built in.

For the Q2 differentials, I want the formula to return the difference between the 1st quarter's User total and the 2nd quarter's user total and apply the same logic for the Total Shares column.

I attached an Excel export of the sheet for a visual:

Amplify - Overall East Stats.xlsx

Ward.Hively

Hi Tony, Are you looking for a column formula so that differentials are automatically calculated when new data is entered?

Tony Fronza

@Ward.Hively Hello! Yes, a formula that will calculate the differential from the previous quarter. If possible, I'd like to set it up where all you need to do is change the year in the formula to perpetuate the fields (I set up others like this).

Ward.Hively

Tony, I like this idea. I have worked through plenty of formulas but never considered doing it this way before. Often, column formulas work with @row or @cell or something to the effect but not usually randomly between rows. Doesn't mean it couldn't be done. In fact, I argue it should be done more.

I always start my day with one warm up solution, and this will be it for today!

Let's try this... (See Attached PDF)

Tony.pdf

Ward.Hively

Tony, I like this idea. I have worked through plenty of formulas but never considered doing it this way before. Often, column formulas work with @row or @cell or something to the effect but not usually randomly between rows. Doesn't mean it couldn't be done. In fact, I argue it should be done more.

I always start my day with one warm up solution, and this will be it for today!

Let's try this...

First let's assume:

• Quarter is a drop down with the options 1st, 2nd, 3rd and 4th.
• 2022 Q4 is the previous Quarter to 2023 Q1
• Q1 Differential is Q1-Q4(PY)
• Q2 Differential is Q2-Q1(Same Year)
• Q3 Differential is Q3-Q2(Same Year)
• Q4 Differential is Q4-Q3(Same Year)

Here's a step-by-step explanation of the logic in the formula:

1. The formula starts with an outermost IF function:
   • It checks if the current quarter (Quarter@row) is equal to "1st". If this condition is true, it performs the calculation for the corresponding column and subtracts the sum of values from the "4th" quarter of the previous year (Year@row - 1) for that same column using the SUMIFS function. The result is assigned to the corresponding column.
2. If the condition in the outermost IF is false, it moves on to the next IF function:
   • This inner IF checks if the current quarter is "2nd". If true, it calculates the value for the corresponding column by subtracting the sum of values from the "1st" quarter of the current year for that same column using the SUMIFS function.
3. The pattern continues for the other quarters (3rd and 4th), with each inner IF function checking the current quarter and performing a calculation based on the quarter and year.
4. If none of the conditions in the IF functions are met (i.e., if the current quarter doesn't match any of the specified quarters), an empty string "" is returned as the result. This serves as a default value when none of the conditions are true.

In essence, this formula adjusts values in different columns based on the quarter and year. It subtracts the sum of values from the corresponding previous quarter and year for each column, effectively calculating the change or difference between the current quarter and the specified reference quarter. The formula ensures that the calculation is done differently depending on the current quarter, with each quarter having its logic defined within the nested IF functions.

This will work for each of your columns as column formulas. Try these out! You will need to add the corresponding differential columns of course. I'd like to see these on a line graph!

If you like this content or would like further help, I invite you to our LinkedIn Page posted in the link below! Me and a couple of other Smartsheet fanatics decided to make a career out of it!

Users

=IF(Quarter@row = "1st", Users@row - SUMIFS(Users:Users, Quarter:Quarter, "4th", Year:Year, Year@row - 1), IF(Quarter@row = "2nd", Users@row - SUMIFS(Users:Users, Quarter:Quarter, "1st", Year:Year, Year@row), IF(Quarter@row = "3rd", Users@row - SUMIFS(Users:Users, Quarter:Quarter, "2nd", Year:Year, Year@row), IF(Quarter@row = "4th", Users@row - SUMIFS(Users:Users, Quarter:Quarter, "3rd", Year:Year, Year@row), ""))))

Total Shares

=IF(Quarter@row = "1st", [Total Shares]@row - SUMIFS([Total Shares]:[Total Shares], Quarter:Quarter, "4th", Year:Year, Year@row - 1), IF(Quarter@row = "2nd", [Total Shares]@row - SUMIFS([Total Shares]:[Total Shares], Quarter:Quarter, "1st", Year:Year, Year@row), IF(Quarter@row = "3rd", [Total Shares]@row - SUMIFS([Total Shares]:[Total Shares], Quarter:Quarter, "2nd", Year:Year, Year@row), IF(Quarter@row = "4th", [Total Shares]@row - SUMIFS([Total Shares]:[Total Shares], Quarter:Quarter, "3rd", Year:Year, Year@row), ""))))

Potential Reach

=IF(Quarter@row = "1st", [Potential Reach]@row - SUMIFS([Potential Reach]:[Potential Reach], Quarter:Quarter, "4th", Year:Year, Year@row - 1), IF(Quarter@row = "2nd", [Potential Reach]@row - SUMIFS([Potential Reach]:[Potential Reach], Quarter:Quarter, "1st", Year:Year, Year@row), IF(Quarter@row = "3rd", [Potential Reach]@row - SUMIFS([Potential Reach]:[Potential Reach], Quarter:Quarter, "2nd", Year:Year, Year@row), IF(Quarter@row = "4th", [Potential Reach]@row - SUMIFS([Potential Reach]:[Potential Reach], Quarter:Quarter, "3rd", Year:Year, Year@row), ""))))

Clicks

=IF(Quarter@row = "1st", [Clicks]@row - SUMIFS([Clicks]:[Clicks], Quarter:Quarter, "4th", Year:Year, Year@row - 1), IF(Quarter@row = "2nd", [Clicks]@row - SUMIFS([Clicks]:[Clicks], Quarter:Quarter, "1st", Year:Year, Year@row), IF(Quarter@row = "3rd", [Clicks]@row - SUMIFS([Clicks]:[Clicks], Quarter:Quarter, "2nd", Year:Year, Year@row), IF(Quarter@row = "4th", [Clicks]@row - SUMIFS([Clicks]:[Clicks], Quarter:Quarter, "3rd", Year:Year, Year@row), ""))))

Total Engagement

=IF(Quarter@row = "1st", [Total Engagements]@row - SUMIFS([Total Engagements]:[Total Engagements], Quarter:Quarter, "4th", Year:Year, Year@row - 1), IF(Quarter@row = "2nd", [Total Engagements]@row - SUMIFS([Total Engagements]:[Total Engagements], Quarter:Quarter, "1st", Year:Year, Year@row), IF(Quarter@row = "3rd", [Total Engagements]@row - SUMIFS([Total Engagements]:[Total Engagements], Quarter:Quarter, "2nd", Year:Year, Year@row), IF(Quarter@row = "4th", [Total Engagements]@row - SUMIFS([Total Engagements]:[Total Engagements], Quarter:Quarter, "3rd", Year:Year, Year@row), ""))))

Active Users

=IF(Quarter@row = "1st", [Active Users]@row - SUMIFS([Active Users]:[Active Users], Quarter:Quarter, "4th", Year:Year, Year@row - 1), IF(Quarter@row = "2nd", [Active Users]@row - SUMIFS([Active Users]:[Active Users], Quarter:Quarter, "1st", Year:Year, Year@row), IF(Quarter@row = "3rd", [Active Users]@row - SUMIFS([Active Users]:[Active Users], Quarter:Quarter, "2nd", Year:Year, Year@row), IF(Quarter@row = "4th", [Active Users]@row - SUMIFS([Active Users]:[Active Users], Quarter:Quarter, "3rd", Year:Year, Year@row), ""))))

Avg. Share Per User

=IF(Quarter@row = "1st", [Avg. Share Per User]@row - SUMIFS([Avg. Share Per User]:[Avg. Share Per User], Quarter:Quarter, "4th", Year:Year, Year@row - 1), IF(Quarter@row = "2nd", [Avg. Share Per User]@row - SUMIFS([Avg. Share Per User]:[Avg. Share Per User], Quarter:Quarter, "1st", Year:Year, Year@row), IF(Quarter@row = "3rd", [Avg. Share Per User]@row - SUMIFS([Avg. Share Per User]:[Avg. Share Per User], Quarter:Quarter, "2nd", Year:Year, Year@row), IF(Quarter@row = "4th", [Avg. Share Per User]@row - SUMIFS([Avg. Share Per User]:[Avg. Share Per User], Quarter:Quarter, "3rd", Year:Year, Year@row), ""))))

Tony Fronza

@Ward.Hively this is outstanding! Thank you so much for putting so much energy into this. I will test out the formulas by adjusting my column headers, the year, etc. and will keep you posted :)

Ward.Hively

Tony,

Glad to help! I got as much value out of it as you did! If you need anything else, please don’t hesitate to reach out!

Tony Fronza

@Ward.Hively I tried using the formula to account for differentials in total # of posts for our region, but I receive an UNPARSEABLE error message:

=IF(Quarter@row = "1st", [East Posts]@row - SUMIFS([East Posts]:[East Posts], Quarter:Quarter, "4th", Year:Year, Year@row - 1), IF(Quarter@row = "2nd", [East Posts]@row - SUMIFS([East Posts]:[East Posts], Quarter:Quarter, "1st", Year:Year, Year@row), IF(Quarter@row = "3rd", [East Posts]@row - SUMIFS([East Posts]:[East Posts], Quarter:Quarter, "2nd", Year:Year, Year@row), IF(Quarter@row = "4th", [East Posts]@row - SUMIFS([East Posts]:[East Posts], Quarter:Quarter, "3rd", Year:Year, Year@row), ""))))

Ward.Hively

Hi Tony!

Could you post the excel file after the changes so I can see what may be going on?

Tony Fronza

@Ward.Hively Yes. Please see the attached (though the formulas are in the Sheet Summary fields).

LinkedIn Stat Tracker.xlsx

Ward.Hively

Hi @Tony Fronza ,

I receive no error with the formula. Simply imported your spreadsheet to Smartsheet, copied your formula exact, converted to column formula (Column10) and all is well.

I don't want to be presumptuous, but I say this assuming "Easts Posts" is a column for the total # of posts for your region.

If not, I have a trick for you that can autogenerate this for any Column you have. You can use this link to schedule a meeting with me!

https://calendly.com/take-the-skyway/30min

Tony Fronza

@Ward.Hively ok, I'll check it again. I'm entering it as a Sheet Summary Field. Are you doing the same? Also, I forgot to ask - how will the formula account for data in 2024 and beyond? Do I need to modify the formula?

Tony Fronza

@Ward.Hively I used the formula as a column, and it worked! Is there a way to use this formula as a sheet summary field? I ask because that is easier to use as metrics on a dashboard (at least, in my experience). I want four sheet summary fields (one for each quarter) that dynamically pull from the respective year's data. The end-user will utilize the sheet to track data ongoing from year to year. Perhaps, I'm overcomplicating this.

Ward.Hively

Tony,

My recommendation is to use the column formulas as I have distributed and have the sheet summaries populate off the column formulas. This will create a low maintenance solution that enables automation. If these columns add undesired weight to your sheet, simply hide them. The formula's below will always be relevant for this year. No maintenance will be required.

For example, the formula's for User Differential would be as follows:

Q1 =SUMIFS([User Differential]:[User Differential], Year:Year, YEAR(TODAY()), Quarter:Quarter, "1st")

Q2=SUMIFS([User Differential]:[User Differential], Year:Year, YEAR(TODAY()), Quarter:Quarter, "2nd")

Q3=SUMIFS([User Differential]:[User Differential], Year:Year, YEAR(TODAY()), Quarter:Quarter, "3rd")

Q4=SUMIFS([User Differential]:[User Differential], Year:Year, YEAR(TODAY()), Quarter:Quarter, "4th")

I would use ChatGPT to duplicate these formulas using a prompt such as:

"For the following column names : "A,B,C,D..." populate these formulas replacing [User Differential]:[User Differential] with each respective column name.

Q1 =SUMIFS([User Differential]:[User Differential], Year:Year, YEAR(TODAY()), Quarter:Quarter, "1st")

Q2=SUMIFS([User Differential]:[User Differential], Year:Year, YEAR(TODAY()), Quarter:Quarter, "2nd")

Q3=SUMIFS([User Differential]:[User Differential], Year:Year, YEAR(TODAY()), Quarter:Quarter, "3rd")

Q4=SUMIFS([User Differential]:[User Differential], Year:Year, YEAR(TODAY()), Quarter:Quarter, "4th")"

Tony Fronza

@Ward.Hively this works perfectly! One last question - what should I add to the formula to result in 0 if there is no data populated yet for the subsequent quarter? For example, I'm receiving a -66 differential from Q2-Q3 because no data was entered yet for Q3.

Genevieve P.

Hey @Tony Fronza

I haven't gone through this entire post, but based on one of the more recent screen captures, you could simply add an IF statement at the front.

=IF([Total Account Posts]@row = 0, 0, rest of your formula...

This will first check if the Total Account Posts for this row is 0. If it is, return 0. If it's not, then follow along with the rest of your formula.

Cheers,

Genevieve