Sales Comparison Help

[Daveom]

I am having trouble with one of my sheets to compare sales & transactions year over year. The formula I can not figure out is how to put one in so on each month as we move throughout the year it does a YTD comparison vs LYTD some were on the sheet that I have attached. Any suggestions would help.

As a scenario this years sales and transactions would go in column J & M and I would like to have a year to date comparison for year to date 2015 Columns E & G.

Any help would be great.

Thanks,

[JohnTopley]

Try

=(SUM($E$3:E3)-SUM($B$3:B3))/SUM($B$3:B3)

[Daveom]

That is what column K is calculating, what I am looking for is a cell that for example : when January, February and Marchs sales are entered it will compare to those same three months from last year.

[FDibbins]

If you want a YTD comparison, shou.dnt the formula progessively include months?
=(SUM(E$3:E3)-SUM(B$3:B3))/SUM(B$3:B3)
instead of just...
=(E3-B3)/B3

then...
K3=IF(J3="","",(SUM(J$3:J3)-SUM(E$3:E3))/SUM(E$3:E3))
L3=IF(J3="","",(SUM(J$3:J3)-SUM(B$3:B3))/SUM(B$3:B3))
N3=IF(M3="","",(SUM(M$3:M3)-SUM(G$3:G3))/SUM(G$3:G3))
O3=IF(M3="","",(SUM(M$3:M3)-SUM(C$3:C3))/SUM(C$3:C3))

[FDibbins]

That is what column K is calculating,

No, that - and the other formulas going down - are only doing a month-on-month test, not YTD

[JohnTopley]

... that's what my formula does: January, then January+February, ...

(as per Ford's post)

[Daveom]

I still need to have the formula as shown =(j3-e3)/e3 this will allow to see a month vs month comparison. I am looking for something to add so I can see one with a rolling YTD comparison that will recognize when data is enetered into a 2016 column then compare that column to the same previous data set from 2015, 2014. Does that make sense ?

[JohnTopley]

That's what the SUM formulae supplied do: try them!

[Daveom]

Which column should they be entered into

[JohnTopley]

My formula in F3 if that's is where you want the comparison.

Ford's reply gives some of the other options.

K3=IF(J3="","",(SUM(J$3:J3)-SUM(E$3:E3))/SUM(E$3:E3))
L3=IF(J3="","",(SUM(J$3:J3)-SUM(B$3:B3))/SUM(B$3:B3))
N3=IF(M3="","",(SUM(M$3:M3)-SUM(G$3:G3))/SUM(G$3:G3))
O3=IF(M3="","",(SUM(M$3:M3)-SUM(C$3:C3))/SUM(C$3:C3))

[Daveom]

Thanks that did work, I wasn't reading it correctly the first time. Thanks for the help , sorry for the confusion.