Tracking line graph

[kentleywiltshire4]

Hello!
I'm in the process of creating an excel sheet which has a list of savings we've made. On another tab I want to make a line graph which shows how we are aligning to where we need to be to hit target. 

A problem I ran into was how to get excel to recognise how much was saved on each day.

I realise that I could get around this by entering the date for when a saving was entered, but the issue is that the amount we've saved on a product based on how many we've sold to customers and is a live figure. Therefore the amount saved for each product will be constantly changing.

What I need is a way to make excel single out each day and work out how much was saved on each day (or added on top of total amount).

Hope this makes sense. Many thanks in advance.

[#2]

Hi @kentleywiltshire4 ,

Could you please send a sample of you file? You can populate it with fake data. I'm just trying to find a solution for it, but I will have to check how you organize your entries. 

[#3]

@kentleywiltshire4 

untested...

Without having more detailed information available, I could imagine that one way to track the daily savings in your Excel sheet is to use a PivotTable.

A PivotTable can summarize your data by date and calculate the total savings for each day.

Here’s how you can do this:

1. Make sure your data is organized in a table with columns for Date and Savings.
2. Click on any cell in your data table and go to the “Insert” tab in the ribbon. Click on the “PivotTable” button.
3. In the “Create PivotTable” dialog box, make sure the “Select a table or range” option is selected and that the “Table/Range” field shows the correct range of cells for your data table. Choose where you want to place the PivotTable and click “OK”.
4. In the PivotTable Field List, drag the “Date” field to the Rows area and the “Savings” field to the Values area. By default, Excel will sum the values in the Savings column for each date.
5. You can now see the total savings for each day in the PivotTable.

You can then use this PivotTable as the source data for your line chart to show how your savings are aligning with your target over time.

Maybe this helps

[#4]

@rzanetisee below link.

https://1drv.ms/x/s!Avxy_97FdIruhwsiVhb1oIJXBTTU?e=uC6GhA

Thanks

[#5]

Thanks @NikolinoDE will try this as well.

[#6]

@kentleywiltshire4 ,

Just to confirm: you want the line chart to present the cumulative totals from each day? So if you got a total savings of 100 in Jan 31st and a total savings of 50 in Feb 2nd, you want the line chart to present Feb 2nd as 150, correct?

If yes, you first have to transform your savings column in numeric data (right now it is as text, at least in the file that you sent) and summarize the entries by day. You can do this by using a pivot table or the UNIQUE function connected to a SUMIF (example of the attached file).

Please, confirm if my assumptions are correct and let me know if the file that I sent you is in the right direction for solve your problem. 

[#7]

@rzaneti 

Yes correct, cumulative is the idea.

Your attachment is similar to what I need but not quite. See below example pic of what I had in mind...

This would be simpler if all data was fixed. But instead data for each row will be moving every day. I was thinking if excel was able to snapshot how much has added onto the total each day at the end of the day that would give me a daily figure.

I'll try your suggestions and try and make it work. Thanks

[#8]

@kentleywiltshire4 

I'm not sure, but I believe that Excel is not able to snapshot any data and register only the changes. Considering the file that your shared, I'm afraid that it will be very difficult to catch these changes with the current Sheet structure. 

If you have the option to modify the workbook structure, maybe it will be helpful to catch these changes.