Change cumulative value (running total) to actual value

[MadHatsJess]

Hi

I have cumulative sales data. Is there a quick way to change to value?

A1 = Week 1
A2 = Week 2
A3 = Week 4

B1 = 10
B2 - 12
B3 = 14

In C I need

C1 - 10
C2 - 2
C3 - 2

Thanks

 

[Eric W]

In C1 put 10. In C2 put

=B2-B1

and copy down.

 

[MadHatsJess]

Thanks Eric

This is what I have done but I have so much data that its too hard to keep up. I have 70 different products and the data extract is daily in product order

 

[Eric W]

In that case I don't understand what you want. Try to provide a more realistic example of your data, and the expected results. Do you expect the list to be sorted? Are you looking for formulas, or a macro to sort and total the numbers for you?

 

[MadHatsJess]

Thanks for the reply,

Just a formula will do it

What I have

Column A
Product name

Column B
Weekly dates (over three sales years Nov - Sep)

Column C
Cumulative Sale number


I sorted the data by product then date and then applied the sum.

That worked perfect and I was able to produce the pivot.

The problem is that the report I run is daily and it spits out all products with that days sales (total)

To keep up the document every day I would have to paste, sort and then drag each formula down 70 times.

In Column D I would like a formula that would reset at a new item OR sales year.

Hope this is more clear :D

 

[Eric W]

OK, ideally you have headings in row 1. Either insert a line and put some headings, or just start pasting your data at row 2. Now you still have to sort your table as before, but you can put this formula in D2:

=IF(A1<>A2,C2,C2-C1)

and copy down. This resets the totals whenever the product changes. We can add a check for the sales year too, but I don't see where October goes?

 

[MadHatsJess]

That's great thank you for the help.

We close in October for a month so there will be no sales data for that month

Jess

 

[Eric W]

OK, try this:

=IF(A1<>A2,C2,IF(YEAR(EOMONTH(B1,-9))<>YEAR(EOMONTH(B2,-9)),C2,C2-C1))