If / Then / Else statements in Calc

Discuss the spreadsheet application
Post Reply
danost
Posts: 9
Joined: Wed Nov 28, 2007 1:25 pm

If / Then / Else statements in Calc

Post by danost »

I'm trying to make a simple file to track my bank account activities, seems the formula is not quite right, see attached, please help.

The formula in error is: =IF(((C5>"0") OR (D5>"0"));( E4-C5+D5);0)

Many thanks.
Attachments
Calc error.jpg
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: If / Then / Else statements in Calc

Post by keme »

You are comparing numbers to text here. Calc always takes numbers as smaller than text, so with C5 and D5 containing numbers (or empty), the test will always return false.
=IF(((C5>0) OR (D5>0));( E4-C5+D5);0) works.

As far as I can see, using OR as an operator ("infix") is not a documented feature, but it works.

The documented OR works as a function ("prefix"). Thus: =IF(OR((C5>0);(D5>0));( E4-C5+D5);0). This is how I use it. I know i've had problems with the infix notation on some software, but that's a while ago, so perhaps it won't be an issue anymore...
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
Analog_Man
Posts: 1
Joined: Wed Nov 28, 2007 3:25 pm

Re: If / Then / Else statements in Calc

Post by Analog_Man »

It doesn't look like the OR clause is needed at all. Why not just e4-c5=d5?
Dave
Posts: 1011
Joined: Sun Dec 23, 2007 6:53 pm

Re: If / Then / Else statements in Calc

Post by Dave »

Analog_Man wrote:It doesn't look like the OR clause is needed at all. Why not just e4-c5=d5?
Did you try it with both less than zero for example?

David.
danost
Posts: 9
Joined: Wed Nov 28, 2007 1:25 pm

Re: If / Then / Else statements in Calc

Post by danost »

Thank you for your time and thoughts. What I'd like to have is a spread sheet that will resemble a bank statement, but where the balance column will continue down beyond the last valid entry (to be able to enter more data later on). A formula is needed that will make the Balance column beyond the last valid entry NOT be filled with the same amount of dollars as the last valid entry.

Best regards
Dan
danost
Posts: 9
Joined: Wed Nov 28, 2007 1:25 pm

Re: If / Then / Else statements in Calc

Post by danost »

Dave wrote:
Analog_Man wrote:It doesn't look like the OR clause is needed at all. Why not just e4-c5=d5?
Did you try it with both less than zero for example?

David.
Thank you for your time and thoughts. What I'd like to have is a spread sheet that will resemble a bank statement, but where the balance column will continue down beyond the last valid entry (to be able to enter more data later on). A formula is needed that will make the Balance column beyond the last valid entry NOT be filled with the same amount of dollars as the last valid entry.

Best regards
Dan
danost
Posts: 9
Joined: Wed Nov 28, 2007 1:25 pm

Re: If / Then / Else statements in Calc

Post by danost »

keme wrote:You are comparing numbers to text here. Calc always takes numbers as smaller than text, so with C5 and D5 containing numbers (or empty), the test will always return false.
=IF(((C5>0) OR (D5>0));( E4-C5+D5);0) works.

As far as I can see, using OR as an operator ("infix") is not a documented feature, but it works.

The documented OR works as a function ("prefix"). Thus: =IF(OR((C5>0);(D5>0));( E4-C5+D5);0). This is how I use it. I know i've had problems with the infix notation on some software, but that's a while ago, so perhaps it won't be an issue anymore...
Many thanks for this, I'll give it a try
Best regards
Dan
danost
Posts: 9
Joined: Wed Nov 28, 2007 1:25 pm

Re: If / Then / Else statements in Calc

Post by danost »

keme wrote:You are comparing numbers to text here. Calc always takes numbers as smaller than text, so with C5 and D5 containing numbers (or empty), the test will always return false.
=IF(((C5>0) OR (D5>0));( E4-C5+D5);0) works.

As far as I can see, using OR as an operator ("infix") is not a documented feature, but it works.

The documented OR works as a function ("prefix"). Thus: =IF(OR((C5>0);(D5>0));( E4-C5+D5);0). This is how I use it. I know i've had problems with the infix notation on some software, but that's a while ago, so perhaps it won't be an issue anymore...
Many thanks for this, I'll certainly give it a try
Best Regards
Dan
Dave
Posts: 1011
Joined: Sun Dec 23, 2007 6:53 pm

Re: If / Then / Else statements in Calc

Post by Dave »

danost wrote:What I'd like to have is a spread sheet that will resemble a bank statement, but where the balance column will continue down beyond the last valid entry (to be able to enter more data later on). A formula is needed that will make the Balance column beyond the last valid entry NOT be filled with the same amount of dollars as the last valid entry.
Perhaps what you might need is =IF(AND(C5="";D5="");"",E4-C5+D5) ?

This will allow the formula to be inserted prior to amounts being entered in columns C and D.

David.
danost
Posts: 9
Joined: Wed Nov 28, 2007 1:25 pm

Re: If / Then / Else statements in Calc

Post by danost »

Many thanks, this works great. Pity its such a complicated matter to accomplish an apparently simple need.
Best Regards
Dan
danost
Posts: 9
Joined: Wed Nov 28, 2007 1:25 pm

Re: If / Then / Else statements in Calc

Post by danost »

keme wrote:You are comparing numbers to text here. Calc always takes numbers as smaller than text, so with C5 and D5 containing numbers (or empty), the test will always return false.
=IF(((C5>0) OR (D5>0));( E4-C5+D5);0) works.

As far as I can see, using OR as an operator ("infix") is not a documented feature, but it works.

The documented OR works as a function ("prefix"). Thus: =IF(OR((C5>0);(D5>0));( E4-C5+D5);0). This is how I use it. I know i've had problems with the infix notation on some software, but that's a while ago, so perhaps it won't be an issue anymore...
This works, many thanks for your good efforts, very much appreciated.
Best Regards
Dan
User avatar
Villeroy
Volunteer
Posts: 31270
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: If / Then / Else statements in Calc

Post by Villeroy »

danost wrote:Many thanks, this works great. Pity its such a complicated matter to accomplish an apparently simple need.
Best Regards
Dan
Instead of writing your own accounting software on a spreadsheet (in my opinion the totally wrong tool, by the way) you may try something like http://gnucash.org/
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply