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?
<tbody>
</tbody>
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?
| Supplier 1 | Supplier 2 | Supplier 3 | Supplier 4 | Supplier 5 | Supplier 6 | Supplier 7 | Supplier 8 | Supplier 9 | Supplier 10 | Supplier 11 | |
| CREDIT | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 |
| MONTH | |||||||||||
| Jan-16 | £260.00 | £125.00 | £135.00 | £43.00 | £54.50 | £41.00 | £260.00 | £125.00 | £135.00 | £43.00 | £54.50 |
| Feb-16 | £270.00 | £130.00 | £140.00 | £44.00 | £55.50 | £42.00 | £270.00 | £130.00 | £140.00 | £44.00 | £55.50 |
| Mar-16 | £280.00 | £135.00 | £145.00 | £45.00 | £56.50 | £43.00 | £280.00 | £135.00 | £145.00 | £45.00 | £56.50 |
| Apr-16 | £290.00 | £140.00 | £150.00 | £46.00 | £57.50 | £44.00 | £290.00 | £140.00 | £150.00 | £46.00 | £57.50 |
| May-16 | £300.00 | £145.00 | £155.00 | £47.00 | £58.50 | £45.00 | £300.00 | £145.00 | £155.00 | £47.00 | £58.50 |
| Jun-16 | £310.00 | £150.00 | £160.00 | £48.00 | £59.50 | £46.00 | £310.00 | £150.00 | £160.00 | £48.00 | £59.50 |
| Jul-16 | £320.00 | £155.00 | £165.00 | £49.00 | £60.50 | £47.00 | £320.00 | £155.00 | £165.00 | £49.00 | £60.50 |
| Aug-16 | £330.00 | £160.00 | £170.00 | £50.00 | £61.50 | £48.00 | £330.00 | £160.00 | £170.00 | £50.00 | £61.50 |
| Sep-16 | £340.00 | £165.00 | £175.00 | £51.00 | £62.50 | £49.00 | £340.00 | £165.00 | £175.00 | £51.00 | £62.50 |
| Oct-16 | £350.00 | £170.00 | £180.00 | £52.00 | £63.50 | £50.00 | £350.00 | £170.00 | £180.00 | £52.00 | £63.50 |
| Nov-16 | £360.00 | £175.00 | £185.00 | £53.00 | £64.50 | £51.00 | £360.00 | £175.00 | £185.00 | £53.00 | £64.50 |
| Dec-16 | £370.00 | £180.00 | £190.00 | £54.00 | £65.50 | £52.00 | £370.00 | £180.00 | £190.00 | £54.00 | £65.50 |
| Jan-17 | £380.00 | £185.00 | £195.00 | £55.00 | £66.50 | £53.00 | £380.00 | £185.00 | £195.00 | £55.00 | £66.50 |
| Feb-17 | £390.00 | £190.00 | £200.00 | £56.00 | £67.50 | £54.00 | £390.00 | £190.00 | £200.00 | £56.00 | £67.50 |
| Mar-17 | £400.00 | £195.00 | £205.00 | £57.00 | £68.50 | £55.00 | £400.00 | £195.00 | £205.00 | £57.00 | £68.50 |
| Apr-17 | £410.00 | £200.00 | £210.00 | £58.00 | £69.50 | £56.00 | £410.00 | £200.00 | £210.00 | £58.00 | £69.50 |
| Jan to Dec (YTD) | £1,580.00 | £770.00 | £810.00 | £226.00 | £272.00 | £218.00 | £1,580.00 | £770.00 | £810.00 | £226.00 | £272.00 |
| Jan to Dec (Previous Year) | £3,780.00 | £1,830.00 | £1,950.00 | £582.00 | £720.00 | £558.00 | £3,780.00 | £1,830.00 | £1,950.00 | £582.00 | £720.00 |
<tbody>
</tbody>
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
