On 27 Jul., 14:03, "Niek Otten" wrote:
You can of course also leave the percentages the same and change the
threshold values, with the same technique. In that case, apply the
multiplication by (1+$B$19) to C19 and down and adjust your VLOOKUP formula

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Niek Otten" wrote in message

...

In F4 (and down):

=VLOOKUP(D4,$C$19:$E$23,3)

In G4 etc:

=F4*C4

In G17:

=C1-G15

In B19:

0.01

In E19 and down:

=D19*(1+$B$19)

Goalseek G17 to be zero by changing cell B19

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"ran58" wrote in message
....
I am currently confronted with a bonus participation scheme problem as
follows:

Assumptions:
Column B4:B13 Names of 10 employees Employee1:Employee10
Column C4:C13 Annual salary of each afore-mentioned employee
Cell C1 = Average Bonus-Amount of 2.5% of the total annual salaries
(SUM(C4:C13)/100*2.5)
Column D4:D13 Individual Performance Assessment rating from 0.0
(minimum) to 4.0 (maximum)
Column F4:F13 Individual Bonus-Amount as a percentage rate
Column G4:G13 Individual Bonus-Amount
Cell G15 = Cell C1 (i.e. SUM(G4:G13) = C1)

Depending on the individual performance assessment, a bonus factor
must be applied in order to calculate an exponentially increasing
individual bonus amount, e.g.

Assessment - Bonus-Amount as a percentage of the annual salary

B19 = 0.0 - C19 = 0.00%
B20 = 1.0 - C20 = 0.63%
B21 = 2.0 - C21 = 1.56%
B22 = 3.0 - C22 = 2.81%
B23 = 4.0 - C23 = 5.00%

Restrictions: The total average bonus amount must be 2.5% of the total
annual salaries, see above Cell G15.

How can I make use of the GOAL SEEK function in order to
a) vary the percentage rates above (C19:C23) AND
b) restrict the total amount of bonus to 2.5% (C1=G15)?

Many thanks for any ideas in this respect!

Thank you so much, Niek Otten, for your guidance. This works fine....
There is however one additional factor, i.e. a further restriction, to
consider:
The maximum bonus percentage rate may not exceed 5.00%. If I e.g.
change value C20 to 0.00% (equal to C19 = 0.00%), then the remaining
GOAL SEEK calculated values in E21 (and down) change e.g. from
1.56% to 1.95%
2.81% to 3.52%
5.00% to 6.26%
However, the maximum bonus percentage rate may not exceed 5.00%.
How can this restriction be considered? Once again, many thanks for
your advice!

René