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
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