Apache OpenOffice Community Forum

I have a spreadsheet where I want to know the balance after entering the daily profit and loss in trading.
A column has date, B column has the title Profit, C column has the title Loss, D column has the Balance
I enter the formula in D4 as =IF('Profit';D3+B4;D3-C4) but it returns an error while it works with my bank sheets where the Profit and loss are replaced with Credit and Debit. Am I missing anything?

Edit: Changed subject, was Problems with IF function in Calc  Make your post understandable by others  -- MrProgrammer, forum moderator

You can get the behavior you want by going to Tools -> Options, expanding the Calc list on the left, selecting Calculate, and selecting "Automatically find column and row labels".
If the cells that look empty in your image are really empty, you could use the formula

FJCC wrote: ↑Fri Jul 12, 2024 3:03 pm You can get the behavior you want by going to Tools -> Options, expanding the Calc list on the left, selecting Calculate, and selecting "Automatically find column and row labels".
If the cells that look empty in your image are really empty, you could use the formula

Code: Select all

=D2+B3-C3

Thank you very much for the reply and help. I just checked and the box for automatically find the column and row labels was ticked on.
Hence I used the formula and it is what I wanted.

FJCC wrote: ↑Fri Jul 12, 2024 3:03 pm You can get the behavior you want by going to Tools -> Options, expanding the Calc list on the left, selecting Calculate, and selecting "Automatically find column and row labels".
If the cells that look empty in your image are really empty, you could use the formula

Code: Select all

=D2+B3-C3

Finally solved it in another way. Instead of writing profit in the IF function I wrote Loss and made necessary swap in the calculation of cells. It worked. I have enclosed the screen shot where the D column has the formula suggested by you and E column has the IF function.

The workaround is fine, but you may still want to use the column headers instead, for formula legibility.

The formula error suggests that the field name (column header) is wrong. The usual cause is a trailing space.
If you correct the column header, you may need to enter the formula again to establish the column referencing.

Thank you. Shall check it out.
Yes there was a trailing space which caused the confusion

D2+B3-C3 is IMHO the leanest method. Why bother with formulas when logic and basic calculation does the job?
It also reduces CPU consumption and file size.

Hagar Delest wrote: ↑Sat Jul 13, 2024 3:17 pm D2+B3-C3 is IMHO the leanest method. Why bother with formulas when logic and basic calculation does the job?
It also reduces CPU consumption and file size.

Very true. Thank you.