I have a spreadsheet to follow my banking. It lists in
consecutive columns:
A. Notes(such as cheque no.), B. Date of transaction, C. Description
(name), D. Debit amount, E. credit amount, F. running total.
I have always used this in the running total cell: =IF(OR(ISNUMBER(D19),ISNUMBER(E19)),F18-D19+E19,"")
where Column 19 is the current column, which changes as appropriate.
If
there is a debit in D19, or a credit in E19, then the new value for
F19, the new total, is calculated, otherwise a blank is shown.
Recently, there has been an error appearing. Instead of the correct new value, I am seeing '#VALUE' or '#NAME' appearing. I cannot see what is
wrong.
It is not changed if I remove any Conditional formatting.
Sometimes it cures itself, other times it refuses to be fixed.
The last time, I had to start a new spreadsheet.
Any ideas?
On 2025-05-21 10:19, Davey wrote:
I have a spreadsheet to follow my banking. It lists in
consecutive columns:
A. Notes(such as cheque no.), B. Date of transaction, C. Description
(name), D. Debit amount, E. credit amount, F. running total.
I have always used this in the running total cell:
=IF(OR(ISNUMBER(D19),ISNUMBER(E19)),F18-D19+E19,"")
where Column 19 is the current column, which changes as appropriate.
ITYM row?
If
there is a debit in D19, or a credit in E19, then the new value for
F19, the new total, is calculated, otherwise a blank is shown.
Recently, there has been an error appearing. Instead of the correct new
value, I am seeing '#VALUE' or '#NAME' appearing. I cannot see what is
wrong.
It is not changed if I remove any Conditional formatting.
Sometimes it cures itself, other times it refuses to be fixed.
The last time, I had to start a new spreadsheet.
Any ideas?
I'm guessing a little here and not testing beforehand what I'm writing,
but I note that the conditionals only test whether either Dnn or Enn has
a number, yet the formula applied assumes in all cases that both are numbers. I think that perhaps you may need to cover the four
possibilities explicitly:
Neither are numbers ""
Only Dnn is a number F(nn-1) - Dnn
Only Enn is a number F(nn-1) + Enn
Both are numbers F(nn-1) - Dnn + Enn
Perhaps this would be easiest by explicitly defaulting non-numbers in D
or E to 0 in the calculating part of the formula? For example ...
IF(ISNUMBER(D19),D19,0)
I repeat, I haven't tried this, but that's the direction I'd be experimenting in.
On 2025-05-21 13:04, Java Jive wrote:=A0 F(nn-1) - Dnn + Enn
On 2025-05-21 10:19, Davey wrote: =20
=20
I have a spreadsheet to follow my banking. It lists in
consecutive columns:
A. Notes(such as cheque no.), B. Date of transaction, C.
Description (name), D. Debit amount, E. credit amount, F. running
total. I have always used this in the running total cell:
=3DIF(OR(ISNUMBER(D19),ISNUMBER(E19)),F18-D19+E19,"")
where Column 19 is the current column, which changes as
appropriate. =20
ITYM row?
=20
If=20
there is a debit in D19, or a credit in E19, then the new value for
F19, the new total, is calculated, otherwise a blank is shown.
Recently, there has been an error appearing. Instead of the
correct new value, I am seeing '#VALUE' or '#NAME' appearing. I
cannot see what is wrong.
It is not changed if I remove any Conditional formatting.
Sometimes it cures itself, other times it refuses to be fixed.
The last time, I had to start a new spreadsheet.
Any ideas? =20
I'm guessing a little here and not testing beforehand what I'm
writing, but I note that the conditionals only test whether either
Dnn or Enn has a number, yet the formula applied assumes in all
cases that both are numbers.=C2=A0 I think that perhaps you may need to cover the four possibilities explicitly:
=20
=C2=A0 Neither are numbers=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ""
=C2=A0 Only Dnn is a number=C2=A0=C2=A0=C2=A0=C2=A0 F(nn-1) - Dnn
=C2=A0 Only Enn is a number=C2=A0=C2=A0=C2=A0=C2=A0 F(nn-1) + Enn
=C2=A0 Both are numbers=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=20=20
Perhaps this would be easiest by explicitly defaulting non-numbers
in D or E to 0 in the calculating part of the formula?=C2=A0 For example ...
=20
=C2=A0=C2=A0=C2=A0 IF(ISNUMBER(D19),D19,0)
=20
I repeat, I haven't tried this, but that's the direction I'd be=20 experimenting in. =20
A further possible problem with the formula stated in your OP: if=20 sometimes the result is "", then without knowing anything more about=20
your spreadsheet, reasonably it might supposed on the next row
F(nn-1) might not be a number either, leading to another possible
cause of the errors that you're getting.
=20
To speak more generally, there has long been recognition in data=20 programming circles that there is a need for separation between
various aspects of the subject ...
=20
D Data - Ensuring the accuracy and integrity of the data itself
V View - Displaying the data in a manner useful to a human
viewer
=20
... and a third, variously known and described in the three or four=20
letter acronyms with which the area abounds, but, for example ...
=20
M Model - Simplifying possibly over much for some, how the data
is manipulated and processed.
=20
Further, and quite possibly better, explanations would doubtless be=20 available online for those who wish to investigate further, but let
us see how the first two are of particular concern to you here,
because the reason you're having difficulties is that you're
combining the two, when really you'd do better to keep them separate.
=20
What, as far as possible, you need to do is to ensure that all those=20
cells that *SOMETIMES* have numeric data in fact *ALWAYS* have
numeric data, even if it's just zero, and deal with the display of it separately by conditional formatting, for example from memory I'm
reasonably sure that it's possible to hide cells containing zeros.
I'm sorry that I haven't time to explain this more thoroughly by experimenting myself and giving you precise instructions as a result,
because I'm extremely busy preparing for a trip away for a family
wedding, but if you can't make progress yourself following the
guidelines given above, I'll be able to give more attention to it
upon my return sometime after the weekend.
=20
=20
On 2025-05-21 10:19, Davey wrote:
I have a spreadsheet to follow my banking. It lists in
consecutive columns:
A. Notes(such as cheque no.), B. Date of transaction, C. Description (name), D. Debit amount, E. credit amount, F. running total.
I have always used this in the running total cell: =IF(OR(ISNUMBER(D19),ISNUMBER(E19)),F18-D19+E19,"")
where Column 19 is the current column, which changes as
appropriate.
ITYM row?
If
there is a debit in D19, or a credit in E19, then the new value for
F19, the new total, is calculated, otherwise a blank is shown.
Recently, there has been an error appearing. Instead of the correct
new value, I am seeing '#VALUE' or '#NAME' appearing. I cannot see
what is wrong.
It is not changed if I remove any Conditional formatting.
Sometimes it cures itself, other times it refuses to be fixed.
The last time, I had to start a new spreadsheet.
Any ideas?
I'm guessing a little here and not testing beforehand what I'm
writing, but I note that the conditionals only test whether either
Dnn or Enn has a number, yet the formula applied assumes in all cases
that both are numbers. I think that perhaps you may need to cover
the four possibilities explicitly:
Neither are numbers ""
Only Dnn is a number F(nn-1) - Dnn
Only Enn is a number F(nn-1) + Enn
Both are numbers F(nn-1) - Dnn + Enn
Perhaps this would be easiest by explicitly defaulting non-numbers in
D or E to 0 in the calculating part of the formula? For example ...
IF(ISNUMBER(D19),D19,0)
I repeat, I haven't tried this, but that's the direction I'd be experimenting in.
Without delving into that yet, why would it work for nearly 700 rows,
and then suddenly start to exhibit the described behaviour?
I have today added a '$' to each of the row letters, just to ensure
that there is no confusion internally in the formula.
I note that the conditionals only test whether either Dnn or Enn has a number, yet the formula applied assumes in all cases that both are numbers.
On 21/05/2025 13:04, Java Jive wrote:
I note that the conditionals only test whether either Dnn or Enn
has a number, yet the formula applied assumes in all cases that
both are numbers.
the formula doesn't check whether the F column from the previous row
is also number?
On 21/05/2025 14:36, Davey wrote:
Without delving into that yet, why would it work for nearly 700
rows, and then suddenly start to exhibit the described behaviour?
I have today added a '$' to each of the row letters, just to ensure
that there is no confusion internally in the formula.
$ can be dangerous.
But I'm tempted to repeat what someone told me a long time ago....
"if you want to go /there/, don't start /here/'. In this case, using something like gnucash, designed for the job, rather than trying to
design from scratch and debug an idiosyncratic spreadsheet might be
more rewarding in the medium term.
Just my 2p worth.
On Wed, 21 May 2025 16:11:31 +0100If the problem isn't currently happening, nobody can give an accurate
Andy Burns <usenet@andyburns.uk> wrote:
On 21/05/2025 13:04, Java Jive wrote:
I note that the conditionals only test whether either Dnn or Enn
has a number, yet the formula applied assumes in all cases that
both are numbers.
the formula doesn't check whether the F column from the previous row
is also number?
No, but that has never been a problem. This problem that I am now seeing
only occurs when the result should be a number.
What, as far as possible, you need to do is to ensure that all those
cells that *SOMETIMES* have numeric data in fact *ALWAYS* have numeric
data, even if it's just zero, and deal with the display of it separately
by conditional formatting, for example from memory I'm reasonably sure
that it's possible to hide cells containing zeros. I'm sorry that I haven't time to explain this more thoroughly by experimenting myself and giving you precise instructions as a result, because I'm extremely busy preparing for a trip away for a family wedding, but if you can't make progress yourself following the guidelines given above, I'll be able to
give more attention to it upon my return sometime after the weekend.
I thought I ought to check up on my statement above based only on
memory, and indeed I was correct. Although it is slightly more complex
than I thought I remembered (but probably that memory was based on
Excel), you can use conditional formatting to hide zero values, as follows:
1) Select the cells to be formatted
2) Format, Cells
3) In the Format Code field, enter something like the following
(this for UK currency, other currencies can be adapted similarly to
this) ...
[=0];[<0][RED][$£-809]#,##0.00;[$£-809]#,##0.00
... explained as follows ...
[=0]; # If =0, no formula, nothing displayed
[<0][RED][$£-809]#,##0.00; # If <0, usual formula but red
Remainder # If neither of above, usual formula
On 28/05/2025 14:26, Java Jive wrote:
I thought I ought to check up on my statement above based only on
memory, and indeed I was correct. Although it is slightly more complex
than I thought I remembered (but probably that memory was based on
Excel), you can use conditional formatting to hide zero values, as
follows:
1) Select the cells to be formatted
2) Format, Cells
3) In the Format Code field, enter something like the following
(this for UK currency, other currencies can be adapted similarly to
this) ...
[=0];[<0][RED][$£-809]#,##0.00;[$£-809]#,##0.00
... explained as follows ...
[=0]; # If =0, no formula, nothing displayed
[<0][RED][$£-809]#,##0.00; # If <0, usual formula but red
Remainder # If neither of above, usual formula
*IF* I understand you correctly ...
That's not what's normally meant by "Conditional formatting" in
LibreOffice Calc. That's just the normal formatting applied to a cell,
and your example is rather over-complicated for this simple case.
I normally use a format string for currencies something like:
[$£-809]#,##0.00;[RED]-[$£-809]#,##0.00;""
Note that there are three semicolon-separated formats here:
- [$£-809]#,##0.00
- [RED]-[$£-809]#,##0.00
- ""
These are applied to display of positive, negative, and zero values respectively. There's no need to specify conditionals explicitly if you
just want those three. (There's also no need, in my experience, to write "[$£-809]" for a pound sign, "£" will do just as well. Your mileage may vary - I'm using Linux with Unicode as the default character encoding
and more hoops may need to be jumped through on other systems/locales.
Note that what LO Calc calls "Conditional formatting" is a feature that allows a cell to be displayed in any defined style according to the
value of any general expression. This lets you do fancy things like
giving alternate lines a grey background, automatically highlighting the largest value in a table, etc.
| Sysop: | Luis Silva |
|---|---|
| Location: | Lisbon |
| Users: | 764 |
| Nodes: | 10 (0 / 10) |
| Uptime: | 140:55:46 |
| Calls: | 331 |
| Calls today: | 1 |
| Files: | 46,971 |
| Messages: | 13,086 |