Bonus formula help needed

Hi. I am compiling a workbook for managers to use to easily calculate bonuses for employees. I have figured out most of it but am having a problem where two different criteria need to be viewed for one formula:

Call score bonus %
91+ 8
85-93.9 6
86-87.9 3
<86 0

rollovers Bonus 5
10+ 2
<9 0, but the bonus numbers on call scores above bump up to 10, 7, and 3 respectively if a zero is received here.

I have attempted a number of If, And, Count, etc. and can get it to work part-way, but not for every different possibility. Any assistance would be greatly appreciated.

Not clear what difficulty you are experiencing. Do you have two criteria,
call score and rollovers, and that you want to calculate each bonus and add
them. And what does that bump-up statement mean? Perhaps an example would
help.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"jiffy97" <[email protected]> wrote in
message news:[email protected]...
>
> Hi. I am compiling a workbook for managers to use to easily calculate
> bonuses for employees. I have figured out most of it but am having a
> problem where two different criteria need to be viewed for one
> formula:
>
> Call score bonus %
> 91+ 8
> 85-93.9 6
> 86-87.9 3
> <86 0
>
> rollovers Bonus 5
> 10+ 2
> <9 0, but the bonus numbers on call scores above bump up
> to 10, 7, and 3 respectively if a zero is received here.
>
> I have attempted a number of If, And, Count, etc. and can get it to
> work part-way, but not for every different possibility. Any assistance
> would be greatly appreciated.
>
>
> --
> jiffy97
> ------------------------------------------------------------------------
> jiffy97's Profile:
http://www.excelforum.com/member.php...o&userid=29443
> View this thread: http://www.excelforum.com/showthread...hreadid=491527
>

Example:
If an employee has 10+ rollovers they earn 2% bonus; 91+ on call scores would earn an 8% bonus for a total of 10% bonus.
If an employee has <9 rollovers they get 0% for that bonus portion. Rollovers are not something an employee has complete control over, so management has said if you get <9 rollovers we will bump up the bonus for call scores to account for the lost bonus %.
So a 91+ call score would now earn a 10% bonus on its own.



I need a formula that will look at the rollover score first and if it is under <10 refer to a higher bonus number based on the call scores.

Does this help?

Your ranges overlap. Is that correct?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"jiffy97" <[email protected]> wrote in
message news:[email protected]...
>
> Example:
> If an employee has 10+ rollovers they earn 2% bonus; 91+ on call
> scores would earn an 8% bonus for a total of 10% bonus.
> If an employee has <9 rollovers they get 0% for that bonus portion.
> Rollovers are not something an employee has complete control over, so
> management has said if you get <9 rollovers we will bump up the bonus
> for call scores to account for the lost bonus %.
> So a 91+ call score would now earn a 10% bonus on its own.
>
>
>
> I need a formula that will look at the rollover score first and if it
> is under <10 refer to a higher bonus number based on the call scores.
>
> Does this help?
>
>
> --
> jiffy97
> ------------------------------------------------------------------------
> jiffy97's Profile:
http://www.excelforum.com/member.php...o&userid=29443
> View this thread: http://www.excelforum.com/showthread...hreadid=491527
>