Scaled bonus help

**newalker91** · 08-26-2012, 12:07 AM

I was hoping that someone could assist me with a formula that I'm trying to work out. The purpose of this formula is to calculate my payout based on my percent to goal. I've never had an Excel course and have been trying to construct this based on research, but it keeps giving an error 508 so I'm definitely doing something incorrect.

On my spreadsheet, cell B34 has my calculated % to goal and it is to be referenced in this formula.

The way that the formula should calculate is as follows:

$330 * % to goal * accelerator

Accelerators are

<50% - 0.00
50%-69.999% - 0.20
70%-79.999% - 0.50
80%-89.999% - 0.70
90%-109.999% - 1.00
110-129.999% - 1.15
130-159.999% - 1.50
160-199.999% - 1.17
200% and up - 2.00

So far I have:

=IF(B34<.5,(B34*330*0),IF(B34>=.5,(B34*330*0.2),IF(B34>=.7,(B34*330*0.5),IF(B34>=.8,(B34*330*0.7),IF(B34>=.9,(B34*330*1),IF(B34>=1.1,(B34*330*1.15,)IF(B34>=1.3,(B34*330*1.5),IF(B34>=1.6,(B34*330*1.75),IF(B34>=2,(B34*330*2))))))))))

If anyone can assist, I would appreciate it.

**MarvinP** · 08-26-2012, 12:17 AM

Hi newalker91 and welcome to the forum,

Find the attached with a VLookup formula that you need.

**newalker91** · 08-26-2012, 12:35 AM

That's a huge help. The only thing I'm having trouble with is getting it to scale properly if it's under 50% to goal.

Thank you very much!

**MarvinP** · 08-26-2012, 12:42 AM

Did you expect them to get a bonus if they sold less than .50 of Goal? That is why it goes to ZERO for the bonus.

Use a few example numbers to see if your formula is correct. Do you want to calculate the BONUS or final pay?

**icestationzbra** · 08-26-2012, 12:43 AM

what do you mean by "scale if under 50%"?

until then, here's another approach:

**newalker91** · 08-26-2012, 12:47 AM

Originally Posted by MarvinP

Did you expect them to get a bonus if they sold less than .50 of Goal? That is why it goes to ZERO for the bonus.

Use a few example numbers to see if your formula is correct. Do you want to calculate the BONUS or final pay?

I want it to calculate the final payout. The 330 * % to goal * accelerator. I just noticed that the VLOOKUP is only working if the % to goal is exactly matched with one in the reference table. It isn't taking into consideration that someone may be at 51% and multiply by .2.

Originally Posted by icestationzbra

what do you mean by "scale if under 50%"?

until then, here's another approach:

It doesn't seem to be multiplying by 0 if the % to goal is under 50%.

EDIT: Ice, your's works perfectly! Thank you!

Scaled bonus help

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Scaled bonus help

Re: Scaled bonus help

Re: Scaled bonus help

Re: Scaled bonus help

Re: Scaled bonus help

Re: Scaled bonus help

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions