[Solved] Almost There - IF Statement

Discuss the spreadsheet application

[Solved] Almost There - IF Statement

Postby quarkrad » Thu Apr 11, 2019 8:34 pm

The attached shows how much to add to a unit price of £80, £4 if the drop down in A7 is "good". I would like to add to the formula in E5 such that if A7 is changed to "bad" then D3 is added to B3. I've tried a number of variants but no luck so far.
Attachments
if.ods
(9.53 KiB) Downloaded 11 times
Last edited by Hagar Delest on Fri Apr 12, 2019 8:13 am, edited 1 time in total.
Reason: tagged solved
libreoffice 5.4.1.2 on ubuntu 16.04
quarkrad
 
Posts: 59
Joined: Mon Nov 03, 2008 10:01 am

Re: Almost There - IF Statement

Postby quarkrad » Thu Apr 11, 2019 8:41 pm

Not sure if this is the most effective but =IF(A7="good",B3+C3,IF(A7="bad",B3+D3)) appears to work.
libreoffice 5.4.1.2 on ubuntu 16.04
quarkrad
 
Posts: 59
Joined: Mon Nov 03, 2008 10:01 am

Re: Almost There - IF Statement

Postby jeffs12 » Thu Apr 11, 2019 9:47 pm

If A7 is ALWAYS and ONLY "good" or "bad", you don't need the nested IF() statement; a simple =IF(A7="good";B3+C3;B3+D3) is all you need. The result is that any value (or lack of value) in A7, other than "good", will result in B3+D3.
If A7 may be blank, or some other value, use your nested IF() statement, but add the otherwise parameter after the B3+D3 to display what you want in E5 if A7 is neither "good" nor "bad".
AOO 4.1.3 on M$ Windows 7/10
jeffs12
 
Posts: 39
Joined: Wed Mar 19, 2008 9:48 pm

Re: Almost There - IF Statement

Postby quarkrad » Thu Apr 11, 2019 10:06 pm

Thank you very much - I think I'm beginning to understand this LITTLE bit. Appreciate your help.
libreoffice 5.4.1.2 on ubuntu 16.04
quarkrad
 
Posts: 59
Joined: Mon Nov 03, 2008 10:01 am

Re: Almost There - IF Statement

Postby RusselB » Thu Apr 11, 2019 10:11 pm

A slight re-write of jeffs12's code, just to give you another option, could be
Code: Select all   Expand viewCollapse view
=B3+if(A7="good";C3;D3)
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5391
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Almost There - IF Statement

Postby Villeroy » Thu Apr 11, 2019 11:06 pm

if the first argument evaluates to TRUE then return the second argument (if_true) value, otherwise return the third argument (if_false) value:
IF(condition ; if_true ; if_false)
nested, branching from the false:
IF(condition1 ; if1_true ; IF(condition2 ; if2_true ; if2_false))
or branching from the true:
IF(condition1 ; IF(condition2 ; if2_true ; if2_false) ; if1_false)

In your formula which branches from the false:
IF(A7="good" ; B3+C3 ; IF(A7="bad" ; B3+D3))
the if2_false is missing. The inner IF has only 2 arguments as in:
IF(condition ; if_true )
When the third argument to IF is missing, the function returns FALSE (equivalent to 0) in case of false as you can see when you put anything else A7 that is not "good" or "bad".

Personally, I try my best to avoid any use of IF. And I avoid words with special meanings such as "good" or "bad". They add unnecessary complexity.
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.x
User avatar
Villeroy
Volunteer
 
Posts: 27090
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 23 guests