I have a spreadsheet to keep tabs on my bank account.
One cell uses, today: G576=IF(OR($D576>0,$E576>0),G575-D576+E576,"") to
show the current balance. $D576 is the current outgoing, $E576 is the
current incoming, G575 is the previous total. If there are no outgoings
or incomings, then the display is "", or blank. It works fine.
I am adding a cell to display the theoretical result if I include the
amount due to my credit card account, to be found in $N$2.
So I have used H576=IF(G576>0,G576-$N$2,""), which works if there is a
value in or out. But it doesn't work when there is nothing in G576, it
faults with "VALUE?". If I delete the $N$2 reference, it's happy, but
then I have a column full of "VALUE?" for the future rows.
What is the problem? Why does it want a value in this formula, but not
in the first one?
On 15/10/2024 00:08, Davey wrote:
I have a spreadsheet to keep tabs on my bank account.
One cell uses, today:
G576=IF(OR($D576>0,$E576>0),G575-D576+E576,"") to show the current
balance. $D576 is the current outgoing, $E576 is the current
incoming, G575 is the previous total. If there are no outgoings or incomings, then the display is "", or blank. It works fine. I am
adding a cell to display the theoretical result if I include the
amount due to my credit card account, to be found in $N$2. So I
have used H576=IF(G576>0,G576-$N$2,""), which works if there is a
value in or out. But it doesn't work when there is nothing in G576,
it faults with "VALUE?". If I delete the $N$2 reference, it's
happy, but then I have a column full of "VALUE?" for the future
rows. What is the problem? Why does it want a value in this
formula, but not in the first one?
Rather than trying to decode what your problem is, may I suggest you
look at gnucash? - it's purpose made for financial accounting and
could save much heartache.
On Tue, 15 Oct 2024 08:45:16 +0100 Mike Scott <usenet.16@scottsonline.org.uk.invalid> wrote:
On 15/10/2024 00:08, Davey wrote:
I have a spreadsheet to keep tabs on my bank account.
I'll take a look, thanks.
Rather than trying to decode what your problem is, may I suggest you
look at gnucash? - it's purpose made for financial accounting and could
save much heartache.
On Tue, 15 Oct 2024 09:16:06 +0100, Davey wrote:
On Tue, 15 Oct 2024 08:45:16 +0100 Mike Scott <usenet.16@scottsonline.org.uk.invalid> wrote:
On 15/10/2024 00:08, Davey wrote:
I have a spreadsheet to keep tabs on my bank account.
[snip]
I'll take a look, thanks.
Rather than trying to decode what your problem is, may I suggest
you look at gnucash? - it's purpose made for financial accounting
and could save much heartache.
Or maybe homebank? Personally, I find homebank simpler than gnucash.
Depends what you want to do :)
On Tue, 15 Oct 2024 08:47:44 +0000 Andrew Woodward <andrew.woodward@gmx.co.uk> wrote:
On Tue, 15 Oct 2024 09:16:06 +0100, Davey wrote:Simply keep an up-to-date spreadsheet that follows my bank accounts as I enter transactions, essentially so I can see if I need to transfer money
On Tue, 15 Oct 2024 08:45:16 +0100 Mike Scott
<usenet.16@scottsonline.org.uk.invalid> wrote:
On 15/10/2024 00:08, Davey wrote:
I have a spreadsheet to keep tabs on my bank account.
[snip]
from my savings account to my chequing account, and monthly, so I can
account for each transaction. Nothing too complicated, until I tried to
ad this one extra cell to the sheet. If I could solve this one
conundrum, it would be all I need.
On Tue, 15 Oct 2024 09:52:32 +0100, Davey wrote:
On Tue, 15 Oct 2024 08:47:44 +0000 Andrew Woodward <andrew.woodward@gmx.co.uk> wrote:
On Tue, 15 Oct 2024 09:16:06 +0100, Davey wrote:Simply keep an up-to-date spreadsheet that follows my bank accounts
On Tue, 15 Oct 2024 08:45:16 +0100 Mike Scott
<usenet.16@scottsonline.org.uk.invalid> wrote:
On 15/10/2024 00:08, Davey wrote:
I have a spreadsheet to keep tabs on my bank account.
[snip]
as I enter transactions, essentially so I can see if I need to
transfer money from my savings account to my chequing account, and
monthly, so I can account for each transaction. Nothing too
complicated, until I tried to ad this one extra cell to the sheet.
If I could solve this one conundrum, it would be all I need.
Try replacing H576=IF(G576>0,G576-$N$2,"") with H576=IF(ISNUMBER(G576),G576-$N$2,"")
On Tue, 15 Oct 2024 09:27:42 +0000 Andrew Woodward <andrew.woodward@gmx.co.uk> wrote:
On Tue, 15 Oct 2024 09:52:32 +0100, Davey wrote:
On Tue, 15 Oct 2024 08:47:44 +0000 Andrew Woodward
<andrew.woodward@gmx.co.uk> wrote:
On Tue, 15 Oct 2024 09:16:06 +0100, Davey wrote:Simply keep an up-to-date spreadsheet that follows my bank accounts
On Tue, 15 Oct 2024 08:45:16 +0100 Mike Scott
<usenet.16@scottsonline.org.uk.invalid> wrote:
On 15/10/2024 00:08, Davey wrote:
I have a spreadsheet to keep tabs on my bank account.
[snip]
as I enter transactions, essentially so I can see if I need to
transfer money from my savings account to my chequing account, and
monthly, so I can account for each transaction. Nothing too
complicated, until I tried to ad this one extra cell to the sheet. If
I could solve this one conundrum, it would be all I need.
Try replacing H576=IF(G576>0,G576-$N$2,"") with
H576=IF(ISNUMBER(G576),G576-$N$2,"")
Now that did the job! Many thanks. I had not encountered ISNUMBER
before.
Why does yours work, when the complaint it had before, was that the $N$2
was what it wanted a value for? ISNUMBER(G576) would appear to refer to
G576, not N2.
I'm confused. But it saves me learning a new programme.
On Tue, 15 Oct 2024 11:17:27 +0100, Davey wrote:
On Tue, 15 Oct 2024 09:27:42 +0000 Andrew Woodward <andrew.woodward@gmx.co.uk> wrote:
On Tue, 15 Oct 2024 09:52:32 +0100, Davey wrote:
On Tue, 15 Oct 2024 08:47:44 +0000 Andrew Woodward
<andrew.woodward@gmx.co.uk> wrote:
On Tue, 15 Oct 2024 09:16:06 +0100, Davey wrote:Simply keep an up-to-date spreadsheet that follows my bank
On Tue, 15 Oct 2024 08:45:16 +0100 Mike Scott
<usenet.16@scottsonline.org.uk.invalid> wrote:
On 15/10/2024 00:08, Davey wrote:
I have a spreadsheet to keep tabs on my bank account.
[snip]
accounts as I enter transactions, essentially so I can see if I
need to transfer money from my savings account to my chequing
account, and monthly, so I can account for each transaction.
Nothing too complicated, until I tried to ad this one extra cell
to the sheet. If I could solve this one conundrum, it would be
all I need.
Try replacing H576=IF(G576>0,G576-$N$2,"") with
H576=IF(ISNUMBER(G576),G576-$N$2,"")
Now that did the job! Many thanks. I had not encountered ISNUMBER
before.
Why does yours work, when the complaint it had before, was that the
$N$2 was what it wanted a value for? ISNUMBER(G576) would appear to
refer to G576, not N2.
I'm confused. But it saves me learning a new programme.
It's not $N$2 that is causing the problem. If you substitute (e.g.)
123 in place of $N$2, it still gives '#VALUE!'.
The 'problem' is the way Calc handles cell values. It sounds weird,
but an 'empty' string is not nothing, it is a string with nothing in
it, and *any* string evaluates to greater than a numeric value; thus
G576>0 is true, but then ""-$N$2 is not a valid statement.
See:
https://forum.openoffice.org/en/forum/viewtopic.php?p=402757#p402757
for a better explanation.
On Tue, 15 Oct 2024 11:17:27 +0100, Davey wrote:
On Tue, 15 Oct 2024 09:27:42 +0000 Andrew Woodward <andrew.woodward@gmx.co.uk> wrote:
On Tue, 15 Oct 2024 09:52:32 +0100, Davey wrote:
On Tue, 15 Oct 2024 08:47:44 +0000 Andrew Woodward
<andrew.woodward@gmx.co.uk> wrote:
On Tue, 15 Oct 2024 09:16:06 +0100, Davey wrote:Simply keep an up-to-date spreadsheet that follows my bank
On Tue, 15 Oct 2024 08:45:16 +0100 Mike Scott
<usenet.16@scottsonline.org.uk.invalid> wrote:
On 15/10/2024 00:08, Davey wrote:
I have a spreadsheet to keep tabs on my bank account.
[snip]
accounts as I enter transactions, essentially so I can see if I
need to transfer money from my savings account to my chequing
account, and monthly, so I can account for each transaction.
Nothing too complicated, until I tried to ad this one extra cell
to the sheet. If I could solve this one conundrum, it would be
all I need.
Try replacing H576=IF(G576>0,G576-$N$2,"") with
H576=IF(ISNUMBER(G576),G576-$N$2,"")
Now that did the job! Many thanks. I had not encountered ISNUMBER
before.
Why does yours work, when the complaint it had before, was that the
$N$2 was what it wanted a value for? ISNUMBER(G576) would appear to
refer to G576, not N2.
I'm confused. But it saves me learning a new programme.
It's not $N$2 that is causing the problem. If you substitute (e.g.)
123 in place of $N$2, it still gives '#VALUE!'.
The 'problem' is the way Calc handles cell values. It sounds weird,
but an 'empty' string is not nothing, it is a string with nothing in
it, and *any* string evaluates to greater than a numeric value; thus
G576>0 is true, but then ""-$N$2 is not a valid statement.
See:
https://forum.openoffice.org/en/forum/viewtopic.php?p=402757#p402757
for a better explanation.
| Sysop: | Luis Silva |
|---|---|
| Location: | Lisbon |
| Users: | 763 |
| Nodes: | 10 (0 / 10) |
| Uptime: | 179:47:47 |
| Calls: | 111 |
| Files: | 46,971 |
| Messages: | 11,214 |