if >200 and <300 formula

A

Ann

how do i do a if A1 >200 and <300 return A1 otherwise return 0. i can't get
the logic to work
 
P

Piscator

One way is with two IF statements. logically reads:
If (A1>200 then If A1<300 then A1, else 0), else 0)

=IF(A1>200,IF(A1<300,A1,0),0)
 
R

Rick Rothstein \(MVP - VB\)

how do i do a if A1 >200 and <300 return A1 otherwise
return 0. i can't get the logic to work
Click to expand...

Everyone has given you the IF function method, so let me be different.<g>

=A1*AND(A1>200,A1<300)

Rick
 
R

Rick Rothstein \(MVP - VB\)

Or even
=A1*(A1>200)*(A1<300)
Click to expand...

Yeah, I was going to post that with an explanation that understanding this
construction will be helpful in making use of such functions as SUMPRODUCT
later on; but I had to take care of something and couldn't get back to my
computer until just now.

Rick
 
R

Roger Govier

Yes Rick, I posted in haste and should have explained to the OP that the
tests
(A1>200)
(A1<300)
would lead to a True or False result.
When Multiplied together, the True's would be coerced to 1's and the
False's to 0's

Hence
A1*TRUE*TRUE becomes A1*1*1 which returns the value in A1
If either (or both) of the tests is False, then we get 0.
A1*0*1 = 0
A1*1*0 = 0

As you rightly say, understanding this principle, does help with the
understanding of other functions such as Sumproduct.
 
R

Rick Rothstein \(MVP - VB\)

Yes Rick, I posted in haste and should have explained to the OP that the
tests
(A1>200)
(A1<300)
would lead to a True or False result.
When Multiplied together, the True's would be coerced to 1's and the
False's to 0's

Hence
A1*TRUE*TRUE becomes A1*1*1 which returns the value in A1
If either (or both) of the tests is False, then we get 0.
A1*0*1 = 0
A1*1*0 = 0
Click to expand...

I was going to make the OP look it up and try to decipher it... in thinking
about it, it is better that the explanation be given directly as you did...
good job there.

Rick
 
Top