excel formula

  • Thread starter Thread starter Biff
  • Start date Start date
B

Biff

Hi George,

Not interested in that option? OK. Let me try another
explanation.

You want to know if B4 is less than B1 and depending on
how much less, assign a specific value. No problem. You
ask if B4<B1 by 2 then reward 2, if B4<B1 by 3 then reward
5. Using your value for B1 as 4. Let's say that B4 has the
value of 2. This meets your condition, B4<B1 by 2. To
express that in a formula it would be: B4=B1-2 or 2=4-2.
If you were to express it as B4<B1-2, B4 would have to be
less than 2. Use the same logic for B4<B1 by 3.

Now here's where the problem comes in. You haven't defined
what to do if these conditions are not met. What if B4=B1
or what if B4>B1 or what if B4<B1 by 1? We can construct
the formula up to that point as:

=IF(B4=B1-2,3,IF(B4=B1-3,5))

Because you haven't defined what to do if neither of these
conditions is met, by default, the formula would return
FALSE.

If you only want to define these 2 conditions and have
nothing returned if these conditions are not met, this
will leave B2 blank:

=IF(B4=B1-2,3,IF(B4=B1-3,5,""))

Or if you'd rather have a zero returned:

=IF(B4=B1-2,3,IF(B4=B1-3,5,0))

Hope this helps!

Biff
 
The OP did respond privately and his problem was as I had
suspected.Instead of expressing B4=B1-2, he used B4<B1 and
didn't include the -2. The solution included nested IF's
and a defined value if false.

=IF(sheet1!B4>sheet1!B1,1,IF(sheet1!B4=sheet1!B1,2,IF
(sheet1!B4=sheet1!B1-1,3,IF(sheet1!B4=sheet1!B1-2,4,IF
(sheet1!B4=sheet1!B1-3,5,0)))))

You might think that using a LOOKUP would have been better
however in this case that would not be true. The same
LOOKUP value would have a different return value in
different cells.

Biff
 
Back
Top