Home |
Search |
Today's Posts |
#1
|
|||
|
|||
formula help
Hi,
I need help with this formula A1 = value x IF A1 <= 30000 Then A2=420.00 IF A1 =30000, but <=45000 then A2=(A2-30000/1000)*7.25+420.00 IF A1=45000, but <100000 then A2=(A2-45000/1000)*6+528.75 And so on Could you get me past these steps? If I type 30000 I should get 420 for an answer If type 40000 my answer should be 492.50 If type 50000 my answer should be 558.75 Thanks |
#2
|
|||
|
|||
Hi
You cannot let A2 = A2 - 30000 or whatever. A2 = A2. To solve your problem, you need to use another cell, in which a formula may insert a value, and then let A2 = the value of that cell - 30000/1000 etc -- ve_2nd_at. Randburg, Gauteng, South Africa "dom" wrote: Hi, I need help with this formula A1 = value x IF A1 <= 30000 Then A2=420.00 IF A1 =30000, but <=45000 then A2=(A2-30000/1000)*7.25+420.00 IF A1=45000, but <100000 then A2=(A2-45000/1000)*6+528.75 And so on Could you get me past these steps? If I type 30000 I should get 420 for an answer If type 40000 my answer should be 492.50 If type 50000 my answer should be 558.75 Thanks |
#3
|
|||
|
|||
can you get more detailed
"Kassie" wrote: Hi You cannot let A2 = A2 - 30000 or whatever. A2 = A2. To solve your problem, you need to use another cell, in which a formula may insert a value, and then let A2 = the value of that cell - 30000/1000 etc -- ve_2nd_at. Randburg, Gauteng, South Africa "dom" wrote: Hi, I need help with this formula A1 = value x IF A1 <= 30000 Then A2=420.00 IF A1 =30000, but <=45000 then A2=(A2-30000/1000)*7.25+420.00 IF A1=45000, but <100000 then A2=(A2-45000/1000)*6+528.75 And so on Could you get me past these steps? If I type 30000 I should get 420 for an answer If type 40000 my answer should be 492.50 If type 50000 my answer should be 558.75 Thanks |
#4
|
|||
|
|||
This might help
http://www.mcgimpsey.com/excel/variablerate.html it looks like you are calculation commissions -- Regards, Peo Sjoblom (No private emails please) "dom" wrote in message ... can you get more detailed "Kassie" wrote: Hi You cannot let A2 = A2 - 30000 or whatever. A2 = A2. To solve your problem, you need to use another cell, in which a formula may insert a value, and then let A2 = the value of that cell - 30000/1000 etc -- ve_2nd_at. Randburg, Gauteng, South Africa "dom" wrote: Hi, I need help with this formula A1 = value x IF A1 <= 30000 Then A2=420.00 IF A1 =30000, but <=45000 then A2=(A2-30000/1000)*7.25+420.00 IF A1=45000, but <100000 then A2=(A2-45000/1000)*6+528.75 And so on Could you get me past these steps? If I type 30000 I should get 420 for an answer If type 40000 my answer should be 492.50 If type 50000 my answer should be 558.75 Thanks |
#5
|
|||
|
|||
You will also need to watch your evaluations. You have overlaps with 30,000
and 45,000 fitting into 2 categories each as written in your example below. It may not do what you expect. You need to drop the equals (=) on the 2nd and 3rd, or raise the bar by 1, i.e., =30,001 and =45,001. If you have additional tiers beyond 100,000 they will have to be added. You can only go so far with 1 formula. How many other tiers do you have? If you type the sales figure (?) in A1 and put this formula in A2, it produces the commisions you listed for 30,000, 40,000 and 50,000. =IF(A1<30001,420,IF(A1<45001,(((A1-30000)/1000)*7.25)+420,(((A1-45000)/1000)*6)+528.75)) Roy -- (delete .nospam) "dom" wrote: Hi, I need help with this formula A1 = value x IF A1 <= 30000 Then A2=420.00 IF A1 =30000, but <=45000 then A2=(A2-30000/1000)*7.25+420.00 IF A1=45000, but <100000 then A2=(A2-45000/1000)*6+528.75 And so on Could you get me past these steps? If I type 30000 I should get 420 for an answer If type 40000 my answer should be 492.50 If type 50000 my answer should be 558.75 Thanks |
#6
|
|||
|
|||
two more tiers if possible
"Roy Wagner" wrote: You will also need to watch your evaluations. You have overlaps with 30,000 and 45,000 fitting into 2 categories each as written in your example below. It may not do what you expect. You need to drop the equals (=) on the 2nd and 3rd, or raise the bar by 1, i.e., =30,001 and =45,001. If you have additional tiers beyond 100,000 they will have to be added. You can only go so far with 1 formula. How many other tiers do you have? If you type the sales figure (?) in A1 and put this formula in A2, it produces the commisions you listed for 30,000, 40,000 and 50,000. =IF(A1<30001,420,IF(A1<45001,(((A1-30000)/1000)*7.25)+420,(((A1-45000)/1000)*6)+528.75)) Roy -- (delete .nospam) "dom" wrote: Hi, I need help with this formula A1 = value x IF A1 <= 30000 Then A2=420.00 IF A1 =30000, but <=45000 then A2=(A2-30000/1000)*7.25+420.00 IF A1=45000, but <100000 then A2=(A2-45000/1000)*6+528.75 And so on Could you get me past these steps? If I type 30000 I should get 420 for an answer If type 40000 my answer should be 492.50 If type 50000 my answer should be 558.75 Thanks |
#7
|
|||
|
|||
By nesting 1-2 more if's, it should work. What are the additional tiers and
examples of commissions for them? Roy -- (delete .nospam) "dom" wrote: two more tiers if possible "Roy Wagner" wrote: You will also need to watch your evaluations. You have overlaps with 30,000 and 45,000 fitting into 2 categories each as written in your example below. It may not do what you expect. You need to drop the equals (=) on the 2nd and 3rd, or raise the bar by 1, i.e., =30,001 and =45,001. If you have additional tiers beyond 100,000 they will have to be added. You can only go so far with 1 formula. How many other tiers do you have? If you type the sales figure (?) in A1 and put this formula in A2, it produces the commisions you listed for 30,000, 40,000 and 50,000. =IF(A1<30001,420,IF(A1<45001,(((A1-30000)/1000)*7.25)+420,(((A1-45000)/1000)*6)+528.75)) Roy -- (delete .nospam) "dom" wrote: Hi, I need help with this formula A1 = value x IF A1 <= 30000 Then A2=420.00 IF A1 =30000, but <=45000 then A2=(A2-30000/1000)*7.25+420.00 IF A1=45000, but <100000 then A2=(A2-45000/1000)*6+528.75 And so on Could you get me past these steps? If I type 30000 I should get 420 for an answer If type 40000 my answer should be 492.50 If type 50000 my answer should be 558.75 Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
put formula results into a different cell if it is empty | Excel Worksheet Functions | |||
how do i write a formula and keep in in formula form, so it DOESN. | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |