I’ve rewritten the question. I’ve attached a spreadsheet. Best way I think. Here’s how it is. Starts with all the rows just one after another. And a couple of simple calcs.
What’s happening is the left row shows the starting figure for each week. (period, week in this case) The next row shows the ending figure for each after taking into account what’s in the third row.
Next week the starting figure is (in this case) 250 + the ending figure for the last week minus the input.
And it keeps going like that.
Trouble comes when there’s multiple days with input during the week.
I insert rows when that happens and that breaks my simple calculation routine.
tinytwo.ods
According to my reading of your description, the 200 should have been 250. Otherwise I don’t get it.
In most cases, the “right way” is to attach a spreadsheet file. When the issue is more of a visual case (formatting/rendering, user interface/toolbars) a picture is required.
For your case the picture is most likely sufficient. Attaching the spreadsheet file appears less graphic on the page, but relieves helpers of creating the data set anew to test their suggestions. In this case it is done in a minute anyway, so no big deal.
You’re dead right. My mistake. It should be 250. What a blunder.
Okay, I’ll attach spreadsheets in future.
To calculate your sums I would usually use the middle column C to add the. value from D to the value of C one row higher, and reset to 0 if there is a sum to the left.
But I can’t see any rule, where the sums will be placed.
Manual placement?
J.
what happens is the ‘answer’ column (column B) where I want the formula is prefilled with a formula and each line is directly beneath the other.
the formula is like B8 = 200 + C7 - D8.
I stripped it down and changed it a bit to make it simpler and retain the problem.
Then over time rows get inserted. we can’t know ahead of time how many.
That breaks the formula. That’s the problem.
Now, after an insertion, the ‘D8’ portion has to be sum from the cell below the last D we used to the D cell in the same row as the formula.
So for this I think I’d say it has to be 'If the current cell <> “” then do the formula.
Whatever that formula is going to be.
Perhaps I should have made a real spreadsheet and attached it.
Sounds pretty much like a duplicate of https://ask.libreoffice.org/en/question/304006/how-can-i-sum-these-rows-when-needed/
does too. and i posted that. don’t remember it. i’ve deleted it.
Just a general advice: Redesign your sheet.
Place totals above. You then can fix their range to always be shown. (Don’t think paper!)
Never mix up formulas and directly entered content in the same ranges.
Never insert afdditional rows into ranges containing formulas. It would make a mess most likely, and you wouldn’t be able in most cases to rework formulas so you can fill them to other rows again.
Never ever make both mistakes in the same sheet.
If an entry needs to be processed in a specific way (to start a new week e.g.) use a dedicated column for the information.
Use the efficient built-in tools where available. In this case the Pivot Table (aka “DataPilot”) should be appropriate.
Let’s start with the fact that in your example, you manually enter only two values - the week number and the amount of receipts “input”. Formulas should do the rest.
Let’s change the entry form a little - instead of the week number, we will enter the exact date of receipt, let Calc calculate the week number himself.
In order not to jump across the entire sheet from cell to cell, place the data entered manually in the first two columns of the sheet and enter the first date and the first receipt $250.(By the way, did you know that the current date can be inserted into a cell using the Ctrl +; hotkey?)
Let’s move on to creating formulas. To prevent the manual input area and the calculated part from interfering with each other, move two columns to the right, to column E. The week number is simple: subtract the very first date from the date in the current row and divide the result by 7 days.
=IF(A2="";"";INT((A2-$A$2)/7)+1)
Now let’s calculate the weekly deductions. The topmost cell will be just the number 250, and as soon as the week number changes, we calculate the size like this
=IF(A3="";"";IF(E3=E2;"";E3*250-SUM($F$1:F2)))
The trick is that each time we calculate how much we should have given and subtract how much we have already given. This allows you to know the exact amount in case, for some reason, a week or several weeks was missed (pandemic, quarantine - anything can happen)
Now let’s create a Balance column - everything that was received minus everything that was given.
=IF(A2="";"";SUM($B$1:B2)-SUM($F$1:F2))
Stretch these formulas down far enough. Due to the fact that each calculation was wrapped in a function of the form =IF(A2="";"";...) cells for which the data has not yet been entered will remain empty.
This is day-by-day data, but you would like to see week-by-week grouped data
Move even further to the right and create formulas for calculating the amounts for the week. I hope you understand the principle and from this example - tinytwo_diff.ods - and you can understand meaning of the formulas on your own.
Yes, that’s very lovely. Thank you.