## [Solved] IF then statements - requirements changed

### [Solved] IF then statements - requirements changed

Why do people always change requirements on you. Hopefully I do better and only need some direction like column M.
Maybe it would help if I list the columns so everyone can understand better what I am trying to do. In the parentheses I have listed the value that should be obtained by the formula. Rem marks are in [ ]. The first 4 columns are manually input.
Col C = Account (2848)
Col D = Purchase (274.18)
Col F = LG (28.01)
Col H = LGH (22.88)
Col J = New Total =sum(d3-f3-h3)
Col K = Always is 3
Col L = Per person (74.43) =j3/3
Col M = # Shares (1) =IF(OR(C3=1685;2;c3=3488;2;IF(C3=2848;1;""))
[If C3 = 2848 then M3=1
If C3 = 1686 then M3=2
If C3 = 3488 then M3=2]
Col N = LG Paid (74.43) =Sum(L3*M3)
Col O = LG Owe (102.44) =IF(OR(M3=1;=sum(F3,N3);IF(M3=;2;O3=leave field blank))
[If M3 = 1 then it equals F3 plus N3
If M2 = 2 then the field should be empty]
Col P = LGH Paid (148.86) =Sum(L3*M3)
Col Q = LGH Owe (blank) =IF(OR(M3=1;blank;IF(M3=2;=sum(H3+P3))
[If M3=1 then the field should be empty
If M3=2 then it equals H3 plus P3]

Wow. I hope this is close. I am definitely new to this.
Last edited by robleyd on Sun Feb 17, 2019 12:35 am, edited 1 time in total.
Reason: Tagged [Solved]
OpenOffice 4.16
OS Windows 10
lhentz

Posts: 7
Joined: Mon Feb 11, 2019 6:46 pm

### Re: IF then statements - requirements changed

Why should we type all this into a sheet?
Why don't you copy all these data and formulas into a sheet and attach it here?
Why do you sum a single value as in =sum(d3-f3-h3)
Why don't people read books anymore? Some 30 years ago I read the book that was shipped with a spreadsheet application much simpler than Calc and still benefit from that knowledge.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4 Villeroy
Volunteer

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

### Re: IF then statements - requirements changed

The OR() in the contitional formulas is a mistake. You can do it with nested IF(IF()) constructs, but depending on context and the nature of requirement change, there may be better ways to do it.

I suggest you...
• attach a file here with what you have. Explain what you have working as required, and what needs change.
• describe how requirements have changed, is it likely to happen again, and why, how and how often. keme
Volunteer

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

### Re: IF then statements - requirements changed

I am sorry if this has been a problem for some but I am trying my best and didn't realize that I could attach a file.

The more that I look at it the more I get confused and would be grateful for anyone who is nice enough to help.
Attachments lG lgh w help.ods
OpenOffice 4.16
OS Windows 10
lhentz

Posts: 7
Joined: Mon Feb 11, 2019 6:46 pm

### Re: IF then statements - requirements changed

Since Calc lacks a Case function nested IF statements can be used. It can be helpful to first write out the full statement
For instance the formula in M3 would be
Code: Select all   Expand viewCollapse view
`=IF C3=1685 THEN 2 ELSE IF C3 = 3488 THEN 2 ELSE IF C3 =2848 THEN 1 ELSE BLANK`

then add open parenthesis after each IF and a matching close parenthesis at the end of the statement
Code: Select all   Expand viewCollapse view
`=IF( C3=1685 THEN 2 ELSE IF( C3 = 3488 THEN 2 ELSE IF( C3 =2848 THEN 1 ELSE NOTHING)))`

finally replace all the THEN and ELSE words with semicolons and BLANK with a Null string.
Code: Select all   Expand viewCollapse view
`=IF( C3=1685 ; 2 ; IF( C3 = 3488 ; 2 ; IF( C3 =2848 ; 1 ; "" )))`

Applying the same approach to the formula in O3
=IF M3=1 THEN SUM(F3,N3) ELSE IF M3=2 THEN BLANK ELSE ???

As you can see, there something missing

Likewise, the formula in Q3
=IF M3=1THEN blank ELSE IF M3=2 THEN SUM(H3+P3) ELSE ???

The argument to the SUM function can have cell names separated by a semicolon to denote a list or a colon to designate a range. (H3+P3) without the SUM function would work the same as SUM(H3;P3)
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer

Posts: 1326
Joined: Wed Jun 24, 2015 12:56 am

### [SOLVED] IF then statements - requirements changed

UNKLdonald418 you are a blessing. With your help, I have been able to make the sheet work and learn at the same time. I was getting tripped over the use of the ELSE statement and using the OR. I have printed this for future use.
OpenOffice 4.16
OS Windows 10
lhentz

Posts: 7
Joined: Mon Feb 11, 2019 6:46 pm