Percentage increase or decrease on comparision to same time last year.

J

JamesCES

New Member
Joined
Dec 22, 2016
Messages
15
Hi Guys,

Could you possibly help with the following please?

Can I do a comparison to the same time period last year?
So if the sheet is calculating the spend per supplier between Jan 17 to April 17, i like it to calculate the same time period a year previous i.e. Jan 16 to April 16.
If i load the sheet up in September it will compare Jan 17 - Sept 17 with Jan 16 - Sept 16.
Once i have this information i would like to show it as a percentage increase or decrease.
Is it possible to do this as one formula?[TABLE="width: 1152"]
<tbody>[TR]
[TD][/TD]
[TD]Supplier 1[/TD]
[TD]Supplier 2[/TD]
[TD]Supplier 3[/TD]
[TD]Supplier 4[/TD]
[TD]Supplier 5[/TD]
[TD]Supplier 6[/TD]
[TD]Supplier 7[/TD]
[TD]Supplier 8[/TD]
[TD]Supplier 9[/TD]
[TD]Supplier 10[/TD]
[TD]Supplier 11[/TD]
[/TR]
[TR]
[TD]CREDIT[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MONTH[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan-16[/TD]
[TD]£260.00[/TD]
[TD]£125.00[/TD]
[TD]£135.00[/TD]
[TD]£43.00[/TD]
[TD]£54.50[/TD]
[TD]£41.00[/TD]
[TD]£260.00[/TD]
[TD]£125.00[/TD]
[TD]£135.00[/TD]
[TD]£43.00[/TD]
[TD]£54.50[/TD]
[/TR]
[TR]
[TD]Feb-16[/TD]
[TD]£270.00[/TD]
[TD]£130.00[/TD]
[TD]£140.00[/TD]
[TD]£44.00[/TD]
[TD]£55.50[/TD]
[TD]£42.00[/TD]
[TD]£270.00[/TD]
[TD]£130.00[/TD]
[TD]£140.00[/TD]
[TD]£44.00[/TD]
[TD]£55.50[/TD]
[/TR]
[TR]
[TD]Mar-16[/TD]
[TD]£280.00[/TD]
[TD]£135.00[/TD]
[TD]£145.00[/TD]
[TD]£45.00[/TD]
[TD]£56.50[/TD]
[TD]£43.00[/TD]
[TD]£280.00[/TD]
[TD]£135.00[/TD]
[TD]£145.00[/TD]
[TD]£45.00[/TD]
[TD]£56.50[/TD]
[/TR]
[TR]
[TD]Apr-16[/TD]
[TD]£290.00[/TD]
[TD]£140.00[/TD]
[TD]£150.00[/TD]
[TD]£46.00[/TD]
[TD]£57.50[/TD]
[TD]£44.00[/TD]
[TD]£290.00[/TD]
[TD]£140.00[/TD]
[TD]£150.00[/TD]
[TD]£46.00[/TD]
[TD]£57.50[/TD]
[/TR]
[TR]
[TD]May-16[/TD]
[TD]£300.00[/TD]
[TD]£145.00[/TD]
[TD]£155.00[/TD]
[TD]£47.00[/TD]
[TD]£58.50[/TD]
[TD]£45.00[/TD]
[TD]£300.00[/TD]
[TD]£145.00[/TD]
[TD]£155.00[/TD]
[TD]£47.00[/TD]
[TD]£58.50[/TD]
[/TR]
[TR]
[TD]Jun-16[/TD]
[TD]£310.00[/TD]
[TD]£150.00[/TD]
[TD]£160.00[/TD]
[TD]£48.00[/TD]
[TD]£59.50[/TD]
[TD]£46.00[/TD]
[TD]£310.00[/TD]
[TD]£150.00[/TD]
[TD]£160.00[/TD]
[TD]£48.00[/TD]
[TD]£59.50[/TD]
[/TR]
[TR]
[TD]Jul-16[/TD]
[TD]£320.00[/TD]
[TD]£155.00[/TD]
[TD]£165.00[/TD]
[TD]£49.00[/TD]
[TD]£60.50[/TD]
[TD]£47.00[/TD]
[TD]£320.00[/TD]
[TD]£155.00[/TD]
[TD]£165.00[/TD]
[TD]£49.00[/TD]
[TD]£60.50[/TD]
[/TR]
[TR]
[TD]Aug-16[/TD]
[TD]£330.00[/TD]
[TD]£160.00[/TD]
[TD]£170.00[/TD]
[TD]£50.00[/TD]
[TD]£61.50[/TD]
[TD]£48.00[/TD]
[TD]£330.00[/TD]
[TD]£160.00[/TD]
[TD]£170.00[/TD]
[TD]£50.00[/TD]
[TD]£61.50[/TD]
[/TR]
[TR]
[TD]Sep-16[/TD]
[TD]£340.00[/TD]
[TD]£165.00[/TD]
[TD]£175.00[/TD]
[TD]£51.00[/TD]
[TD]£62.50[/TD]
[TD]£49.00[/TD]
[TD]£340.00[/TD]
[TD]£165.00[/TD]
[TD]£175.00[/TD]
[TD]£51.00[/TD]
[TD]£62.50[/TD]
[/TR]
[TR]
[TD]Oct-16[/TD]
[TD]£350.00[/TD]
[TD]£170.00[/TD]
[TD]£180.00[/TD]
[TD]£52.00[/TD]
[TD]£63.50[/TD]
[TD]£50.00[/TD]
[TD]£350.00[/TD]
[TD]£170.00[/TD]
[TD]£180.00[/TD]
[TD]£52.00[/TD]
[TD]£63.50[/TD]
[/TR]
[TR]
[TD]Nov-16[/TD]
[TD]£360.00[/TD]
[TD]£175.00[/TD]
[TD]£185.00[/TD]
[TD]£53.00[/TD]
[TD]£64.50[/TD]
[TD]£51.00[/TD]
[TD]£360.00[/TD]
[TD]£175.00[/TD]
[TD]£185.00[/TD]
[TD]£53.00[/TD]
[TD]£64.50[/TD]
[/TR]
[TR]
[TD]Dec-16[/TD]
[TD]£370.00[/TD]
[TD]£180.00[/TD]
[TD]£190.00[/TD]
[TD]£54.00[/TD]
[TD]£65.50[/TD]
[TD]£52.00[/TD]
[TD]£370.00[/TD]
[TD]£180.00[/TD]
[TD]£190.00[/TD]
[TD]£54.00[/TD]
[TD]£65.50[/TD]
[/TR]
[TR]
[TD]Jan-17[/TD]
[TD]£380.00[/TD]
[TD]£185.00[/TD]
[TD]£195.00[/TD]
[TD]£55.00[/TD]
[TD]£66.50[/TD]
[TD]£53.00[/TD]
[TD]£380.00[/TD]
[TD]£185.00[/TD]
[TD]£195.00[/TD]
[TD]£55.00[/TD]
[TD]£66.50[/TD]
[/TR]
[TR]
[TD]Feb-17[/TD]
[TD]£390.00[/TD]
[TD]£190.00[/TD]
[TD]£200.00[/TD]
[TD]£56.00[/TD]
[TD]£67.50[/TD]
[TD]£54.00[/TD]
[TD]£390.00[/TD]
[TD]£190.00[/TD]
[TD]£200.00[/TD]
[TD]£56.00[/TD]
[TD]£67.50[/TD]
[/TR]
[TR]
[TD]Mar-17[/TD]
[TD]£400.00[/TD]
[TD]£195.00[/TD]
[TD]£205.00[/TD]
[TD]£57.00[/TD]
[TD]£68.50[/TD]
[TD]£55.00[/TD]
[TD]£400.00[/TD]
[TD]£195.00[/TD]
[TD]£205.00[/TD]
[TD]£57.00[/TD]
[TD]£68.50[/TD]
[/TR]
[TR]
[TD]Apr-17[/TD]
[TD]£410.00[/TD]
[TD]£200.00[/TD]
[TD]£210.00[/TD]
[TD]£58.00[/TD]
[TD]£69.50[/TD]
[TD]£56.00[/TD]
[TD]£410.00[/TD]
[TD]£200.00[/TD]
[TD]£210.00[/TD]
[TD]£58.00[/TD]
[TD]£69.50[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan to Dec (YTD)[/TD]
[TD]£1,580.00[/TD]
[TD]£770.00[/TD]
[TD]£810.00[/TD]
[TD]£226.00[/TD]
[TD]£272.00[/TD]
[TD]£218.00[/TD]
[TD]£1,580.00[/TD]
[TD]£770.00[/TD]
[TD]£810.00[/TD]
[TD]£226.00[/TD]
[TD]£272.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan to Dec (Previous Year)[/TD]
[TD]£3,780.00[/TD]
[TD]£1,830.00[/TD]
[TD]£1,950.00[/TD]
[TD]£582.00[/TD]
[TD]£720.00[/TD]
[TD]£558.00[/TD]
[TD]£3,780.00[/TD]
[TD]£1,830.00[/TD]
[TD]£1,950.00[/TD]
[TD]£582.00[/TD]
[TD]£720.00[/TD]
[/TR]
</tbody>[/TABLE]


Code used for each suppliers year to date figure
=SUMPRODUCT((YEAR($B$7:$B$43)=YEAR(TODAY()))*$C$7:$C$43)

Code used for suppliers previous year
=SUMPRODUCT((YEAR($B$7:$B$43)=YEAR(TODAY())-1)*$C$7:$C$43)

Thanks
 
You must log in or register to reply here.

Similar threads

M
  • Solved
combine data in listbox on userform based on another listbox
Replies
3
Views
109
Maklil
M
M
  • Solved
Add item in first digits with create space after it based on sheet name
Replies
8
Views
171
kvsrinivasamurthy
K
H
  • Solved
Collection and calculation columns on userform across sheets
Replies
5
Views
84
Hasson
H
H
  • Solved
Getting the last price for each duplicates ID
Replies
2
Views
185
Hasson
H
M
  • Solved
Merge data on userform for each sheet based on multiple columns.
2
Replies
15
Views
256
Maklil
M

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