Year to over year sales?

[Emerlin]

I am comparing sales from last year to this year and want to compute a YTD run rate or percentage that matches last year...

So march 2010 to march 2011..... AND a YTD run rate. These are currently in a sheet but are not in consecutive rows or columns. so

month 1 _______month 2__________________YTD Total
sales margin___sales margin________________$$$$ 92% (run rate the compares last year)

etc...

I am not sure if a pivot table would work - I have never used one. Also some months have zero dollars in them.

any ideas?

 

[Emerlin]

Anyone?

 

[Emerlin]

Does this help

The only difference is my data is not in consecutive rows or columns. Trying to solve the yellow cell that compares a YTD% for the same months - through July.

 

[Emerlin]

Anyone .... please?

 

[shg]

Try =SUM(C2:C13)/SUM(B2:INDEX(B2:B13, COUNT(C2:C13))) - 1

 

[Emerlin]

is there any way make this work when the data is not in consecutive cells?

 

[shg]

It would be messy. I would use some additional formuls to get it arranged just as you have it.

 

[Emerlin]

So as a best practice I should use sheets or areas with raw data then create a "report" to get to the data I need?

my current sheet I am trying to work with is very different - meaning I have sales and margin mixed in so the array formulas do not work.

I wonder if I should start over...

Thank you BTW the formula works perfectly - I will try to reverse engineer it to understand why

 

[shg]

It's pretty simple; it adds YTD sales and divides by the sum of the same number of months of last year's.

 

[Emerlin]

Thanks for helping me with this....

So as a best practice I should use sheets or areas with raw data then create a "report" to get to the data I need?

Thoughts on this?