Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation question
What would be the best way to calculate a total based using criteria like the
following: I want to take varying totals of investments under management and calculate costs related using .45% of first 100,000, .30% of next 400,000, .20% of next 500,000 and .10% of anything over 1,000,000. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation question
If A1 contains your total investment, you can use a formula like:
=MIN(MAX(0,A1),100000)*0.45% + MIN(MAX(0,A1-100000),400000)*0.3% + MIN(MAX(0,A1-500000),500000)*0.2% + MAX(0,A1-1000000)*0.1% Stephane. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation question
On Jul 21, 7:22 am, Schorn wrote:
What would be the best way to calculate a total based using criteria like the following: I want to take varying totals of investments under management and calculate costs related using .45% of first 100,000, .30% of next 400,000, .20% of next 500,000 and .10% of anything over 1,000,000. "Best" way? Well, that's debatable. There are many ways to accomplish this. Arguably, the simplest formula might be (if the investment total is in A1): =45%*min(A1,100000) + max(0,30%*min(A1-100000,400000)) + max(0,20%*min(A1-500000,500000)) + max(0,10%*(A1-1000000)) Arguably, a formula that is easier to change as needed (including adding additional breakpoints) would rely on a table. The formula might be: =(A1-vlookup(A1,table,1))*vlookup(A1,table,2) + vlookup(A1,table,3) where "table" is the following in A2:C5 : A2: 0 B2: 45% C2: 0 A3: 100000 B3: 30% C3: =C2 + B2*(A3-A2) A4: =A3+400000 B4: 20% C4: =C3 + B3*(A4-A3) A5: =A4+500000 B5: 10% C5: =C4 + B4*(A5-A4) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation question
Joeu2004,
Sorry to inform you that your formula returns a wrong result. Test it with A1 = 1 for example, you get a negative amount (I corrected the first percentage to 0.45%). It took me a while to write a proper formula, as you have to combine MIN and MAX, look at my post above yours in this thread. Your table approach is very good, again check your formulas in column C as they return negative values for amounts lower than the threshold. Use MAX(0, your formula) to solve it. Stephane "joeu2004" wrote: On Jul 21, 7:22 am, Schorn wrote: What would be the best way to calculate a total based using criteria like the following: I want to take varying totals of investments under management and calculate costs related using .45% of first 100,000, .30% of next 400,000, .20% of next 500,000 and .10% of anything over 1,000,000. "Best" way? Well, that's debatable. There are many ways to accomplish this. Arguably, the simplest formula might be (if the investment total is in A1): =45%*min(A1,100000) + max(0,30%*min(A1-100000,400000)) + max(0,20%*min(A1-500000,500000)) + max(0,10%*(A1-1000000)) Arguably, a formula that is easier to change as needed (including adding additional breakpoints) would rely on a table. The formula might be: =(A1-vlookup(A1,table,1))*vlookup(A1,table,2) + vlookup(A1,table,3) where "table" is the following in A2:C5 : A2: 0 B2: 45% C2: 0 A3: 100000 B3: 30% C3: =C2 + B2*(A3-A2) A4: =A3+400000 B4: 20% C4: =C3 + B3*(A4-A3) A5: =A4+500000 B5: 10% C5: =C4 + B4*(A5-A4) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation question
On Jul 21, 12:32 pm, Stephane Quenson
wrote: "joeu2004" wrote: =45%*min(A1,100000) + max(0,30%*min(A1-100000,400000)) + max(0,20%*min(A1-500000,500000)) + max(0,10%*(A1-1000000)) Sorry to inform you that your formula returns a wrong result. Test it with A1 = 1 for example, you get a negative amount I get 0.45, as expected. The logic of the first term is: use the smaller of A1 or 100000. Since 1 is smaller, min(...) should return 1, which is then multiplied by 45%. The other min(...) terms will return negative numbers, but they are nullified because max(0,...) will return 0 in those cases. I do notice one potentially undesirable behavior. The formula works fine if A1 is an explicit 0. But if A1 is blank, min(A1,100000) returns 100000(!). This can be fixed by replacing A1 with n(A1) in the first term. That is: =45%*min(n(A1),100000) + .... N() is not required in the other terms because Excel treats a blank cell as zero in an arithmetic expression (e.g. A1-100000). On the other hand, if the cell __appears__ blank, but it actually contains a formula that returns the null string ("") sometimes, it might be prudent to replace A1 with N(A1) in all instances. Isn't Excel grand? (Rhetorical.) PS: I did not bother to bullet-proof the formula for A1<0. KISS. The OP says that A1 (in my example) represents the "total of investments under management". Presumably, that is non-negative. But if negative A1 is a possibility, then yes, replacing A1 in the first term with max(0,A1) fixes both problems. That is: =45%*min(max(0,A1),100000) + .... Again, max(0,A1) is required only in the first term because the max(0,min(...)) takes care of the problem in the other terms. Your table approach is very good, again check your formulas in column C as they return negative values for amounts lower than the threshold. Use MAX(0, your formula) to solve it. Again, I did not feel the need to bullet-proof against having a negative "total of investments under management". But if that is desirable, I would simply do: =if(A1<0, 0, (A1-vlookup(A1,table,1))*....) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation question
Errata....
On Jul 21, 11:26 am, I wrote: On Jul 21, 7:22 am, Schorn wrote: I want to take varying totals of investments under management and calculate costs related using .45% of first 100,000, .30% of next 400,000, .20% of next 500,000 and .10% of anything over 1,000,000. [....] =45%*min(A1,100000) + max(0,30%*min(A1-100000,400000)) + max(0,20%*min(A1-500000,500000)) + max(0,10%*(A1-1000000)) [....] where "table" is the following in A2:C5 : A2: 0 B2: 45% C2: 0 A3: 100000 B3: 30% C3: =C2 + B2*(A3-A2) A4: =A3+400000 B4: 20% C4: =C3 + B3*(A4-A3) A5: =A4+500000 B5: 10% C5: =C4 + B4*(A5-A4) Oops: obviously, all percentages should be of the form 0.45% instead of 45%. I didn't see the itsy-bitsy period before all the percentages in the OP's posting. Note to OP: That is why is always best to write 0.45 instead of .45 -- that is, with the "superfluous" leading zero. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation question
Errata....
On Jul 21, 12:32 pm, Stephane Quenson wrote: I corrected the first percentage to 0.45% .... And all the other percentage accordingly. Good pick up! My bad! On Jul 21, 6:22 pm, I wrote: I get 0.45, as expected. Make that 0.0045. Whether or not the OP wants to round and how are unspecified. In any case, my "off by 0." error only changes the magnitude of the result of my formulas, not their correct operation otherwise, I believe. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation question
Sorry joeu2004, I was wrong. Reason is that I am French and we use the comma
as decimal separator and I thought that on the formula max(0,10%... you actually meant 0.10% (what Schorn wanted), and therefore your max function had only one argument for me, returning a negative value. "joeu2004" wrote: On Jul 21, 12:32 pm, Stephane Quenson wrote: "joeu2004" wrote: =45%*min(A1,100000) + max(0,30%*min(A1-100000,400000)) + max(0,20%*min(A1-500000,500000)) + max(0,10%*(A1-1000000)) Sorry to inform you that your formula returns a wrong result. Test it with A1 = 1 for example, you get a negative amount I get 0.45, as expected. The logic of the first term is: use the smaller of A1 or 100000. Since 1 is smaller, min(...) should return 1, which is then multiplied by 45%. The other min(...) terms will return negative numbers, but they are nullified because max(0,...) will return 0 in those cases. I do notice one potentially undesirable behavior. The formula works fine if A1 is an explicit 0. But if A1 is blank, min(A1,100000) returns 100000(!). This can be fixed by replacing A1 with n(A1) in the first term. That is: =45%*min(n(A1),100000) + .... N() is not required in the other terms because Excel treats a blank cell as zero in an arithmetic expression (e.g. A1-100000). On the other hand, if the cell __appears__ blank, but it actually contains a formula that returns the null string ("") sometimes, it might be prudent to replace A1 with N(A1) in all instances. Isn't Excel grand? (Rhetorical.) PS: I did not bother to bullet-proof the formula for A1<0. KISS. The OP says that A1 (in my example) represents the "total of investments under management". Presumably, that is non-negative. But if negative A1 is a possibility, then yes, replacing A1 in the first term with max(0,A1) fixes both problems. That is: =45%*min(max(0,A1),100000) + .... Again, max(0,A1) is required only in the first term because the max(0,min(...)) takes care of the problem in the other terms. Your table approach is very good, again check your formulas in column C as they return negative values for amounts lower than the threshold. Use MAX(0, your formula) to solve it. Again, I did not feel the need to bullet-proof against having a negative "total of investments under management". But if that is desirable, I would simply do: =if(A1<0, 0, (A1-vlookup(A1,table,1))*....) |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation question
Perhaps another option:
=MIN(0.1%*A1+1650,0.2%*A1+650,0.3%*A1+150,0.45%*A1 ) -- HTH :) Dana DeLouis "Schorn" wrote in message ... What would be the best way to calculate a total based using criteria like the following: I want to take varying totals of investments under management and calculate costs related using .45% of first 100,000, .30% of next 400,000, .20% of next 500,000 and .10% of anything over 1,000,000. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation question
On Jul 21, 10:06 pm, "Dana DeLouis" wrote:
Perhaps another option: =MIN(0.1%*A1+1650, 0.2%*A1+650, 0.3%*A1+150, 0.45%*A1) Excellent! I would only suggest an explanation of the "magic" constants. I leave that to you to have the last word on the subject ;-). |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation question
Hi. I have a program that does this for me automatically.
If we were to plot these types of problems, what we would have are 4 straight line intervals. The problem is to calculate the equation of each line (y = a*x+b). We are given the slopes of each line (the 'a). We need to calculate the y-intercepts 'b. As we start from zero, the first one is easy. The intercept is zero. =.0045+a1 For the second line, we need two points to calculate a straint line. Therefore, let's pick two easy x-points. 100000, and 100001 1) (x,y) = (100000,100000*.0045) -(100000,450) 2) (x,y) = (100001,450+.003) Hence: =INTERCEPT({450,450.003},{100000;100001}) returns: 150 Second equation is: 0.3%*A1+150 Keep going for each one. Because the slopes are decreasing, we take the "Min." Some problems, like commissions, are increasing, so we would want to take the "Max" for those problems. Hope this helps. :) =Min(0.0045*A1,150+0.003*A1,650+0.002*A1,1650+0.00 1*A1) -- Dana DeLouis "joeu2004" wrote in message oups.com... On Jul 21, 10:06 pm, "Dana DeLouis" wrote: Perhaps another option: =MIN(0.1%*A1+1650, 0.2%*A1+650, 0.3%*A1+150, 0.45%*A1) Excellent! I would only suggest an explanation of the "magic" constants. I leave that to you to have the last word on the subject ;-). |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation question
On Jul 22, 6:39 am, "Dana DeLouis" wrote:
"joeu2004" wrote: On Jul 21, 10:06 pm, "Dana DeLouis" wrote: Perhaps another option: =MIN(0.1%*A1+1650, 0.2%*A1+650, 0.3%*A1+150, 0.45%*A1) Excellent! I would only suggest an explanation of the "magic" constants. Hi. I have a program that does this for me automatically. If we were to plot these types of problems, what we would have are 4 straight line intervals. The problem is to calculate the equation of each line (y = a*x+b). Interesting! But I think that is unnecessarily complex. The "magic" numbers can be determined simply by reducing the following expression of your formula: =min(0.1%*(A1-1000000) + 0.2%*500000 + 0.3%*400000 + 0.45%*100000, 0.2%*(A1-500000) + 0.3%*400000 + 0.45%*100000, 0.3%*(A1-100000) + 0.45%*100000, 0.45%*A1) Note that 400000 is 500000-100000; that is, the __size__ of the bracket, not its limit. Expanding and rearranging terms, we get: =min(0.1%*A1 - 1000 + 1000 + 1200 + 450, 0.2%*A1 - 1000 + 1200 + 450, 0.3%*A1 - 300 + 450, 0.45%*A1) which is easily reduced to your concise expression. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculation Question | Excel Discussion (Misc queries) | |||
Calculation Question | Excel Worksheet Functions | |||
Calculation Question | Excel Worksheet Functions | |||
Calculation question | Excel Discussion (Misc queries) | |||
Calculation Question | Excel Worksheet Functions |