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-03T15:13:50+00:00

    Ashish, This is the simplest solution. The only correction that after D20 it is not a dot but a comma in above formula. Thanks Dear

    Was this answer helpful?

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

    Hi,

    Thank you for pointing that out - I missed that line.  The revised formula would become

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

    B1:C1 has 0's and other entries shift down.

    Was this answer helpful?

    0 comments