Formulas beyween a value?

[Samtlila]

Having difficulty setting up a formula & i cant find any way of doing this!
If anyone can help me out it would be great.

Basically i would like to make a formula that only comes into effect if the
number in any given cell is between two amounts.

For example if the number is from 120 to 150 it would appear, if not it
would just be a zero?

Is this possible?

 

[JE McGimpsey]

Having difficulty setting up a formula & i cant find any way of doing this!
If anyone can help me out it would be great.

Basically i would like to make a formula that only comes into effect if the
number in any given cell is between two amounts.

For example if the number is from 120 to 150 it would appear, if not it
would just be a zero?

Is this possible?

One way:

=IF(AND(A1>=120, A1<=150),A1,"")

 

[Samtlila]

many thanks for the reply! i am quite new to formulas tho, can you expalint
to me what is going on here?

 

[JE McGimpsey]

many thanks for the reply! i am quite new to formulas tho, can you expalint
to me what is going on here?

The formula is

=IF(AND(A1>=120, A1<=150),A1,"")

1) Comparison operators (i.e., >, <, >=, >=, <>, =) return boolean
(TRUE/FALSE) values, so if the value in A1 is 140, say, then

A1>=120

returns TRUE, and

A1<=150

returns TRUE.

2) The AND function returns TRUE if all of its arguments are TRUE, and
FALSE if any of the arguments are FALSE.

3) The

IF(condition, true branch, false branch)

formula evaluates the condition. If the condition is TRUE, the True
branch is executed, if FALSE, the false branch is executed, so:

If A1 = 140, the entire formula evaluates to:

=IF(AND(TRUE, TRUE), A1, "")

which is equivalent to

=IF(TRUE, A1, "")

which returns A1.

If A1 = 100, then

=IF(AND(FALSE, TRUE), A1,"")

is equivalent to

=IF(FALSE,A1,"")

which returns the null string.