Cumulative Line Graph

[simonj1980]

I am trying to create a line graph that charts the ups and downs of a weekly set of results.

I have attached a set of dummy results, but imagine the weeks could co up to 52.

So what I want on sheet 1 is a line graph that shows a cumulative running total. But I want it to split the data up by weeks and only when column D on sheet 2 equals EPL.

Example

Week 1(sheet 2) has 11 EPL results with a total value of 45.99. Week 2 has 18 EPL results with a total value of -14.29

So week 1 would be on the bottom axis and would have a value of £45.99 and then week 2 would be the next point and this value would be £31.70 (45.99 – 31.70) and it would continue like this week after week.

Is this possible to do?

[Pauleyb]

You should create a separate table to hold you cumulative values, then use that for your graph.

Here is what I did:
First, I needed to 'fix' your Sheet2 table since row 352 was missing a week. I assumed it was 24 in cell A352.
In sheet 1, starting in cell A38 I put in the week I wanted to track, so I input a 1. Then, the rows below that increment up to 52 (so, A39 had a 2, A40 had a 3, etc.)
In sheet 1, at cell B38 I used this formula:
=SUMPRODUCT('sheet 2'!$F$4:$F$352,--('sheet 2'!$A$4:$A$352<='sheet 1'!A38),--('sheet 2'!$D$4:$D$352="EPL"))

I then dragged that formula down column B to my last week.

Note: I calculated 45.99 for Week 1, but 36.70 for Week 2. I see Week 2 has 19 EPL results, not 18.

Note, if you have a later version of Excel, you would use SUMIFS

[Spencer101]

Not sure if this is what you envisaged, but it's my take on your original post.

Let me know if you have any questions or would like more help with it.

I agree with Pauley though in the sense that the figures you quote in your post are not exactly what the data gives.

[simonj1980]

thanks Pauleyb, that was the best option and works perfectly.