## If / Then / Else statements in Calc

### If / Then / Else statements in Calc

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
danost

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

### Re: If / Then / Else statements in Calc

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 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14

keme
Volunteer

Posts: 2828
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

### Re: If / Then / Else statements in Calc

It doesn't look like the OR clause is needed at all. Why not just e4-c5=d5?
Analog_Man

Posts: 1
Joined: Wed Nov 28, 2007 3:25 pm

### Re: If / Then / Else statements in Calc

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.
Dave

Posts: 1011
Joined: Sun Dec 23, 2007 6:53 pm

### Re: If / Then / Else statements in Calc

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

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

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

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
danost

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

### Re: If / Then / Else statements in Calc

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.
Dave

Posts: 1011
Joined: Sun Dec 23, 2007 6:53 pm

### Re: If / Then / Else statements in Calc

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

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
danost

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

### Re: If / Then / Else statements in Calc

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 16.04, OpenOffice 4.x & LibreOffice 5.x

Villeroy
Volunteer

Posts: 25365
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany