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.
The formula in error is: =IF(((C5>"0") OR (D5>"0"));( E4-C5+D5);0)
Many thanks.
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...
=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
-
- Posts: 1
- Joined: Wed Nov 28, 2007 3:25 pm
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?
Re: If / Then / Else statements in Calc
Did you try it with both less than zero for example?Analog_Man wrote:It doesn't look like the OR clause is needed at all. Why not just e4-c5=d5?
David.
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
Best regards
Dan
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.Dave wrote:Did you try it with both less than zero for example?Analog_Man wrote:It doesn't look like the OR clause is needed at all. Why not just e4-c5=d5?
David.
Best regards
Dan
Re: If / Then / Else statements in Calc
Many thanks for this, I'll give it a trykeme 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...
Best regards
Dan
Re: If / Then / Else statements in Calc
Many thanks for this, I'll certainly give it a trykeme 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...
Best Regards
Dan
Re: If / Then / Else statements in Calc
Perhaps what you might need is =IF(AND(C5="";D5="");"",E4-C5+D5) ?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.
This will allow the formula to be inserted prior to amounts being entered in columns C and D.
David.
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
Best Regards
Dan
Re: If / Then / Else statements in Calc
This works, many thanks for your good efforts, very much appreciated.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...
Best Regards
Dan
Re: If / Then / Else statements in Calc
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/danost wrote:Many thanks, this works great. Pity its such a complicated matter to accomplish an apparently simple need.
Best Regards
Dan
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice