YTD Growth YOY in Excel

jmenzel30

New Member
Joined
Dec 20, 2021
Messages
17
Office Version
  1. 2016
Platform
  1. Windows
Hello All,

So today my boss told me that he wanted a just a cell that displays YTD YOY growth. So essentially here is what we're working with for excel tables.

YearJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberTotal
2017$ 16,186.00$ 20,262.00$ 19,763.00$ 17,349.00$ 16,098.00$ 16,590.00$ 16,930.00$ 16,151.00$ 11,336.00$ 20,398.00$ 20,309.00$ 20,199.00$ 211,571.00
2018$ 16,331.00$ 16,566.00$ 16,281.00$ 18,645.00$ 16,706.00$ 18,708.00$ 18,655.00$ 17,813.00$ 18,833.00$ 17,529.00$ 17,858.00$ 18,140.00$ 212,065.00
2019$ 18,140.00$ 19,461.00$ 22,933.00$ 22,933.00$ 20,587.00$ 22,003.00$ 22,309.00$ 21,785.00$ 22,409.00$ 19,931.00$ 21,236.00$ 20,223.00$ 253,950.00
2020$ 20,247.00$ 21,908.00$ 22,661.00$ 18,234.00$ 18,011.00$ 21,137.00$ 19,018.00$ 20,781.00$ 19,979.00$ 19,975.00$ 20,336.00$ 16,980.00$ 239,267.00
2021$ 17,698.00$ 20,284.00$ 18,400.00$ 25,722.00$ 18,964.00$ 19,638.00$ 20,636.00$ 20,835.00$ 18,041.00$ 16,671.00$ 16,809.00$ 17,000.00$ 213,698.00
2022
YOY +/-=[Jan-2022]-[Jan-2021]Display results of the formula here
Growth %

Now I need to get the YOY amount and Growth % displayed just once in cell each. But I need it to be dynamic. So for example if we're putting data for May into the sheet, the cell will display the growth % and YOY for 2022 up until May. I might be super unclear in explaining this. (also I am aware that Pivot tables are much easier to manipulate in this regard, but the guy that signs my paycheck wants this format). Any help or sense of direction would be greatly appreciated.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Is this what you're looking for?

Book1
ABCDEFGHIJKLMN
1YearJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberTotal
22017$16,186.00$20,262.00$19,763.00$17,349.00$16,098.00$16,590.00$16,930.00$16,151.00$11,336.00$20,398.00$20,309.00$20,199.00$211,571.00
32018$16,331.00$16,566.00$16,281.00$18,645.00$16,706.00$18,708.00$18,655.00$17,813.00$18,833.00$17,529.00$17,858.00$18,140.00$212,065.00
42019$18,140.00$19,461.00$22,933.00$22,933.00$20,587.00$22,003.00$22,309.00$21,785.00$22,409.00$19,931.00$21,236.00$20,223.00$253,950.00
52020$20,247.00$21,908.00$22,661.00$18,234.00$18,011.00$21,137.00$19,018.00$20,781.00$19,979.00$19,975.00$20,336.00$16,980.00$239,267.00
62021$17,698.00$20,284.00$18,400.00$25,722.00$18,964.00$19,638.00$20,636.00$20,835.00$18,041.00$16,671.00$16,809.00$17,000.00$213,698.00
72022$21,000.00160002200022000
8YOY +/-$3,302.00($4,284.00)$3,600.00($3,722.00)        ($1,104.00)
9Growth %19%-21%20%-14%        -1%
Sheet1
Cell Formulas
RangeFormula
B8:M8B8=IF(B7<>"",SUM(B7-B6),"")
N8N8=(SUM((INDIRECT("$B$8:"&ADDRESS(8,SUMPRODUCT(MAX((B8:M8<>"")*COLUMN(B8:M8))))))))
B9:M9B9=IF(B$7<>"",((B$7-B$6)/B$6),"")
N9N9=((SUM(INDIRECT("$B$7:"&ADDRESS(7,SUMPRODUCT(MAX((B7:M7<>"")*COLUMN(B7:M7))))))-SUM(INDIRECT("$B$6:"&ADDRESS(6,SUMPRODUCT(MAX((B7:M7<>"")*COLUMN(B7:M7)))))))/SUM(INDIRECT("$B$6:"&ADDRESS(6,SUMPRODUCT(MAX((B7:M7<>"")*COLUMN(B7:M7)))))))
 
Upvote 0
Is this what you're looking for?

Book1
ABCDEFGHIJKLMN
1YearJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberTotal
22017$16,186.00$20,262.00$19,763.00$17,349.00$16,098.00$16,590.00$16,930.00$16,151.00$11,336.00$20,398.00$20,309.00$20,199.00$211,571.00
32018$16,331.00$16,566.00$16,281.00$18,645.00$16,706.00$18,708.00$18,655.00$17,813.00$18,833.00$17,529.00$17,858.00$18,140.00$212,065.00
42019$18,140.00$19,461.00$22,933.00$22,933.00$20,587.00$22,003.00$22,309.00$21,785.00$22,409.00$19,931.00$21,236.00$20,223.00$253,950.00
52020$20,247.00$21,908.00$22,661.00$18,234.00$18,011.00$21,137.00$19,018.00$20,781.00$19,979.00$19,975.00$20,336.00$16,980.00$239,267.00
62021$17,698.00$20,284.00$18,400.00$25,722.00$18,964.00$19,638.00$20,636.00$20,835.00$18,041.00$16,671.00$16,809.00$17,000.00$213,698.00
72022$21,000.00160002200022000
8YOY +/-$3,302.00($4,284.00)$3,600.00($3,722.00)        ($1,104.00)
9Growth %19%-21%20%-14%        -1%
Sheet1
Cell Formulas
RangeFormula
B8:M8B8=IF(B7<>"",SUM(B7-B6),"")
N8N8=(SUM((INDIRECT("$B$8:"&ADDRESS(8,SUMPRODUCT(MAX((B8:M8<>"")*COLUMN(B8:M8))))))))
B9:M9B9=IF(B$7<>"",((B$7-B$6)/B$6),"")
N9N9=((SUM(INDIRECT("$B$7:"&ADDRESS(7,SUMPRODUCT(MAX((B7:M7<>"")*COLUMN(B7:M7))))))-SUM(INDIRECT("$B$6:"&ADDRESS(6,SUMPRODUCT(MAX((B7:M7<>"")*COLUMN(B7:M7)))))))/SUM(INDIRECT("$B$6:"&ADDRESS(6,SUMPRODUCT(MAX((B7:M7<>"")*COLUMN(B7:M7)))))))
I appreciate your response. I'm not sure what I did that just posted your potential solution, but it happened. So It's close. I assume I will have to have hidden helper rows. But Essentially assume that the YOY and Growth wouldn't be visible except for the last column where it would display the YTD Year over year growth. So this would be the final format of the table. The formulas for B8 and and B9 are correct for "helper" rows. But I want the table to only display a dynamic amount.

YearJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberTotal
2017$ 100,354.00$ 94,903.00$ 96,792.00$ 111,590.00$ 108,106.00$ 109,953.00$ 110,093.00$ 114,243.00$ 113,164.00$ 111,684.00$ 111,686.00$ 108,334.00$ 1,290,902.00
2018$ 110,966.00$ 109,825.00$ 109,890.00$ 121,067.00$ 77,288.00$ 79,216.00$ 79,197.00$ 80,004.00$ 79,380.00$ 76,175.00$ 76,440.00$ 75,264.00$ 1,074,712.00
2019$ 76,061.00$ 72,563.00$ 73,683.00$ 78,953.00$ 76,939.00$ 81,592.00$ 81,743.00$ 82,171.00$ 82,582.00$ 79,146.00$ 79,441.00$ 77,478.00$ 942,352.00
2020$ 77,795.00$ 76,177.00$ 77,540.00$ 70,364.00$ 51,315.00$ 68,578.00$ 77,423.00$ 79,071.00$ 43,850.00$ 64,203.00$ 64,711.00$ 60,028.00$ 811,055.00
2021$ 60,039.00$ 62,607.00$ 58,484.00$ 69,639.00$ 69,442.00$ 72,665.00$ 70,946.00$ 73,060.00$ 71,266.00$ 68,725.00$ 70,438.00$ 747,311.00
2022
YOY +/-
Growth% +/-
 
Upvote 0
How about this then. Helper cells would probably make it easier to read but not needed.

