Share via

IF Function Incentive Calculation

Anonymous
2013-03-02T10:11:28+00:00

Column A      Column B  Column C 

1.      A1                 100%         1200

  1.      A2                 110%         1600
  2.      A3                 120%         2200

Then for another cell D20 the Conditions are as following:

If D20<B1 then 0(Zero)

If D20>=B1 but < than B2 then C1

If D20>B2   but < than B3 then C2

If D20>B3 then C3

The Desired Answer is in form

=If(??????)

Or any other way to calculate????

Regards

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Ashish Mathur 101.9K Reputation points Volunteer Moderator
2013-03-03T22:56:43+00:00

Hi,

You are welcome.  If my solution helped, please mark it as Answer.

Was this answer helpful?

0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-03-02T23:47:13+00:00

    =VLOOKUP(D20,$B$1:$C$3,2,1)

    That doesn't return 0 if D20<B1, as requested.

    Was this answer helpful?

    0 comments
  2. Ashish Mathur 101.9K Reputation points Volunteer Moderator
    2013-03-02T23:30:53+00:00

    Hi,

    Try this

    =VLOOKUP(D20,$B$1:$C$3,2,1)

    Hope this helps.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2013-03-02T11:43:13+00:00

    This is Amazing! This was exactly I was Looking For. Thank You Very Much Jasper P

    Was this answer helpful?

    0 comments
  4. Anonymous
    2013-03-02T11:17:51+00:00

    Try this:

    =IF(D20>=B3,C3,IF(D20>=B2,C2,IF(D20>=B1,C1,0)))

    Was this answer helpful?

    0 comments