Fiscal Quarter Percentage difference

W

Walker_Ice

Board Regular
Joined
Oct 6, 2023
Messages
50
Office Version
  1. 2021
Platform
  1. MacOS
Hi Everyone,

I have a table that calculates the revenue and expenses per quarter of the selected fiscal years. For the selected fiscal year, its easy to get what the profit percentage is for the quarters. But I want to be able to calculate the profit percentage difference is for the selected year and the previous year.
For example, If select "2023" as the year. I want be able to calculate the profit percentage is for the 2023 quarter (AMJ) and what the profit percentage is for the previous year quarter ('2022 AMJ" - in this example). I want to know what the percentage difference is between these two percentages. Is the business up during the quarter or is it down and by how much?

Any help would be appreciated. Thank you in advance.








TEsting_Excel_File.xlsx
ABCDEFGHIJKLMNO
1Income ServicesExpense_DatesExpense_TotalSpent
2ServiceDatePaymentTipsTotal2/4/21$858.00
3MDL4-Feb-21$900.00$0.00$900.003/13/23$750.00
4LDL13-Mar-23$300.00$25.00$325.006/17/22$75,000.00FISCAL YEAR QUARTERS:2023
5POL17-Jun-22$200.00$25.00$225.006/7/23$7,000.00RevenueTotal CostProfitsCurrent Quarter profit %Quarter difference profit %
6POW7-Jun-23$900.00$0.00$900.007/10/23$85,000.00JFM$917,101.00($126,560.00)$790,541.0086.2%
7MAN10-Jul-23$1,900.00$0.00$1,900.003/31/24$851,002.00AMJ$2,700.00($13,844.00)($11,144.00)-412.7%
8INO31-Mar-24$23.00$10.00$33.005/26/23$6,520.00JAS$1,900.00($85,000.00)($83,100.00)-4373.7%
9YOU26-May-23$875.00$10.00$885.002/18/23$1,000.00OND$0.00($77.00)($77.00)#DIV/0!
10TBW18-Feb-23$750.00$10.00$760.006/1/23$324.00Year Total:$921,701.00($225,481.00)$696,220.0075.5%
11DPW1-Jun-23$600.00$10.00$610.002/18/24$4,243.00
12MDL18-Feb-24$600.00$5.00$605.003/18/23$4,537.00
13RDL18-Mar-23$850,999.00$5.00$851,004.005/28/22$8,789.00
14NAM28-May-22$85,000.00$0.00$85,000.002/8/23$75.00
15PPP8-Feb-23$10,000.00$10.00$10,010.003/3/23$433.00
16PPE3-Mar-23$10,090.00$10.00$10,100.001/4/21$2,224.00
17PPE4-Jan-21$100.00$0.00$100.002/14/23$42,235.00
18INO14-Feb-23$23,998.00$0.00$23,998.0011/1/23$77.00
19LDL1-Apr-23$300.00$5.00$305.001/3/22$78.00
20HDL3-Jan-22$8,500.00$0.00$8,500.002/25/23$987.00
21AND25-Feb-23$20,000.00$0.00$20,000.006/26/25$4,567.00
22WWW26-Jun-25$79,000.00$5.00$79,005.003/10/23$76,543.00
23WDL10-Mar-23$899.00$5.00$904.002/2/25$9,357.00
Sheet5
Cell Formulas
RangeFormula
K6:K9K6=IFERROR( SUMPRODUCT( Income_TotalPay * IF($K$4="All", 1, (YEAR(Income_Dates) = $K$4)) * (ROUNDUP(MONTH(Income_Dates)/3, 0) = ROWS($J$6:$J6)) ), 0 )
L6:L9L6=IFERROR( -SUMPRODUCT( Expense_TotalSpent * IF($K$4="All", 1, (YEAR(Expense_Dates) = $K$4)) * (ROUNDUP(MONTH(Expense_Dates)/3, 0) = ROWS($J$6:$J6)) ), 0 )
M6:M10,E3:E23M6=SUM(K6:L6)
N6:N10N6=M6/K6
K10:L10K10=SUM(K6:K9)
Named Ranges
NameRefers ToCells
Expense_Dates=Sheet5!$G$2:$G$22L6:L9
Expense_TotalSpent=Sheet5!$H$2:$H$22L6:L9
Income_Dates=Sheet5!$B$3:$B$23K6:K9
Income_TotalPay=Sheet5!$E$3:$E$23K6:K9
Cells with Data Validation
CellAllowCriteria
K4ListAll,2021,2022,2023,2024,2025
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
This is what I have so far. But still not functioning as expected. I'm getting 0 and don't know where I'm going wrong. Any help would be appreciated.

=IFERROR( ( (SUMPRODUCT(Income_TotalPay * IF($O$22="All", 1, (YEAR(Expense_Dates) = $O$22)) * (ROUNDUP(MONTH(Expense_Dates)/3, 0) = ROWS($K$24:$K24))) - SUMPRODUCT(Expense_TotalSpent * IF($O$22="All", 1, (YEAR(Expense_Dates) = $O$22)) * (ROUNDUP(MONTH(Expense_Dates)/3, 0) = ROWS($K$24:$K24)))) / SUMIFS(Expense_TotalSpent, YEAR(Expense_Dates), $O$22, ROUNDUP(MONTH(Expense_Dates)/3, 0), ROWS($K$24:$K24)) ) - ( (SUMPRODUCT(Income_TotalPay * IF($O$22="All", 1, (YEAR(Expense_Dates) = ($O$22 - 1))) * (ROUNDUP(MONTH(Expense_Dates)/3, 0) = ROWS($K$24:$K24))) - SUMPRODUCT(Expense_TotalSpent * IF($O$22="All", 1, (YEAR(Expense_Dates) = ($O$22 - 1))) * (ROUNDUP(MONTH(Expense_Dates)/3, 0) = ROWS($K$24:$K24)))) / SUMIFS(Expense_TotalSpent, YEAR(Expense_Dates), $O$22-1, ROUNDUP(MONTH(Expense_Dates)/3, 0), ROWS($K$24:$K24)) ), 0)
 
Upvote 0
There are a few problems with your formula ...

- You can't use SUMIFS for a calculation like this, as it expects range arguments, not calculated values.
- It calculates profit as (Income - Expenses )/ Expenses rather than as Income - Expenses / Income as you've done in your original formulae.
- It doesn't allow correctly for the possibility of O22="All".

Wouldn't you be better off calculating results separately for the two quarters, so that you compare them side by side, like this:

ABCDEFGHIJKL
1Income ServicesExpense_DatesExpense_TotalSpent
2ServiceDatePaymentTipsTotal4 Feb 2021858
3MDL4 Feb 2021900090013 Mar 2023750
4LDL13 Mar 20233002532517 Jun 202275,000Year2023
5POL17 Jun 2022200252257 Jun 20237,000Qtr1
6POW7 Jun 2023900090010 Jul 202385,000
7MAN10 Jul 20231,90001,90031 Mar 2024851,002IncomeCosts
8INO31 Mar 202423103326 May 20236,520Current year917,101126,560
9YOU26 May 20238751088518 Feb 20231,000Previous year8,50078
10TBW18 Feb 2023750107601 Jun 2023324
11DPW1 Jun 20236001061018 Feb 20244,243
12MDL18 Feb 2024600560518 Mar 20234,537
13RDL18 Mar 2023850,9995851,00428 May 20228,789
14NAM28 May 202285,000085,0008 Feb 202375
15PPP8 Feb 202310,0001010,0103 Mar 2023433
16PPE3 Mar 202310,0901010,1004 Jan 20212,224
17PPE4 Jan 2021100010014 Feb 202342,235
18INO14 Feb 202323,998023,9981 Nov 202377
19LDL1 Apr 202330053053 Jan 202278
20HDL3 Jan 20228,50008,50025 Feb 2023987
21AND25 Feb 202320,000020,00026 Jun 20254,567
22WWW26 Jun 202579,000579,00510 Mar 202376,543
23WDL10 Mar 202389959042 Feb 20259,357
Sheet1
Cell Formulas
RangeFormula
K8K8=SUMPRODUCT(Income_TotalPay,--(YEAR(Income_Dates)=$K$4),--(ROUNDUP(MONTH(Income_Dates)/3,0)=$K$5))
L8L8=SUMPRODUCT(Expense_TotalSpent,--(YEAR(Expense_Dates)=$K$4),--(ROUNDUP(MONTH(Expense_Dates)/3,0)=$K$5))
K9K9=SUMPRODUCT(Income_TotalPay,--(YEAR(Income_Dates)=$K$4-1),--(ROUNDUP(MONTH(Income_Dates)/3,0)=$K$5))
L9L9=SUMPRODUCT(Expense_TotalSpent,--(YEAR(Expense_Dates)=$K$4-1),--(ROUNDUP(MONTH(Expense_Dates)/3,0)=$K$5))
E3:E23E3=SUM(C3:D3)
Named Ranges
NameRefers ToCells
Expense_Dates=Sheet3!$G$2:$G$23L8:L9
Expense_TotalSpent=Sheet3!$H$2:$H$23L8:L9
Income_Dates=Sheet3!$B$3:$B$23K8:K9
Income_TotalPay=Sheet3!$E$3:$E$23K8:K9

The more fundamental question is whether it is appropriate to compare two percentages in this way ?

Walker_Ice said:
I want to know what the percentage difference is between these two percentages. Is the business up during the quarter or is it down and by how much?
Click to expand...

In the scenario below, your approach will calculate -7%. Does quoting -7% make sense to you when income is up 50%, and profit up 25%?

IncomeCostProfit %Profit $
Q1 20221006040%40
Q1 202315010033%50
-7%+25%
 
Upvote 0
Hey Stephen,

Thank you for your response. I see what you did, the reason I need it all to be in 1 cell in a singular formula is because I don't have enough space in the spreadsheet that I am working on. When I print the sheet I need it all to be in one sheet. Thats why this is complicated problem and the biggest constraint is the space that I am working with. Every time I try to combine the formula into a singular version I always get errors.
Can you help me make this work with a singular formula?

Thank you in advanced.
 
Upvote 0
Hi,

Below is what I have so far. I keep getting lost in the logic, when trying to combine all of this into a singular formula.

I know this formula works for Current year + Previous year Expenses. It does seem to break when I select "All" and I'm not sure what happens but it produces a number that is not valid.

=IFERROR( -SUMPRODUCT(Expense_TotalSpent * IF($O$22="All", 1, (YEAR(Expense_Dates) = $O$22)) * (ROUNDUP(MONTH(Expense_Dates)/3, 0) = ROWS($K$24:$K26))) + -SUMPRODUCT(Expense_TotalSpent * IF($O$22="All", 1, (YEAR(Expense_Dates) = ($O$22 - 1))) * (ROUNDUP(MONTH(Expense_Dates)/3, 0) = ROWS($K$24:$K26))), 0)

I tried applying the same formula to the calculate the total Income for current year and previous year but it doesn't seem to be able to calculate it correctly. Especially if I select "All", It gives me a very big number that I have no idea how it's getting the number.

=IFERROR( SUMPRODUCT(Income_TotalPay * IF($O$22="All", 1, (YEAR(Income_Dates) = $O$22)) * (ROUNDUP(MONTH(Income_Dates)/3, 0) = ROWS($K$24:$K26))) + SUMPRODUCT(Income_Dates * IF($O$22="All", 1, (YEAR(Income_Dates) = ($O$22 - 1))) * (ROUNDUP(MONTH(Income_Dates)/3, 0) = ROWS($K$24:$K26))), 0)

Can someone please help me in fixing these forumlas and then combining them to accomplish the goal? Any help would be appreciated, I am getting so lost in the logic and have been working on this problem for several days.

Thank you 🙏
 
Upvote 0
I think you're looking for this?

ABCDEFGHIJKLM
1Income ServicesExpense_DatesExpense_TotalSpent
2ServiceDatePaymentTipsTotal4 Feb 2021858
3MDL4 Feb 2021900090013 Mar 2023750
4LDL13 Mar 20233002532517 Jun 202275,000Year2023
5POL17 Jun 2022200252257 Jun 20237,000QtrALL
6POW7 Jun 2023900090010 Jul 202385,000
7MAN10 Jul 20231,90001,90031 Mar 2024851,002IncomeCostsMargin
8INO31 Mar 202423103326 May 20236,520Current year921,611225,48175.5%
9YOU26 May 20238751088518 Feb 20231,000Previous year93,70083,86710.5%
10TBW18 Feb 2023750107601 Jun 202332465.0%
11DPW1 Jun 20236001061018 Feb 20244,243In one go65.0%
12MDL18 Feb 2024600560518 Mar 20234,537
13RDL18 Mar 2023850,9995851,00428 May 20228,789
14NAM28 May 202285,000085,0008 Feb 202375
15PPP8 Feb 202310,0001010,0103 Mar 2023433
16PPE3 Mar 202310,0901010,1004 Jan 20212,224
17PPE4 Jan 2021100010014 Feb 202342,235
18INO14 Feb 202323,998023,9981 Nov 202377
19LDL1 Apr 202330053053 Jan 202278
20HDL3 Jan 20228,50008,50025 Feb 2023987
21AND25 Feb 202320,000020,00026 Jun 20254,567
22WWW26 Jun 202579,000579,00510 Mar 202376,543
23WDL10 Mar 202389959042 Feb 20259,357
Sheet1
Cell Formulas
RangeFormula
K8K8=SUMPRODUCT(Income_TotalPay,(YEAR(Income_Dates)=$K$4)*IF($K$5="ALL",1,ROUNDUP(MONTH(Income_Dates)/3,0)=$K$5))
L8L8=SUMPRODUCT(Expense_TotalSpent,(YEAR(Expense_Dates)=$K$4)*IF($K$5="ALL",1,ROUNDUP(MONTH(Expense_Dates)/3,0)=$K$5))
K9K9=SUMPRODUCT(Income_TotalPay,(YEAR(Income_Dates)=$K$4-1)*IF($K$5="ALL",1,ROUNDUP(MONTH(Income_Dates)/3,0)=$K$5))
L9L9=SUMPRODUCT(Expense_TotalSpent,(YEAR(Expense_Dates)=$K$4-1)*IF($K$5="ALL",1,ROUNDUP(MONTH(Expense_Dates)/3,0)=$K$5))
M8:M9M8=1-L8/K8
M10M10=M8-M9
M11M11=SUMPRODUCT(Expense_TotalSpent,(YEAR(Expense_Dates)=$K$4-1)*IF($K$5="ALL",1,ROUNDUP(MONTH(Expense_Dates)/3,0)=$K$5))/SUMPRODUCT(Income_TotalPay,(YEAR(Income_Dates)=$K$4-1)*IF($K$5="ALL",1,ROUNDUP(MONTH(Income_Dates)/3,0)=$K$5))-SUMPRODUCT(Expense_TotalSpent,(YEAR(Expense_Dates)=$K$4)*IF($K$5="ALL",1,ROUNDUP(MONTH(Expense_Dates)/3,0)=$K$5))/SUMPRODUCT(Income_TotalPay,(YEAR(Income_Dates)=$K$4)*IF($K$5="ALL",1,ROUNDUP(MONTH(Income_Dates)/3,0)=$K$5))
E3:E23E3=SUM(C3:D3)
Named Ranges
NameRefers ToCells
Expense_Dates=Sheet1!$G$2:$G$23M11, L8:L9
Expense_TotalSpent=Sheet1!$H$2:$H$23M11, L8:L9
Income_Dates=Sheet1!$B$3:$B$23M11, K8:K9
Income_TotalPay=Sheet1!$C$3:$C$23M11, K8:K9, E3
 
Upvote 0
Hi Stephen,

Thank you for your reply. When I apply it to my spreadsheet, I am coming up with an error. I think it's because I do not have separate cells for the Year & QTR, like you have set up here.
In my spreadsheet, I have cell O22, where the user can select the specific year or "All". I tried making it just O22 for the cell being selected but I keep getting an error. Below is what I put into my spreadsheet to try and get the same results as you but didn't have any luck. Any advice would be greatly appreciated. Thank you.

=SUMPRODUCT(Expense_TotalSpent, (YEAR(Expense_Dates) = $O$22 - 1) * IF($O$22 = "ALL", 1, ROUNDUP(MONTH(Expense_Dates)/3, 0) = $O$22)) / SUMPRODUCT(Income_TotalPay, (YEAR(Income_Dates) = $O$22 - 1) * IF($O$22 = "ALL", 1, ROUNDUP(MONTH(Income_Dates)/3, 0) = $O$22)) - SUMPRODUCT(Expense_TotalSpent, (YEAR(Expense_Dates) = $O$22) * IF($O$22 = "ALL", 1, ROUNDUP(MONTH(Expense_Dates)/3, 0) = $O$22)) / SUMPRODUCT(Income_TotalPay, (YEAR(Income_Dates) = $O$22) * IF($O$22 = "ALL", 1, ROUNDUP(MONTH(Income_Dates)/3, 0) = $O$22))
 
Upvote 0
Sorry, I went back to your original layout, and realise I mis-interpreted your "ALL"requirement. I thought this meant all quarters, but in fact it means all years.

I think this is closer to what you're looking for? (Although the change in margin - a difference of two percentages - still doesn't make sense to me).

ABCDEFGHIJKLMN
1Income ServicesExpense_DatesExpense_TotalSpent
2ServiceDatePaymentTipsTotal4 Feb 2021858
3MDL4 Feb 2021900090013 Mar 2023750
4LDL13 Mar 20233002532517 Jun 202275,000Year2022
5POL17 Jun 2022200252257 Jun 20237,000Change in
6POW7 Jun 2023900090010 Jul 202385,000MarginMargin
7MAN10 Jul 20231,90001,90031 Mar 2024851,002JFM8,5007899.1%307.3%
8INO31 Mar 202423103326 May 20236,520AMJ85,22583,7891.7%n/a
9YOU26 May 20238751088518 Feb 20231,000JAS00n/an/a
10TBW18 Feb 2023750107601 Jun 2023324OND00n/an/a
11DPW1 Jun 20236001061018 Feb 20244,243Total Year93,72583,867
12MDL18 Feb 2024600560518 Mar 20234,537
13RDL18 Mar 2023850,9995851,00428 May 20228,789
14NAM28 May 202285,000085,0008 Feb 202375
15PPP8 Feb 202310,0001010,0103 Mar 2023433
16PPE3 Mar 202310,0901010,1004 Jan 20212,224
17PPE4 Jan 2021100010014 Feb 202342,235
18INO14 Feb 202323,998023,9981 Nov 202377
19LDL1 Apr 202330053053 Jan 202278
20HDL3 Jan 20228,50008,50025 Feb 2023987
21AND25 Feb 202320,000020,00026 Jun 20254,567
22WWW26 Jun 202579,000579,00510 Mar 202376,543
23WDL10 Mar 202389959042 Feb 20259,357
Sheet1
Cell Formulas
RangeFormula
K7:K10K7=SUMPRODUCT(Income_TotalPay,IF($K$4="ALL",1,YEAR(Income_Dates)=$K$4)*(ROUNDUP(MONTH(Income_Dates)/3,0)=ROWS($J$7:$J7)))
L7:L10L7=SUMPRODUCT(Expense_TotalSpent,IF($K$4="ALL",1,YEAR(Expense_Dates)=$K$4)*(ROUNDUP(MONTH(Expense_Dates)/3,0)=ROWS($J$7:$J7)))
M7:M10M7=IFERROR(1-L7/K7,"n/a")
N7:N10N7=IFERROR(M7-(1-SUMPRODUCT(Expense_TotalSpent,(YEAR(Expense_Dates)=$K$4-1)*(ROUNDUP(MONTH(Expense_Dates)/3,0)=ROWS($J$7:$J7)))/SUMPRODUCT(Income_TotalPay,(YEAR(Income_Dates)=$K$4-1)*(ROUNDUP(MONTH(Income_Dates)/3,0)=ROWS($J$7:$J7)) )),"n/a")
K11:L11K11=SUM(K7:K10)
E3:E23E3=SUM(C3:D3)
Named Ranges
NameRefers ToCells
Expense_Dates=Sheet1!$G$2:$G$23L7:L10, N7:N10
Expense_TotalSpent=Sheet1!$H$2:$H$23L7:L10, N7:N10
Income_Dates=Sheet1!$B$3:$B$23K7:K10, N7:N10
Income_TotalPay=Sheet1!$E$3:$E$23K7:K10, N7:N10

I assume you only want the change in margin from one year to the next? My formula will return N/A if you select "ALL"
 
Upvote 0
Hi Stephen,

Yes, the point of this formula is to calculate Y/Y profits. That is my goal and maybe I am not clear in my description.
So the goal is to have 1 column that calculates the profits percentage for the current year. And the column next to it that calculates the Y/Y profits. Just how publicly traded companies report in their quarterly reports, showing the Y/Y profits in %. I hope this helps clarify what I am trying to accomplish with this formula. Maybe my math is wrong or my description is wrong, but I hope this will make things clearer on what the reason for this formula is.

Thank you for your help and patience.
 
Upvote 0
Y/Y results usually measure $/$ as a %.

Example: Revenue Year 1 = $1 million, Revenue Yr 2 = $1.2 m, therefore Revenue is up 20% on a Y/Y basis.

As I said in Post #3, your approach compares two percentages:

IncomeCostProfit %Profit $
Q1 20221006040%40
Q1 202315010033%50
-7%+25%

It makes sense to report income is up 50%. Or that profit is up 25%.

Your approach calculates -7%, which is not a meaningful number.

Also, if you're looking for Y/Y, I'm not sure why your formula are calculating quarterly?

Perhaps you could post some simplified data and the results you're expecting to see?
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Filter/Matching HELP
Replies
5
Views
457
Alex Blakenburg
A
A
  • Solved
Calculate monthly booked percentage of hotel rooms
2 3
Replies
21
Views
340
arendberg
A
W
  • Solved
HELP finding first Date of Clients
Replies
4
Views
323
CephasOz
CephasOz
K
  • Solved
Macro to compare old prices with new prices for stock and show(-/+)
4 5 6
Replies
58
Views
685
Peter_SSs
Peter_SSs
WESTERNWALL
  • Question Question
Sum and Count functions
Replies
4
Views
241
WESTERNWALL
WESTERNWALL

Forum statistics

Threads
1,224,818
Messages
6,181,151
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top