Sum values by date number

asked 2018-04-20 04:38:38 +0200

Cyberxine gravatar image

I have some financial data I'd like to process and graph. I have the transaction dates formatted as numbers, such that the first day in Quarter 2 is '1' and the last day will be '91'. The values for the transactions are in adjacent columns, and there are multiple accounts with databases formatted as such on the same sheet.

I have for each account a running balance, such that there are multiple values for the balance of the account in a day if multiple transactions occurred. I have learned how to graph these running balances, but I am having difficulty creating a formula using functions to create the most important curve in my graph, which is Net Worth (Acc. 1 + Acc. 2 - Acc. 3... and so on).

How do I create a function to render the data for graphing Net Worth?

image description

Here is an illustration of my planned graph, the entire purpose of the spreadsheets.

I could easily do it on paper, but that is wasteful and time-consuming to manually calculate and sum all of the values, and then plot them on paper of digitize the values and plot it with Calc thereafter.

In the end, when a TODAY function is called, I want it to check if a an earned, but unpaid/unreceived asset should have liquidated (pay-day) and to see if an unearned, scheduled, potential asset (a future workday) has passed (becoming a virtual or gaseous asset, rather than a nebulous one in my terms).

This will allow me to see the sum total of my finances at a glance, historically and in the near future for the purposes of large bill payments and savings deposits, as well as make projections for when I can expect larger lump sums if my budget is followed and discretionary expenses eliminated.

Any help is appreciated, Thanks in advance!

edit retag flag offensive close merge delete

Comments

1

How to represent dates by numbers is standardised. TODAY() and NOW() comply with the standard. Apply standards first.

Lupp gravatar imageLupp ( 2018-04-20 10:22:57 +0200 )edit

Thanks, I'll reformat the dates to conform with those functions so they can be implemented later.

Cyberxine gravatar imageCyberxine ( 2018-04-20 18:36:32 +0200 )edit

Does anyone know how I can generate the list of values needed for the Net Worth line? Even if I must use end-of-day numbers, without the transactions and balances throughout a day, that is fine.

Cyberxine gravatar imageCyberxine ( 2018-04-23 04:35:48 +0200 )edit

I made an attempt to suggest a solution, but I simply did not understand the question sufficiently.
What are "...multiple accounts with databases formatted as such on the same sheet."?
In what way should the "Net Worth" be determined?
How would you treat "...such that there are multiple values for the balance of the account in a day..."?
I'm afraid you will need another gambler understanding your intentions, and at the same time capable of designing a solution.

Lupp gravatar imageLupp ( 2018-04-23 12:30:31 +0200 )edit

I'll provide some more information tomorrow when I have time. I'll include sanitized data and screenshots so commenters can understand my intentions better. Thanks for attempting a solution.

Cyberxine gravatar imageCyberxine ( 2018-04-23 19:33:27 +0200 )edit

Please provide
-1- clear explanations
-2- real spreadsheets containinjg examples
-3- screenshots only if there is a specific question about the rendering, the view, or about prints.
I cannot work on such things in an exclusively abstract way, and to create real examples based on screenshots is an excessive waste of time.

Lupp gravatar imageLupp ( 2018-04-23 20:34:53 +0200 )edit