If / Then / Else statements in Calc

Discuss the spreadsheet application

If / Then / Else statements in Calc

Postby danost » Tue Dec 09, 2008 8:29 am

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
danost
 
Posts: 9
Joined: Wed Nov 28, 2007 1:25 pm

Re: If / Then / Else statements in Calc

Postby keme » Tue Dec 09, 2008 9:42 am

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...
OOo 3.3.0 and Apache OOo 3.4.1/4.0.0, on Ms Windows 7 and Mac OS-X.
User avatar
keme
Volunteer
 
Posts: 1601
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: If / Then / Else statements in Calc

Postby Analog_Man » Tue Dec 09, 2008 8:59 pm

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

Postby Dave » Wed Dec 10, 2008 2:16 am

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

Postby danost » Wed Dec 24, 2008 6:04 pm

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

Postby danost » Wed Dec 24, 2008 6:05 pm

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

Postby danost » Wed Dec 24, 2008 6:06 pm

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

Postby danost » Wed Dec 24, 2008 6:07 pm

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

Postby Dave » Wed Dec 24, 2008 6:27 pm

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

Postby danost » Wed Dec 24, 2008 6:33 pm

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

Postby danost » Wed Dec 24, 2008 6:34 pm

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

Postby Villeroy » Wed Dec 24, 2008 7:07 pm

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 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17262
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 11 guests