Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In Over My Head | Excel Discussion (Misc queries) | |||
over my head | Excel Discussion (Misc queries) | |||
Head to head | Excel Discussion (Misc queries) | |||
Cant get my head around this one | Excel Worksheet Functions | |||
Over my head on this one... | Excel Worksheet Functions |