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

1. ## 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.

2. ## Re: Calculation with multiple criteria- CAP Amount, Min. Amount etc.

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.

3. ## Re: Calculation with multiple criteria- CAP Amount, Min. Amount etc.

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

4. ## Re: Calculation with multiple criteria- CAP Amount, Min. Amount etc.

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 %.

5. ## Re: Calculation with multiple criteria- CAP Amount, Min. Amount etc.

Originally Posted by Bo_Ry
These numbers I am looking for, but please do without Solver.

6. ## Re: Calculation with multiple criteria- CAP Amount, Min. Amount etc.

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?

7. ## Re: Calculation with multiple criteria- CAP Amount, Min. Amount etc.

Glenn, you can make it short, say 20 rows
No, that's your job. In future, please supply data samples, not huge workbooks. Thanks.

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!

8. ## Re: Calculation with multiple criteria- CAP Amount, Min. Amount etc.

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1