I'm over my head with this function
 

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

I'm over my head with this function
 

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



" wrote:

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

I'm over my head with this function
 

See this:

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

--
Biff
Microsoft Excel MVP


wrote in message
...
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

I'm over my head with this function
 

On Mar 13, 5:51 pm, " wrote:
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 -----

On Mar 13, 5:51*pm, " wrote:
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

I'm over my head with this function
 

On Mar 13, 9:13*pm, "T. Valko" wrote:
See this:

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

--
Biff
Microsoft Excel MVP

wrote in message

...

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

Good article, thanks.

I'm over my head with this function
 

On Mar 14, 1:40*am, joeu2004 wrote:
On Mar 13, 5:51 pm, " wrote:

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

On Mar 13, 5:51*pm, " wrote:

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

Thanks for catching my errors.