0

I am trying to sort a column out in open office and need the cell values to stay between 10 and 0. I currently have an If statement as follows: If(X6>0;(((S7/V7)-($B$6))/$B$4)+$X$5;0)
I need to also include an IF statement in the same cell as follows: IF(X6>10;$D$26=0;(((S7/V7)-($B$6))/$B$4)+$X$5)

How can I get these 2 IF statements to work within the same cell?

2 Answers 2

2

The "then" and "else" parts in if(condition; then; else) can be an IF statement too:

if(condition1; then1; else if(condition2; then2; else2))
if(x6<=0; 0; if(x6>10; $D$26=0; (((S7/V7)-($B$6))/$B$4)+$X$5))

Or:

if(condition1; then if(condition2; then2; else2); else1)
if(x6>0; if(x6>10; $D$26=0; (((S7/V7)-($B$6))/$B$4)+$X$5); 0)
1
  • ...but I'm not sure what the $D$26=0 in your original formula does though...
    – Arjan
    Dec 31, 2016 at 15:43
1

Here are the two IF expressions in your question:

If(X6>0; (((S7/V7)-($B$6))/$B$4)+$X$5; 0)         (with spaces added for clarity)   and
IF(X6>10; $D$26=0; (((S7/V7)-($B$6))/$B$4)+$X$5)

I present them like this because it’s not obvious from the question that the

                   (((S7/V7)-($B$6))/$B$4)+$X$5

subexpression is the same in both.  Arjan’s answer is correct, but it depends on the fact that your two IF expressions have a common subexpression.

I also wonder what you intend the $D$26=0 subexpression to do.  If it’s a typo, and you mean

IF(X6>10;     0  ; (((S7/V7)-($B$6))/$B$4)+$X$5)

then there’s another answer that (IMHO) is clearer:

IF(AND(X6>0;X6<=10); (((S7/V7)-($B$6))/$B$4)+$X$5; 0)

Note that your text says “between 10 and 0”, but you are treating 0 and 10 differently.
____________
By the way, you can simplify this subexpression a little by removing excess parentheses.  You can simplify it to any of the following:

  • (((S7/V7)-$B$6)/$B$4)+$X$5
  • ((S7/V7-$B$6)/$B$4)+$X$5
  • (S7/V7-$B$6)/$B$4+$X$5

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .