Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with nested formula
Hi,
Could you please help me with a formula for the following situation: I have the amount of 20450. (1-10000) at 30% (10001-20000) at 27% (20001-30000) at 24% (30001-40000) at 21% (Over 40001) at 18% But if the result is less than 0 then I need the cell to show 0. Is there a formula for this? Thanks in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with nested formula
Check out this link to J.E. McGimpsey's web page on this very subject:
http://www.mcgimpsey.com/excel/variablerate.html -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Rykar2" wrote in message ... Hi, Could you please help me with a formula for the following situation: I have the amount of 20450. (1-10000) at 30% (10001-20000) at 27% (20001-30000) at 24% (30001-40000) at 21% (Over 40001) at 18% But if the result is less than 0 then I need the cell to show 0. Is there a formula for this? Thanks in advance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with nested formula
Set up this table somewhere on your sheet (eg X1:Y5):
1 30% 10001 27% 20001 24% 30001 21% 40001 18% Then, assuming your value of 20450 is in A1, use this formula to return the appropriate percentage: =IF(A1<1,0,VLOOKUP(A1,X1:Y5,2)) Hope this helps. Pete On May 16, 1:08 am, Rykar2 wrote: Hi, Could you please help me with a formula for the following situation: I have the amount of 20450. (1-10000) at 30% (10001-20000) at 27% (20001-30000) at 24% (30001-40000) at 21% (Over 40001) at 18% But if the result is less than 0 then I need the cell to show 0. Is there a formula for this? Thanks in advance. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with nested formula
With the base value in cel A1
Try this: B1: =IF(A10,33%-MIN(CEILING(A1/10000,1),4)*0.03,0) Does that help? *********** Regards, Ron XL2002, WinXP "Rykar2" wrote: Hi, Could you please help me with a formula for the following situation: I have the amount of 20450. (1-10000) at 30% (10001-20000) at 27% (20001-30000) at 24% (30001-40000) at 21% (Over 40001) at 18% But if the result is less than 0 then I need the cell to show 0. Is there a formula for this? Thanks in advance. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with nested formula
Thanks for you answers but I will explain different:
I was entering the result manually and this is the end result: I have the amount of 20450 in A1. (1-10000) at 30% = 30000 (10001-20000) at 27% = 27000 (20001-30000) at 24% = 108 (30001-40000) at 21% (Over 40001) at 18% Total = 57108 Ron and Pete if you try this with that formula you will notice the result is 0.24. In this case I need the first 10K calculated at 30%, the second 10K calculated at 27% and so on, and at the end I am adding that column to get the total. Now, you can see that the line for 21% and 18% do not apply so I need the result to be 0. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with nested formula
(1-10000) at 30% = 30000
Hi. Just to mention... =10000*30% = 3000 I was thinking the solution might be 5808. Here's my guess... =MIN(3000 + 0.18*A1,1800 + 0.21*A1, 900 + 0.24*A1,300 + 0.27*A1, 0.3*A1) -- HTH Dana DeLouis Windows XP & Excel 2007 "Rykar2" wrote in message ... Thanks for you answers but I will explain different: I was entering the result manually and this is the end result: I have the amount of 20450 in A1. (1-10000) at 30% = 30000 (10001-20000) at 27% = 27000 (20001-30000) at 24% = 108 (30001-40000) at 21% (Over 40001) at 18% Total = 57108 Ron and Pete if you try this with that formula you will notice the result is 0.24. In this case I need the first 10K calculated at 30%, the second 10K calculated at 27% and so on, and at the end I am adding that column to get the total. Now, you can see that the line for 21% and 18% do not apply so I need the result to be 0. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with nested formula
Ah, I thought you meant that you had only one percentage which applied
to the value. As your percentages are compounded, the link to JE's site that Ragdyer gave you earlier, i.e.: http://www.mcgimpsey.com/excel/variablerate.html explains very well how to accomplish this, either using a single formula or with tables. Hope this helps. Pete On May 16, 3:33 am, Rykar2 wrote: Thanks for you answers but I will explain different: I was entering the result manually and this is the end result: I have the amount of 20450 in A1. (1-10000) at 30% = 30000 (10001-20000) at 27% = 27000 (20001-30000) at 24% = 108 (30001-40000) at 21% (Over 40001) at 18% Total = 57108 Ron and Pete if you try this with that formula you will notice the result is 0.24. In this case I need the first 10K calculated at 30%, the second 10K calculated at 27% and so on, and at the end I am adding that column to get the total. Now, you can see that the line for 21% and 18% do not apply so I need the result to be 0. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with nested formula
Hi Dana, thanks for the correction, that's what I meant. :^)
"Dana DeLouis" wrote: (1-10000) at 30% = 30000 Hi. Just to mention... =10000*30% = 3000 I was thinking the solution might be 5808. Here's my guess... =MIN(3000 + 0.18*A1,1800 + 0.21*A1, 900 + 0.24*A1,300 + 0.27*A1, 0.3*A1) -- HTH Dana DeLouis Windows XP & Excel 2007 "Rykar2" wrote in message ... Thanks for you answers but I will explain different: I was entering the result manually and this is the end result: I have the amount of 20450 in A1. (1-10000) at 30% = 30000 (10001-20000) at 27% = 27000 (20001-30000) at 24% = 108 (30001-40000) at 21% (Over 40001) at 18% Total = 57108 Ron and Pete if you try this with that formula you will notice the result is 0.24. In this case I need the first 10K calculated at 30%, the second 10K calculated at 27% and so on, and at the end I am adding that column to get the total. Now, you can see that the line for 21% and 18% do not apply so I need the result to be 0. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested Formula - HELP | Excel Worksheet Functions | |||
Nested formula | Excel Discussion (Misc queries) | |||
Nested Formula help | Excel Worksheet Functions | |||
Nested Formula | Excel Worksheet Functions | |||
Nested Formula | Excel Worksheet Functions |