Formulas beyween a value?

S

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?
 
J

JE McGimpsey

Samtlila said:
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?
Click to expand...

One way:

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

Samtlila

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

JE McGimpsey

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

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