Quarter Differentials
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:
Answers
-
Hi Tony, Are you looking for a column formula so that differentials are automatically calculated when new data is entered?
Ward Hively
President and Chief Consultant
🌉Skyway Consulting Co.🌉
Need Smartsheet Help? Schedule a complementary consultation click SCHEDULE
Does your Dashboard need a map that updates from Smartsheet Data? We pioneered 101+ ways to add a map to a Smartsheet Dashboard.
Smartsheet and GIS Integrations
✅ ArcGIS Online
✅ Arc Py
✅ Smartsheet Advanced
✅ Manage your GIS records from Dynamic View, Sheet or Report
✅ No Code (Limited Applications Apply)
Website: https://skywayconsultingco.com
LinkedIn: (49) Ward Hively | LinkedIn
-
@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).
-
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)
Ward Hively
President and Chief Consultant
🌉Skyway Consulting Co.🌉
Need Smartsheet Help? Schedule a complementary consultation click SCHEDULE
Does your Dashboard need a map that updates from Smartsheet Data? We pioneered 101+ ways to add a map to a Smartsheet Dashboard.
Smartsheet and GIS Integrations
✅ ArcGIS Online
✅ Arc Py
✅ Smartsheet Advanced
✅ Manage your GIS records from Dynamic View, Sheet or Report
✅ No Code (Limited Applications Apply)
Website: https://skywayconsultingco.com
LinkedIn: (49) Ward Hively | LinkedIn
-
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:
- 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 theSUMIFS
function. The result is assigned to the corresponding column.
- It checks if the current quarter (
- If the condition in the outermost
IF
is false, it moves on to the nextIF
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 theSUMIFS
function.
- This inner
- 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. - 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), ""))))
Ward Hively
President and Chief Consultant
🌉Skyway Consulting Co.🌉
Need Smartsheet Help? Schedule a complementary consultation click SCHEDULE
Does your Dashboard need a map that updates from Smartsheet Data? We pioneered 101+ ways to add a map to a Smartsheet Dashboard.
Smartsheet and GIS Integrations
✅ ArcGIS Online
✅ Arc Py
✅ Smartsheet Advanced
✅ Manage your GIS records from Dynamic View, Sheet or Report
✅ No Code (Limited Applications Apply)
Website: https://skywayconsultingco.com
LinkedIn: (49) Ward Hively | LinkedIn
-
@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 :)
-
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!
Ward Hively
President and Chief Consultant
🌉Skyway Consulting Co.🌉
Need Smartsheet Help? Schedule a complementary consultation click SCHEDULE
Does your Dashboard need a map that updates from Smartsheet Data? We pioneered 101+ ways to add a map to a Smartsheet Dashboard.
Smartsheet and GIS Integrations
✅ ArcGIS Online
✅ Arc Py
✅ Smartsheet Advanced
✅ Manage your GIS records from Dynamic View, Sheet or Report
✅ No Code (Limited Applications Apply)
Website: https://skywayconsultingco.com
LinkedIn: (49) Ward Hively | LinkedIn
-
@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), ""))))
-
Hi Tony!
Could you post the excel file after the changes so I can see what may be going on?
Ward Hively
President and Chief Consultant
🌉Skyway Consulting Co.🌉
Need Smartsheet Help? Schedule a complementary consultation click SCHEDULE
Does your Dashboard need a map that updates from Smartsheet Data? We pioneered 101+ ways to add a map to a Smartsheet Dashboard.
Smartsheet and GIS Integrations
✅ ArcGIS Online
✅ Arc Py
✅ Smartsheet Advanced
✅ Manage your GIS records from Dynamic View, Sheet or Report
✅ No Code (Limited Applications Apply)
Website: https://skywayconsultingco.com
LinkedIn: (49) Ward Hively | LinkedIn
-
@Ward.Hively Yes. Please see the attached (though the formulas are in the Sheet Summary fields).
-
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
Ward Hively
President and Chief Consultant
🌉Skyway Consulting Co.🌉
Need Smartsheet Help? Schedule a complementary consultation click SCHEDULE
Does your Dashboard need a map that updates from Smartsheet Data? We pioneered 101+ ways to add a map to a Smartsheet Dashboard.
Smartsheet and GIS Integrations
✅ ArcGIS Online
✅ Arc Py
✅ Smartsheet Advanced
✅ Manage your GIS records from Dynamic View, Sheet or Report
✅ No Code (Limited Applications Apply)
Website: https://skywayconsultingco.com
LinkedIn: (49) Ward Hively | LinkedIn
-
@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?
-
@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.
-
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")"
Ward Hively
President and Chief Consultant
🌉Skyway Consulting Co.🌉
Need Smartsheet Help? Schedule a complementary consultation click SCHEDULE
Does your Dashboard need a map that updates from Smartsheet Data? We pioneered 101+ ways to add a map to a Smartsheet Dashboard.
Smartsheet and GIS Integrations
✅ ArcGIS Online
✅ Arc Py
✅ Smartsheet Advanced
✅ Manage your GIS records from Dynamic View, Sheet or Report
✅ No Code (Limited Applications Apply)
Website: https://skywayconsultingco.com
LinkedIn: (49) Ward Hively | LinkedIn
-
@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.
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!