if >200 and <300 formula

[Guest]

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

 

[Trevor Shuttleworth]

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

Regards

Trevor

 

[krcowen]

Ann

=if(and(A1>200,A1<300),A1,0)

is one way.

Good luck.

Ken
Norfolk, Va

 

[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)

 

[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

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

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

Rick

 

[Guest]

thanks all, the first was the easiest and it works!

 

[Roger Govier]

Or even

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

 

[Rick Rothstein \(MVP - VB\)]

Or even

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

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

 

[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.

 

[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

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