I want to Ref two different cells in my IF statement. The problem is the the second cell is a drop menu. If the A or B in the menu then I only need to Ref the first cell. But if C or D then I need to output a different value.
Example: =IF((D2="C" OR "D",C2*8),C2>0,C2*3,"")
D2 is the drop menu, C2 is numerical value
The OR and the AND are Functions in the Calc application, but not statements.
Example:
=OR(A1;B1)
The result is a Boolean value.
1 Like
This is my current formula:
=IF(D3="C",C3*1.4,IF(AND(C3>0),C3*0.85,""))
And it works. However I also need D3="C"or"D" and have the same outcome. Please assume I know little about programing when giving an answer.
I guess you need to replace your D3="C" with OR( D3="C"; D3="D") then.
Edit:
It seems you found out yourself below…
try the case-function: IFS(OR(C2=“a”;C2=“b”);//case1=true//;OR(C2=“c”;C2=“d”);//case2=true//;OR(C2=“e”;C2=“f”);//case3=true//;1=1;"???")
the case 1=1 with output ??? if no case=true (all cases=false)
=IFS(OR(D3="C",D3="D"),C3*1.4,IF(AND(C3>0),C3*0.85,""))
This kind of works. That is to say I get the proper output for “C” and “D”, but my other options on the drop menu come up as N/A. And when there is not any input it should be “(blank)” and that comes up as N/A as well.
Mixing/nesting IFS() and IF() is not for the faint of heart, and not needed here as I read it. Also, for a single conditional expression the AND() is not needed. AND() and OR() are used when you need to combine/group several conditions into one “outcome”.
The #N/A (=“not available”) result is correct, because for the second condition to IFS, you have not given a result expression. The IF(…) constitutes the entire condition parameter and there is no return value for it. This is surely not what you intended.
IFS() takes pairs of condition/result expressions as input parameters. You probably need:
=IFS(OR(D3="C",D3="D"),C3*1.4,C3>0,C3*0.85,TRUE,"")
A structural breakdown (note the pairing of condition/result):
-
=IFS
-
OR(D3=“C”,D3=“D”) … The first condition for IFS
- C3*1.4 … The result to return if the first condition is satisfied
-
C3>0 … The second condition
- C3*0.85 … Result to return if the second condition is satisfied
-
TRUE … A catch-all “condition” for defaulting
- "" … The default to return when none of the actual conditions are satisfied.
-
OR(D3=“C”,D3=“D”) … The first condition for IFS
krude logical:
=IFS(OR(D3="C",D3="D"),C3*1.4,IF(AND(C3>0),C3*0.85,""))
convert that into:
=IFS(OR(D3="C",D3="D"),C3*1.4,C3>0,C3*0.85,1=1,"")
so OR(D3="C",D3="D"),C3*1.4 is the 1st argument if true,
C3>0,C3*0.85 is the 2nd argument if true,
1=1,"" is the 3rd argument if 1st and 2nd are false.
[erAck: edited to format code as code, see This is the guide - How to use the Ask site? - #6 by erAck]
Please use the “Preformatted text” TAG for the formulas in this site.
The asterix sign not appeared for me in your formulas.