Help Needed! Using complex IF function

J

John Ball

Not sure whether =IF function is the correct on to use, but I need t
test on values in column A, to see if the value is 1500 more (+) or les
(-) than values in column B. I would also like to get an output i
column C, so that if the result is "true" then a number "3" can b
attributed for coding purposes

Please find an example of the data below;
A B
97081 21082
20825 21075
94347 21068
92611 21064
90761 21063
89252 21079
87686 21103
85975 21121
84529 21140
82294 21161
80661 21184
78868 21208
77240 21234
75546 21264
73510 21295
72018 21328
70542 21361
68904 21406
67779 21452
66134 21500
64702 21535
63281 21580

Thanks in advance

Joh
 
R

Ron Rosenfeld

Not sure whether =IF function is the correct on to use, but I need to
test on values in column A, to see if the value is 1500 more (+) or less
(-) than values in column B. I would also like to get an output in
column C, so that if the result is "true" then a number "3" can be
attributed for coding purposes

Please find an example of the data below;
A B
97081 21082
20825 21075
94347 21068
92611 21064
90761 21063
89252 21079
87686 21103
85975 21121
84529 21140
82294 21161
80661 21184
78868 21208
77240 21234
75546 21264
73510 21295
72018 21328
70542 21361
68904 21406
67779 21452
66134 21500
64702 21535
63281 21580

Thanks in advance

John
Click to expand...

You need to more clearly state your requirements.
Is what you want to compare the values in the same row of column A & B?
In other words, do you want to compare only A1:B1 when putting a result in C1?
Or did you have something else in mind.

Also, you write you want to test if "1500 more (+) or less (-)".
Do you mean that the value in A is 1500 more than the value in Col B, or that the value in A is less than the value in colB?

In addition to the above, one interpretation would be that you want to test if there is a difference of 1500 between the values on the corresponding rows in col a and col b
Another interpretation, is there a difference of 1500 or more between the values.

If the last is what you really want, the formula would be:

=IF(ABS(A1-B1)>=1500,3,"")

If you want something else, try to explain more clearly.
 
R

Ron Rosenfeld

You need to more clearly state your requirements.
Is what you want to compare the values in the same row of column A & B?
In other words, do you want to compare only A1:B1 when putting a result in C1?
Or did you have something else in mind.

Also, you write you want to test if "1500 more (+) or less (-)".
Do you mean that the value in A is 1500 more than the value in Col B, or that the value in A is less than the value in colB?

In addition to the above, one interpretation would be that you want to test if there is a difference of 1500 between the values on the corresponding rows in col a and col b
Another interpretation, is there a difference of 1500 or more between the values.

If the last is what you really want, the formula would be:

=IF(ABS(A1-B1)>=1500,3,"")

If you want something else, try to explain more clearly.
Click to expand...

Taking your construction of your question very literally, and applying the 1500 difference only to the positive side, one could suggest:

=IF(OR((A1-B1)=1500,A1<B1),3,"")
 
J

joeu2004

John Ball said:
Not sure whether =IF function is the correct on to use,
but I need to test on values in column A, to see if the
value is 1500 more (+) or less (-) than values in column B.
I would also like to get an output in column C, so that
if the result is "true" then a number "3" can be attributed
for coding purposes
Click to expand...

Try:

=IF(ABS(A2-B2)<=1500,3,"")

You neglect to say what you want if column A is not within 1500 of column B.
I opted for the null string (""). Replace "" with something else if you
wish.
 
Top