Book1
ABCDEFGHIJKLMN
1YearJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberTotal
22017161862026219763173491609816590169301615111336203982030920199211571
32018163311656616281186451670618708186551781318833175291785818140212065
42019181401946122933229332058722003223092178522409199312123620223253950
52020202472190822661182341801121137190182078119979199752033616980239267
62021176982028418400257221896419638206362083518041166711680917000213698
7202221000160002200022000
8       YOY +/--1104
9       Growth% +/--1%
Sheet1
Cell Formulas
RangeFormula
F8:L8F8=IF(F7<>"",SUM(F7-F6),"")
F9:L9F9=IF(F$7<>"",((F$7-F$6)/F$6),"")
N8N8=(SUM((INDIRECT("$B$7:"&ADDRESS(7,SUMPRODUCT(MAX((B7:M7<>"")*COLUMN(B7:M7)))))),-(INDIRECT("$B$6:"&ADDRESS(6,SUMPRODUCT(MAX((B7:M7<>"")*COLUMN(B7:M7))))))))
N9N9=((SUM(INDIRECT("$B$7:"&ADDRESS(7,SUMPRODUCT(MAX((B7:M7<>"")*COLUMN(B7:M7))))))-SUM(INDIRECT("$B$6:"&ADDRESS(6,SUMPRODUCT(MAX((B7:M7<>"")*COLUMN(B7:M7)))))))/SUM(INDIRECT("$B$6:"&ADDRESS(6,SUMPRODUCT(MAX((B7:M7<>"")*COLUMN(B7:M7)))))))
 
Upvote 0
How about this then. Helper cells would probably make it easier to read but not needed.

Book1
ABCDEFGHIJKLMN
1YearJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberTotal
22017161862026219763173491609816590169301615111336203982030920199211571
32018163311656616281186451670618708186551781318833175291785818140212065
42019181401946122933229332058722003223092178522409199312123620223253950
52020202472190822661182341801121137190182078119979199752033616980239267
62021176982028418400257221896419638206362083518041166711680917000213698
7202221000160002200022000
8       YOY +/--1104
9       Growth% +/--1%
Sheet1
Cell Formulas
RangeFormula
F8:L8F8=IF(F7<>"",SUM(F7-F6),"")
F9:L9F9=IF(F$7<>"",((F$7-F$6)/F$6),"")
N8N8=(SUM((INDIRECT("$B$7:"&ADDRESS(7,SUMPRODUCT(MAX((B7:M7<>"")*COLUMN(B7:M7)))))),-(INDIRECT("$B$6:"&ADDRESS(6,SUMPRODUCT(MAX((B7:M7<>"")*COLUMN(B7:M7))))))))
N9N9=((SUM(INDIRECT("$B$7:"&ADDRESS(7,SUMPRODUCT(MAX((B7:M7<>"")*COLUMN(B7:M7))))))-SUM(INDIRECT("$B$6:"&ADDRESS(6,SUMPRODUCT(MAX((B7:M7<>"")*COLUMN(B7:M7)))))))/SUM(INDIRECT("$B$6:"&ADDRESS(6,SUMPRODUCT(MAX((B7:M7<>"")*COLUMN(B7:M7)))))))
So the Growth % formulas appears to work flawlessly. Thank you so much for you help on that issue. BUT for some reason, when I enter February the formula for N8 gives me a #value code. My formula is as follows:

=(SUM((INDIRECT("$B$9:"&ADDRESS(9,SUMPRODUCT(MAX((B9:M9<>"")*COLUMN(B9:M9)))))),-(INDIRECT("$B$8:"&ADDRESS(8,SUMPRODUCT(MAX((B9:M9<>"")*COLUMN(B9:M9))))))))

*Note B9=Your B7
B8= your B6
I had a few row I omitted from my posting to protect the data.
 
Upvote 0
BUT for some reason, when I enter February the formula for N8 gives me a #value code.

I'm not getting that error. I've added two additional rows so my data matches yours. Are your cells formatted as numbers or currency?

Book1
ABCDEFGHIJKLMN
1YearJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberTotal
2
3
42017$ 16,186$ 20,262$ 19,763$ 17,349$ 16,098$ 16,590$ 16,930$ 16,151$ 11,336$ 20,398$ 20,309$ 20,199$ 211,571
52018$ 16,331$ 16,566$ 16,281$ 18,645$ 16,706$ 18,708$ 18,655$ 17,813$ 18,833$ 17,529$ 17,858$ 18,140$ 212,065
62019$ 18,140$ 19,461$ 22,933$ 22,933$ 20,587$ 22,003$ 22,309$ 21,785$ 22,409$ 19,931$ 21,236$ 20,223$ 253,950
72020$ 20,247$ 21,908$ 22,661$ 18,234$ 18,011$ 21,137$ 19,018$ 20,781$ 19,979$ 19,975$ 20,336$ 16,980$ 239,267
82021$ 17,698$ 20,284$ 18,400$ 25,722$ 18,964$ 19,638$ 20,636$ 20,835$ 18,041$ 16,671$ 16,809$ 17,000$ 213,698
92022$ 21,000$ 18,500
10       YOY +/-$ 1,518
11       Growth% +/-4%
Sheet1
Cell Formulas
RangeFormula
F10:L10F10=IF(F9<>"",SUM(F9-F8),"")
F11:L11F11=IF(F$9<>"",((F$9-F$8)/F$8),"")
N10N10=(SUM((INDIRECT("$B$9:"&ADDRESS(9,SUMPRODUCT(MAX((B9:M9<>"")*COLUMN(B9:M9)))))),-(INDIRECT("$B$8:"&ADDRESS(8,SUMPRODUCT(MAX((B9:M9<>"")*COLUMN(B9:M9))))))))
N11N11=((SUM(INDIRECT("$B$9:"&ADDRESS(9,SUMPRODUCT(MAX((B9:M9<>"")*COLUMN(B9:M9))))))-SUM(INDIRECT("$B$8:"&ADDRESS(8,SUMPRODUCT(MAX((B9:M9<>"")*COLUMN(B9:M9)))))))/SUM(INDIRECT("$B$8:"&ADDRESS(8,SUMPRODUCT(MAX((B9:M9<>"")*COLUMN(B9:M9)))))))
 
