Draw a table of lines in the balance line of my account

Question

I made a table with my income and cash payments. And I list the account balance. Now I want to draw an account balance in a line chart. But the problem is that there is no value for every day. Thus, the line between the inputs is different, because the time between the two entries is different. Sometimes three days, sometimes 22 days ...

How can I understand that the string is constant after the value, and the string only changes on the day with the new value?

Edit (from comment) : I am using Excel 2007

Answer 1

Like everyone else, to get the real time difference between your points, you need to use a scatter plot instead of an Excel row plot.

In order to display the true state of your balance between dates (that is, without oblique lines), you will need to do some conversion of your data. This means creating two new data columns with duplicate points for each balance: one on the date when the account balance reached this level, and one on the balance date left this level.

I suggest the following: To fill in the “Balance” column, in the first cell (I start my table in E2), enter

=B2 

where B2 is the address of the first balance in the source table. In the next cell, enter

 =IF(E2<>E1,E2,OFFSET(B$2,COUNT(E$2:E2)/2,0)) 

Fill out this formula as much as you need.

Then in the first cell of the Date column of your new table (I start with D2) type

 =A2 

where A2 is the first date in your source table. In the next cell, enter

 =IF(E3=E2,OFFSET(A$2,COUNT(E$2:E3)/2,0),D2) 

Fill out this formula. Then use this new table as input for the scatter plot. Here's a sample screen capture:

Answer 2

Do you use an XY-Scatter chart. A line chart automatically adds on missing days. Make sure the x axis is formatted as date.

If you insist on using the XY-Scatter chart, you can insert days for each day, if there is no data on that day, then =NA() is placed for the y axis.

Answer 3

I think the chart you need is a table of steps based on your description. The answer from @Excelll above already indicated a way to do this in Excel. But here I have another solution that may not manually add some data to your data. You can use the Funfun Excel add-in to create a step chart. Here is an example that I am drawing.

As you can see, Excel has code. The Funfun Excel add-in allows you to use JavaScript code directly in Excel, so you can use powerful libraries like HighCharts.js or D3.js to draw diagrams that can only be complex in Excel. In this example, I used HighCharts.js. And it’s very easy to build a chart, since HighCharts.js itself supports a line of steps, so all you need to do is add a step parameter to your code, for example, the code below.

 series: [{ name: "Balance", data: balance, step: true, showInLegend: false }] 

Another advantage of the chart that I showed you compared to another solution is that you get the real time interval along the x axis. As you mentioned, you cannot register your balance daily, the x axis in the above example shows an irregular time interval .

Funfun also has an online editor where you can learn your JavaScript code and result. You can check the details, as I made an example chart in the link below.

https://www.funfun.io/1/#/edit/5a4e478f1010eb73fe125cb2

Once you are satisfied with the result achieved in the online editor, you can easily load the result into your Excel using the above URL. But, of course, first you need to add the Funfun add-in to your Excel using Insert - Office Add-Ins. Here are some screenshots showing how you load an example in Excel.

Disclosure: I'm a Funfun Developer

Answer 4

Excel is really rubbish when compiling this type of thing. In my experience, the best way to make a diagram is to start with an XY graph rather than a line graph, and choose the option where it connects the points to the line.

You may also need to add a new column indicating “days since launch” and use it as the X axis instead of the date — I cannot remember if Excel 2007 works correctly with dates. Try and see.

Finally, if you include any cells in the chart that are not yet filled (for example, because you want to make the chart from a whole column, although you only have a few records so far), you need to have empty cells filled in "# N / A "otherwise it will display them as 0.