Calculation with multiple criteria- CAP Amount, Min. Amount etc.

Hi Experts,

I am working on a bonus calculator and for the same seeks your expertise.

Attached is the Bonus Calculator, I have below challenges:

1. Min. bonus should be 500
2. CAP amount is given, say: 1,000,000

The excess amount (above cap) should be adjusted from the staff but make sure their salary is not coming under 500.

Your sample.... at more than 3000 rows slightly exceeds our guidelines (10-20 rows). What's worse is that there are NO manually calculated expected answers for us to check our formulae against.

Some expected answers, pelase???

Please try Solver

N2
=Q2-SUM(N4:N3053)

N4:Nxx
=IF(K4="No",0,MAX($Q$12,LOOKUP(G4,$P$10:$P$11,$Q$10:$Q$11-1+$Q$3/$L$2)*H4))

Solver Set objection
$N$2 = 0

by changing Q3

use GRG nonlinear method

Originally Posted by Glenn Kennedy

Your sample.... at more than 3000 rows slightly exceeds our guidelines (10-20 rows).

Glenn, you can make it short, say 20 rows. Try to change the CAP to 10,000 and min. based on logic.
I dont know the amount, so can't put result manually.
What I am looking is, an adjustment in amount from everyone to be made based on the distribution % not a random or equal %.

Originally Posted by Bo_Ry

Please try Solver

These numbers I am looking for, but please do without Solver.

Originally Posted by Bo_Ry

Solver Set objection
$N$2 = 0

by changing Q3

use GRG nonlinear method

Thanks a ton.
What value you put in Q3?

Glenn, you can make it short, say 20 rows

No, that's your job. In future, please supply data samples, not huge workbooks. Thanks.

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

Exactly, Ali. Why do you expect ME to make YOUR sample sheet? Do you employ me? Pay me? Own me?

No, sorry. I won't. Forget it.

YOU do it, and supply some expected answers.