Upvote 0
Solution
I'm not getting that error. I've added two additional rows so my data matches yours. Are your cells formatted as numbers or currency?

Book1
ABCDEFGHIJKLMN
1YearJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberTotal
2
3
42017$ 16,186$ 20,262$ 19,763$ 17,349$ 16,098$ 16,590$ 16,930$ 16,151$ 11,336$ 20,398$ 20,309$ 20,199$ 211,571
52018$ 16,331$ 16,566$ 16,281$ 18,645$ 16,706$ 18,708$ 18,655$ 17,813$ 18,833$ 17,529$ 17,858$ 18,140$ 212,065
62019$ 18,140$ 19,461$ 22,933$ 22,933$ 20,587$ 22,003$ 22,309$ 21,785$ 22,409$ 19,931$ 21,236$ 20,223$ 253,950
72020$ 20,247$ 21,908$ 22,661$ 18,234$ 18,011$ 21,137$ 19,018$ 20,781$ 19,979$ 19,975$ 20,336$ 16,980$ 239,267
82021$ 17,698$ 20,284$ 18,400$ 25,722$ 18,964$ 19,638$ 20,636$ 20,835$ 18,041$ 16,671$ 16,809$ 17,000$ 213,698
92022$ 21,000$ 18,500
10       YOY +/-$ 1,518
11       Growth% +/-4%
Sheet1
Cell Formulas
RangeFormula
F10:L10F10=IF(F9<>"",SUM(F9-F8),"")
F11:L11F11=IF(F$9<>"",((F$9-F$8)/F$8),"")
N10N10=(SUM((INDIRECT("$B$9:"&ADDRESS(9,SUMPRODUCT(MAX((B9:M9<>"")*COLUMN(B9:M9)))))),-(INDIRECT("$B$8:"&ADDRESS(8,SUMPRODUCT(MAX((B9:M9<>"")*COLUMN(B9:M9))))))))
N11N11=((SUM(INDIRECT("$B$9:"&ADDRESS(9,SUMPRODUCT(MAX((B9:M9<>"")*COLUMN(B9:M9))))))-SUM(INDIRECT("$B$8:"&ADDRESS(8,SUMPRODUCT(MAX((B9:M9<>"")*COLUMN(B9:M9)))))))/SUM(INDIRECT("$B$8:"&ADDRESS(8,SUMPRODUCT(MAX((B9:M9<>"")*COLUMN(B9:M9)))))))
I have the cells formatted as "accounting" so currency. I tried your adjusted formula with currency and numbers and I'm still getting the same result. When I evaluate the formula the steps of evaluation result in the following:
1st:
1642618169180.png

2nd:
1642618200204.png

3rd:
1642618223620.png

4th:
1642618255612.png

5th:
1642618285476.png


I'm not sure if this piece will help you or not, but I really don't know where it's getting goofy. And I don't know much about indirect. Thank you so so much for your continued help!
 
Upvote 0
It looks like there is a formatting issue in either cells B8 or C8 or both. Please verify they are a number value as well. Usually a #Value error is a difference in cell formats.
 
Upvote 0
I tried that, it didn't seem to shake the error. I tried filling cells after b and c to see if it would still calculate but I'm fairly certain there is a shutdown built into the formula that says if the cell is "false" it stops calculating. The only other logical reason for the error is that my work computer has been plotting against me for 2 years and this is the project that it has decided it will not be cooperative.
 
Upvote 0
Very simplified but what happens when you plug in this into N10?

Excel Formula:
=(SUM(B9:C9,-B8:C8))
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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