Page 1 of 1

[Solved] IF then statements - requirements changed

Posted: Fri Feb 15, 2019 8:39 pm
by lhentz
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.

Re: IF then statements - requirements changed

Posted: Fri Feb 15, 2019 9:42 pm
by Villeroy
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)
Is it really that difficult to read a spreadsheet manual?
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.

Re: IF then statements - requirements changed

Posted: Fri Feb 15, 2019 9:43 pm
by keme
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.

Re: IF then statements - requirements changed

Posted: Sat Feb 16, 2019 1:14 am
by lhentz
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.

Re: IF then statements - requirements changed

Posted: Sat Feb 16, 2019 7:41 am
by UnklDonald418
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

=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

=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

=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)

[SOLVED] IF then statements - requirements changed

Posted: Sat Feb 16, 2019 9:23 pm
by lhentz
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.