Choose Value on Conditional basis

  • Thread starter Thread starter Murtaza
  • Start date Start date
M

Murtaza

Scenario:
======
I have a to calculate incentive of about 100 employees based on their %Ach
Column, we have already made a *Table of Incentive Slots* i.e.if %Ach falls
between (>=95% to <=98%) he/she will be qualified for $6500 ...and so on

Required:
=======
I need a forumla or way to easily calculate the incentive amount of each
individual.


Details:
=====
The Layout of working sheet is below:

Name Title %Ach IncentiveAmount
Mr. xyz abc 98.7 ?? (this should be equal to 13000)


The layout of *Table of Incentive Slots* is below:

%AchMin %AchMax Incentive Amount
=95% <=98% 6500
98% <100% 13000
Click to expand...


Any help willl be highly appreciated
Murtaza
 
In
in your incetive table enter in column A only the lower
boundary. So something like
0.95
0.98
1.00

Now use the following formula in D2 of your working sheet:
=INDEX('incentive_sheet'!$C$1:$C$20,MATCH
(C2,'incentive_sheet'!$A$1:$A$20,0))
 
In
in your incetive table enter in column A only the lower
boundary. So something like
0.95
0.98
1.00

Now use the following formula in D2 of your working sheet:
=INDEX('incentive_sheet'!$C$1:$C$20,MATCH
(C2,'incentive_sheet'!$A$1:$A$20,1))
 
Hi
ignore this message. Use '1' as the third parameter of
mATCH (as in my other post)
 
Back
Top