I'm over my head with this function

[goldcomac]

As the subject line says, I'm over my head. I want to put a number
into cell A1 that yields a result in cell A2, but there are a up to
three factors involved. Maybe I'm WAY over my head.

Example:

If Cell A is <=1000000 then the result = 5% of the number
If Cell A is >1000000 and <2000000 then the result needs to equal 5%
on the first 1,000,000 (or what ever number is in there) plus 4% on
what ever number is there
If Cell A is >3000000 then the result needs to equal 5% on the first
1,000,000, plus 4% on the next 1,000,000, and 3% on the remaining
number

In english:
Cell A = 1,000,000 then the result is 50,000 (1,000,000 * 5% = 50,000)
Cell A = 2,000,000 then the result is 90,000 (1,000,000 * 4% = 40,000
+ 50,000)
Cell A is 3,000,000 then the result is 120,000 (1,000,000 * 3% =
30,000 + 40,000 + 50,000)

Can this be done or do I have to write this out as three different
formulas and have a simple SUM total all three cells?

I hope I'm making sense.

Thanks.

Chip

 

[Sheeloo]

Us
=IF(A1<=1000000,A1*0.05,IF(A1<=2000000,50000+(A1-1000000)*0.04,130000+(A1-2000000)*0.03))

 

[T. Valko]

See this:

http://mcgimpsey.com/excel/variablerate.html

 

[joeu2004]

If Cell A is <=1000000 then the result = 5% of the number
If Cell A is >1000000 and <2000000 then the result needs to equal 5%
on the first 1,000,000 (or what ever number is in there) plus 4% on
what ever number is there
If Cell A is >3000000 then the result needs to equal 5% on the first
1,000,000, plus 4% on the next 1,000,000, and 3% on the remaining
number

You should pay close attention to "<" and "<=" constraints. According
to your numerical example, you mean <=2,000,000.

And you have a huge hole between 2,000,000 and 3,000,000. I suspect
you mean: if <=1,000,000, then 5%; if <=2,000,000, then 5% on the
first 1,000,000 and 4% on the remainder; otherwise, 5% on the first
1,000,000, 4% on the second 1,000,000 and 3% on the remainder, which
is any amount over 2,000,000. At least, that matches your numerical
example.

As you may know, there are usually many ways to express a solution.
The "best" solution depends on the degree of flexibility you need. I
suspect the simplest solution that you will readily understand is:

=IF(A1<=1000000, A1*5%,
IF(A1<=2000000, 50000+(A1-1000000)*4%, 90000+(A1-2000000)*3%))



----- original posting -----

 

[goldcomac]

See this:

http://mcgimpsey.com/excel/variablerate.html

Good article, thanks.

 

[goldcomac]

You should pay close attention to "<" and "<=" constraints.  According
to your  numerical example, you mean <=2,000,000.

And you have a huge hole between 2,000,000 and 3,000,000.  I suspect
you mean:  if <=1,000,000, then 5%; if <=2,000,000, then 5% on the
first 1,000,000 and 4% on the remainder; otherwise, 5% on the first
1,000,000, 4% on the second 1,000,000 and 3% on the remainder, which
is any amount over 2,000,000.  At least, that matches your numerical
example.

As you may know, there are usually many ways to express a solution.
The "best" solution depends on the degree of flexibility you need.  I
suspect the simplest solution that you will readily understand is:

=IF(A1<=1000000, A1*5%,
    IF(A1<=2000000, 50000+(A1-1000000)*4%, 90000+(A1-2000000)*3%))

----- original posting -----

Thanks for catching my errors.