Help!!!!im So Confused

[james1bo]

im trying to set up a formula to give me a number based on the criteri
of a cell. My goal is setting up a work sheet for a bonus plan. Bonu
are only payed at a higher percentage after a certain amount.

Example.

if bill gates sales are 1 billion dollars or more, then his bonus i
15%... or ... if bill gates sales are 1 billion dollars or less, hi
bonus will be 10%... ect ect...

any good Office Heads out there that can give this frustrated hardwar
engineer advice???? I can build them, trouble shoot them,, and repai
them,, but when it comes to computer software and excell formulas,,, i
might as well be an Apple... lol
thanks for any help.
Ji

 

[kvwood]

Jim,

Did you figure this out, or are you still working on it?

Kevin

 

[aaron.kempf]

use a database for complex math like this

excel is a disease

 

[Biff]

complex math?

LOL

Biff

 

[Biff]

Hi!

You have a conflict in logic here!

If Bill Gates sales are $1 billion his bonus could be either/or by your
description!

The best solution to this depends on how many conditions there are. Just
using the 2 you posted:

Let's make the levels logic friendly:

1 billion or greater = 15%
Less than 1 billion = 10%

A1 = sales

Formula to calculate bonus:

=IF(A1>=1000^3,A1*0.15,A1*0.1)

Biff

 

[Arvi Laanemets]

Hi

Hi!

You have a conflict in logic here!

If Bill Gates sales are $1 billion his bonus could be either/or by your
description!

The best solution to this depends on how many conditions there are. Just
using the 2 you posted:

Let's make the levels logic friendly:

1 billion or greater = 15%
Less than 1 billion = 10%

A1 = sales

Formula to calculate bonus:

=IF(A1>=1000^3,A1*0.15,A1*0.1)

Or
=A1*(0.1+(A1>=1000000)*0.05)

For
less than 1000 =0%
less than 1000000 =10%
1000000 or greater =15%
=A1*((A1>=1000)*0.1+(A1>=1000000)*0.05)

For
less than 1000 =0%
less than 10000 =2%
less than 100000 =5%
less than 1000000 =10%
1000000 or greater =15%
=A1*((A1>=1000)*0.02+(A1>=10000)*0.03+(A1>=1000)*0.05+(A1>=1000000)*0.05)

etc.