Excel 2007 - Comparison of monthly sales year on year

Hi,

Can anyone help please?

I have a spreadsheet that shows sales figures from 2011/12 and 2012/13. What I need to do is to keep a running total of whether the total sales YTD are better or worse than the figures at this time last year. For example, four monthly sales figures for 2012/13 to date are:
1897, 2079,1395 and 662.
I am inputting these figures at the end of every month.

I also have all the monthly sales figures for the whole of last year but the only four I am interested in is the four from the corresponding months last year which are:
1478, 3601, 3937 and 1923

I would also like it that when I input the next months figures it automatically calculates the increase / decrease of total sales from the corresponding 5 months of last year.

I hope this makes sense - I have attached a copy of the spreadsheet to try and make it a bit easier to understand. It is Column R I need a formula for.

Thanks in advance

EXAMPLE.xls

Try, in N7 dragged down

=SUM($B8:INDEX($B8:$M8,MATCH(999.9*10^300,$B7:$M7)))
Does that work for you?

Okay, for comparison, what do you want (This year-Last Year to date)/Last year to date as a percentage or just
This year - last year?

Based on the fact that R is formatted as percentage, In R7
=IF(Q7="N/A","N/A",(SUM(B7:M7)-SUM($B8:INDEX($B8:$M8,MATCH(999.9*10^300,$B7:$M7))))/SUM($B8:INDEX($B8:$M8,MATCH(999.9*10^300,$B7:$M7))))

Check: for rows 7/8 2012/13 = 6033, Same months in 2011/12 is 10,939 (6033-10939)/10939 *100 = -44.85%

See attached.
You might want to use SUM rather than B7+c7+d7......

Hello ChemistB,

I am in a similar situation where I need to compare the YTD sales & booking of current year to last year by the current month (if I were to input sales & booking #s for Aug 2014, it will automatically compare # of Aug 2013). Hope this makes sense. I've tried the way you have it above however my data is slightly different so I couldn't get the results I needed. Hope you can work your magic on this one as well

Thanks in advance!