IF CONDITIONS

[stantheman007]

hey everyone,

I am trying to make a formula that outputs a value of 1 when some conditions hold, and 0 when they don't. Here is what I have tried: {=IF(($A$2:$A$991=$A3)*($M$2:$M$991=$M3)*($P$2:$P$991=$P3)*((0.95*$R3)<$R3<(1.05*$R3)), "1", "0")}

The inequalities are for one of the conditions; that the value in R column be between +/- 5% of the highlighted value.

Excel only outputs 0s in this formula, even though I am sure that there should be 1s somewhere.

Any help on this would be great.

Stan

 

[mikerickson]

The other terms seem straight forward (do the values in columns A,M and P match those of the current row 3).
But the next term in your formula puzzles me. In words, what do you mean by this term ((0.95*$R3)<$R3<(1.05*$R3)) ?

 

[stantheman007]

The other terms seem straight forward (do the values in columns A,M and P match those of the current row 3).
But the next term in your formula puzzles me. In words, what do you mean by this term ((0.95*$R3)<$R3<(1.05*$R3)) ?

So I want Excel to check the R column (R2:R991) and if others values are within 5% of R3 (R4, R5...), as well as if the A,M P conditions hold, then to output a value of 1. Does this make more sense, I subsequently tried {=IF(AND(($A$2:$A$991=A3), ($M$2:$M$991=M3), ($P$2:$P$991=P3), ($R$2:$R$991= (0.95*R3)<R3<(1.05*R3))), "1", "0")} but it still outputted only 0s...

Thanks!

 

[stantheman007]

The other terms seem straight forward (do the values in columns A,M and P match those of the current row 3).
But the next term in your formula puzzles me. In words, what do you mean by this term ((0.95*$R3)<$R3<(1.05*$R3)) ?

Also I should say that M and P columns output text

 

[I_cant_read]

Maybe? separate the formula.

=($R$2:$R$991>=(.95*R3),$R$2:$R$991<=(1.05*R3)

Just guessing.

 

[stantheman007]

Maybe? separate the formula.

=($R$2:$R$991>=(.95*R3),$R$2:$R$991<=(1.05*R3)

Just guessing.

Legend. It works!