Can someone fix these formulas for me please? Probably and easy fix

English
#1

I made a Sheet for my “Monthly Budget” and I’m no good at formulas at all. Basic ones yes, some sheets I use for other things that I made out years ago for calculations and can copy and edit formulas from them when I create new sheets.

Using the “Wizard” is way beyond me learning.

I uploaded the sheet and put all the notes in the sheet what I need.
Should be pretty easy for you “Pros”.

You can see some of the formulas I was able to use from a template I found for a “Check Register” template that seem to work for Columns F,G and H

If anyone has anything better and simpler that would be great.

Thank you everyone …
Spending.ods (26.1 KB)

#2

:thinking:
then how could you come up with such a =IF(OR(G14 > 0, F14 > 0),OFFSET(H14, -1, 0)-G14+F14, IF(OR(OFFSET(G14, 1, 0) > 0, OFFSET(F14, 1, 0) > 0), OFFSET(H14, -1, 0), "")) ??

image
image1780×438 88.8 KB

#3

Like I said in the original post …
“You can see some of the formulas I was able to use from a template I found for a “Check Register” template that seem to work for Columns F,G and H”.

I can change row numbers and column numbers in Formulas If I find a working formula no problem.
Creating a formula from scratch, is another story

#5

The issue with your spreadsheet is the layout of data which makes it so complicated to get any information from data. Your table tries to mix multiple spendings per day with daily limits and monthly aggregations.
I split the input into a table of spendings and a table of limits.
I added an aggregated table of daily spendings, daily limits and daily balance.
Finally, I made a pivot table from the daily table and grouped it by months.
ask137130.ods (109.4 KB)

2 Likes
#6

The problem, As @fpy has detected is the formula in H14 wich refers another formula (h13) and this is the strange function for beginners

https://wiki.documentfoundation.org/Documentation/Calc_Functions/OFFSET

#4

just put a plain 0 in H13, and it will automagically work.

image

because these overkill OFFSET are just obfuscating the formulas for nothing.

e.g. OFFSET(H14, -1, 0) is just H13 :face_with_thermometer:

#7

It is, until you insert a new row 14 in the table. The OFFSET -1 from "here" always points to the previous row, while direct addressing keeps the cell anchoring (so an inserted row is skipped).

The problem arises when you add text (an empty string, but still datatype text) and numbers. Calc decides that text and numbers are incompatible (like adding minutes and feet), and returns an error.

This text content is triggered on the first empty row, and causes an error on the next row which receives an amount. The check register sheet you copied from probably avoided empty rows.

Solution
Use zero instead of “”.

So in H4:

  • Not: =IF(OR(G4 > 0, F4 > 0),OFFSET(H4, -1, 0)-G4+F4, IF(OR(OFFSET(G4, 1, 0) > 0, OFFSET(F4, 1, 0) > 0), OFFSET(H4, -1, 0), ""))
  • Rather: =IF(OR(G4 > 0, F4 > 0),OFFSET(H4, -1, 0)-G4+F4, IF(OR(OFFSET(G4, 1, 0) > 0, OFFSET(F4, 1, 0) > 0), OFFSET(H4, -1, 0), 0))

Copy down.

It appears that cells are formatted to show zero values as blank, so visually a space vs zero makes no difference.

A little aside: I thought it a bit weird with left aligned/centered numbers, but with small amounts I guess it makes little difference.

1 Like
#8

says who ?

#9

Say I.

But don’t take my word for it. Try it for yourself. One formula referencing previous row by direct addressing and one with an indirect reference, OFFSET - 1 from “this row”.

You can use this one. Insert a new row 5, so the old row 5 is pushed down to become row 6, and see what happens.IndirectionByOffset_demo.ods (8.6 KB)

#10

insert row below :expressionless:

#11

1st. question is: what’s the purpose of H13 =

IF(OR(G13 > 0; F13 > 0);OFFSET(H13; -1; 0)-G13+F13; IF(OR(OFFSET(G13; 1; 0) > 0; OFFSET(F13; 1; 0)  > 0); OFFSET(H13; -1; 0); ""))

:grey_question: :thinking:

#12

My interpretation:

  • If there is an entry in/out on this row, update the previous entry in running sum to reflect those changes.
  • Otherwise …
    • If there is an entry in/out in next row, copy previous running sum.
    • Otherwise (i.e. no entries in this or next row), restart summation (return a blank).

That blank returned is what causes error next time the formula tries to calculate, because it is an empty string (not truly a typeless blank/void) and as such not compatible with calculation.

#13

So H13 loooks like the balance carried forward from previous month, right?
A gap of 3 rows between month’s ends, so
H13 should be = H9
And no entries in F13G13 :thinking:

#14

Looks to me like it is intended to be more flexible than what you envision. I.e. “any” row used for “any” purpose (“any” within the context given, of course). Replacing automated behaviour with a workaround which “works in this case” will typically create a mess whenever the spreadsheet is reused.

Ultimately @dannyg is the asker, and should know best what behaviour is intended.

1 Like
#16

Thank You !

I messed around with it and somehow got this to work. I have to check your and see the difference.

I uploaded sample of a test sheet I messed with

At the start of a month all I have to do is change the "SUM number column from “G” to the line of the 1st day of the month in the row.

I get home later I’m going to look at the difference you posted for me.

And yes, I know. Some reason If I align left, center, and then indent I can see things easier.
Thank You
Working Document.ods (1.4 MB)

#17

WTF … 1.4MB for 13 rows of real Data

the same Data (except last Date in Column A changed from May to June)

Keep_it_small_and_simple_with_pivot.ods (22.4 KB)

#18

I know !
I hid all the other rows in the sheet and don’t know why it shows that big.

My real Sheet has 50,000 rows so it will work for years to come and that is 1.5 mb.

That’s another issue I been having and tried everything for months.
No matter how small a Sheet is they take forever to open even if it’s small like 50kb.

Known problem for some people and tried every possible workaround and nothing works.

Started with an update about 2 years ago

#19

Try deleting all the rows and columns beyond the last cell for data. The best way to delete any format on those cells.

#15

dannyg - I think keme1 has given you the cleanest solution here. The issue really is that "" (empty text) breaks the math when the formula tries to calculate. Changing it to 0 instead should fix the error, especially if your cells are formatted to hide zero values anyway.

For your monthly totals in Column I - if you only want a running total when Column G has a number entered, you could use something like:

=IF(G4>0, SUM($G$4:G4), "")

That way it only updates when you actually enter a spent amount, and you can copy it down. For restarting each month, you’d just need to adjust the range manually or set up a separate table per month.

The OFFSET stuff is clever but honestly overcomplicating things - direct references are usually easier to debug and maintain.

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.