[Solved] IF then statements - requirements changed

Discuss the spreadsheet application
Post Reply
lhentz
Posts: 7
Joined: Mon Feb 11, 2019 6:46 pm

[Solved] IF then statements - requirements changed

Post 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.
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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: IF then statements - requirements changed

Post 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.
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
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: IF then statements - requirements changed

Post 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.
lhentz
Posts: 7
Joined: Mon Feb 11, 2019 6:46 pm

Re: IF then statements - requirements changed

Post 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.
Attachments
lG lgh w help.ods
(13.25 KiB) Downloaded 107 times
OpenOffice 4.16
OS Windows 10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: IF then statements - requirements changed

Post 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)
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.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
lhentz
Posts: 7
Joined: Mon Feb 11, 2019 6:46 pm

[SOLVED] IF then statements - requirements changed

Post 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.
OpenOffice 4.16
OS Windows 10
Post Reply