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.
[Solved] IF then statements - requirements changed
[Solved] IF then statements - requirements changed
Last edited by robleyd on Sun Feb 17, 2019 12:35 am, edited 1 time in total.
Reason: Tagged [Solved]
Reason: Tagged [Solved]
OpenOffice 4.16
OS Windows 10
OS Windows 10
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)
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
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...
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
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.
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 121 times
OpenOffice 4.16
OS Windows 10
OS Windows 10
-
- Volunteer
- Posts: 1547
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
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
then add open parenthesis after each IF and a matching close parenthesis at the end of the statement
finally replace all the THEN and ELSE words with semicolons and BLANK with a Null string.
Applying the same approach to the formula in O3
Likewise, the formula in Q3
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
Code: Select all
=IF( C3=1685 THEN 2 ELSE IF( C3 = 3488 THEN 2 ELSE IF( C3 =2848 THEN 1 ELSE NOTHING)))
Code: Select all
=IF( C3=1685 ; 2 ; IF( C3 = 3488 ; 2 ; IF( C3 =2848 ; 1 ; "" )))
As you can see, there something missing=IF M3=1 THEN SUM(F3,N3) ELSE IF M3=2 THEN BLANK ELSE ???
Likewise, the formula in Q3
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 M3=1THEN blank ELSE IF M3=2 THEN SUM(H3+P3) ELSE ???
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
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
[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
OS Windows 10