Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula computing problem
I'm hoping someone somewhere can tell me what's happening and how to fix it.
This is the formula I'm using: =-MAX(0,MIN(I9,100)*0.05)+-MAX(0,(I9-100)*0.02) and copy down through I11 Starting in I9 I have the following amounts. I Results i'm getting 570.75 (14.42) 246.36 (7.93) 38.18 (1.91) If I do a Total cell and use the same calculation on it, the answer will be 20.10, which is what I expect. The results of 9-11 are obviously going to total to more than 20.10. Can someone explain what's happening and is there a way to fix it so that the results are reasonably in line. It seems to me to be too big a difference to be a rounding thing?? Thanks -- OneFineDay |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula computing problem
Are you saying that the total of:
-14.42 + -7.93 + -1.91 Should be -20.10 If so, you'll have to explain why you think that should be the result. The formulas are returning the correct results. Maybe they're not doing what you think they're doing. -- Biff Microsoft Excel MVP "M Thompson" wrote in message ... I'm hoping someone somewhere can tell me what's happening and how to fix it. This is the formula I'm using: =-MAX(0,MIN(I9,100)*0.05)+-MAX(0,(I9-100)*0.02) and copy down through I11 Starting in I9 I have the following amounts. I Results i'm getting 570.75 (14.42) 246.36 (7.93) 38.18 (1.91) If I do a Total cell and use the same calculation on it, the answer will be 20.10, which is what I expect. The results of 9-11 are obviously going to total to more than 20.10. Can someone explain what's happening and is there a way to fix it so that the results are reasonably in line. It seems to me to be too big a difference to be a rounding thing?? Thanks -- OneFineDay |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula computing problem
Sorry-didn't explain properly. If I do a total of the data I've input
(I9-I11), the result is 855.29. The result of the calculation on that is 20.10, which is correct and what I need to see. If I don't do a total of the input data, but do a sum of the calculated answers for each individual piece it comes to 24.25. Why so much difference? -- OneFineDay "T. Valko" wrote: Are you saying that the total of: -14.42 + -7.93 + -1.91 Should be -20.10 If so, you'll have to explain why you think that should be the result. The formulas are returning the correct results. Maybe they're not doing what you think they're doing. -- Biff Microsoft Excel MVP "M Thompson" wrote in message ... I'm hoping someone somewhere can tell me what's happening and how to fix it. This is the formula I'm using: =-MAX(0,MIN(I9,100)*0.05)+-MAX(0,(I9-100)*0.02) and copy down through I11 Starting in I9 I have the following amounts. I Results i'm getting 570.75 (14.42) 246.36 (7.93) 38.18 (1.91) If I do a Total cell and use the same calculation on it, the answer will be 20.10, which is what I expect. The results of 9-11 are obviously going to total to more than 20.10. Can someone explain what's happening and is there a way to fix it so that the results are reasonably in line. It seems to me to be too big a difference to be a rounding thing?? Thanks -- OneFineDay |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula computing problem
Because you don't necessarily use those numbers in the calculation, you take
the MAX/Min of I9 and 100 so it is changing, so your overall result should only be based the results of the formal on the sum of all source data, not the sum of the individual results. -- __________________________________ HTH Bob "M Thompson" wrote in message ... Sorry-didn't explain properly. If I do a total of the data I've input (I9-I11), the result is 855.29. The result of the calculation on that is 20.10, which is correct and what I need to see. If I don't do a total of the input data, but do a sum of the calculated answers for each individual piece it comes to 24.25. Why so much difference? -- OneFineDay "T. Valko" wrote: Are you saying that the total of: -14.42 + -7.93 + -1.91 Should be -20.10 If so, you'll have to explain why you think that should be the result. The formulas are returning the correct results. Maybe they're not doing what you think they're doing. -- Biff Microsoft Excel MVP "M Thompson" wrote in message ... I'm hoping someone somewhere can tell me what's happening and how to fix it. This is the formula I'm using: =-MAX(0,MIN(I9,100)*0.05)+-MAX(0,(I9-100)*0.02) and copy down through I11 Starting in I9 I have the following amounts. I Results i'm getting 570.75 (14.42) 246.36 (7.93) 38.18 (1.91) If I do a Total cell and use the same calculation on it, the answer will be 20.10, which is what I expect. The results of 9-11 are obviously going to total to more than 20.10. Can someone explain what's happening and is there a way to fix it so that the results are reasonably in line. It seems to me to be too big a difference to be a rounding thing?? Thanks -- OneFineDay |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula computing problem
Not sure what the problem is... this might help
=-MAX(0,MIN(I9,100)*0.05) The above will give you 0 if I9<0, minus of 5% of I9 if it is between 0 and 100 and -5 if it is above 100 =-MAX(0,(I9-100)*0.02) The above will give you 0 if I9<100, minus of 2% of I9-100 if it is above 100 ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "M Thompson" wrote: I'm hoping someone somewhere can tell me what's happening and how to fix it. This is the formula I'm using: =-MAX(0,MIN(I9,100)*0.05)+-MAX(0,(I9-100)*0.02) and copy down through I11 Starting in I9 I have the following amounts. I Results i'm getting 570.75 (14.42) 246.36 (7.93) 38.18 (1.91) If I do a Total cell and use the same calculation on it, the answer will be 20.10, which is what I expect. The results of 9-11 are obviously going to total to more than 20.10. Can someone explain what's happening and is there a way to fix it so that the results are reasonably in line. It seems to me to be too big a difference to be a rounding thing?? Thanks -- OneFineDay |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula computing problem
Hello and thanks for responding so quick. The explanation was very helpful,
and the formula does what I thought. Please see my response to T. Valko's question. I hope it sheds better light on my problem. -- OneFineDay "Sheeloo" wrote: Not sure what the problem is... this might help =-MAX(0,MIN(I9,100)*0.05) The above will give you 0 if I9<0, minus of 5% of I9 if it is between 0 and 100 and -5 if it is above 100 =-MAX(0,(I9-100)*0.02) The above will give you 0 if I9<100, minus of 2% of I9-100 if it is above 100 ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "M Thompson" wrote: I'm hoping someone somewhere can tell me what's happening and how to fix it. This is the formula I'm using: =-MAX(0,MIN(I9,100)*0.05)+-MAX(0,(I9-100)*0.02) and copy down through I11 Starting in I9 I have the following amounts. I Results i'm getting 570.75 (14.42) 246.36 (7.93) 38.18 (1.91) If I do a Total cell and use the same calculation on it, the answer will be 20.10, which is what I expect. The results of 9-11 are obviously going to total to more than 20.10. Can someone explain what's happening and is there a way to fix it so that the results are reasonably in line. It seems to me to be too big a difference to be a rounding thing?? Thanks -- OneFineDay |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula computing problem
If you apply =-MAX(0,MIN(I9,100)*0.05) to 90 you will get -4.5, right? so if have 90 in two cells you will get -9 as total... if you apply it to 180 (sum of 90 and 90) you will get -5 Hope this helps... ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "M Thompson" wrote: Hello and thanks for responding so quick. The explanation was very helpful, and the formula does what I thought. Please see my response to T. Valko's question. I hope it sheds better light on my problem. -- OneFineDay "Sheeloo" wrote: Not sure what the problem is... this might help =-MAX(0,MIN(I9,100)*0.05) The above will give you 0 if I9<0, minus of 5% of I9 if it is between 0 and 100 and -5 if it is above 100 =-MAX(0,(I9-100)*0.02) The above will give you 0 if I9<100, minus of 2% of I9-100 if it is above 100 ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "M Thompson" wrote: I'm hoping someone somewhere can tell me what's happening and how to fix it. This is the formula I'm using: =-MAX(0,MIN(I9,100)*0.05)+-MAX(0,(I9-100)*0.02) and copy down through I11 Starting in I9 I have the following amounts. I Results i'm getting 570.75 (14.42) 246.36 (7.93) 38.18 (1.91) If I do a Total cell and use the same calculation on it, the answer will be 20.10, which is what I expect. The results of 9-11 are obviously going to total to more than 20.10. Can someone explain what's happening and is there a way to fix it so that the results are reasonably in line. It seems to me to be too big a difference to be a rounding thing?? Thanks -- OneFineDay |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula computing problem
You're right, of course, it would necessarily have 2 different results. For
some reason it just took me awhile to realize it. Please see my answer to Joe. And thanks -- OneFineDay "Sheeloo" wrote: If you apply =-MAX(0,MIN(I9,100)*0.05) to 90 you will get -4.5, right? so if have 90 in two cells you will get -9 as total... if you apply it to 180 (sum of 90 and 90) you will get -5 Hope this helps... ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "M Thompson" wrote: Hello and thanks for responding so quick. The explanation was very helpful, and the formula does what I thought. Please see my response to T. Valko's question. I hope it sheds better light on my problem. -- OneFineDay "Sheeloo" wrote: Not sure what the problem is... this might help =-MAX(0,MIN(I9,100)*0.05) The above will give you 0 if I9<0, minus of 5% of I9 if it is between 0 and 100 and -5 if it is above 100 =-MAX(0,(I9-100)*0.02) The above will give you 0 if I9<100, minus of 2% of I9-100 if it is above 100 ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "M Thompson" wrote: I'm hoping someone somewhere can tell me what's happening and how to fix it. This is the formula I'm using: =-MAX(0,MIN(I9,100)*0.05)+-MAX(0,(I9-100)*0.02) and copy down through I11 Starting in I9 I have the following amounts. I Results i'm getting 570.75 (14.42) 246.36 (7.93) 38.18 (1.91) If I do a Total cell and use the same calculation on it, the answer will be 20.10, which is what I expect. The results of 9-11 are obviously going to total to more than 20.10. Can someone explain what's happening and is there a way to fix it so that the results are reasonably in line. It seems to me to be too big a difference to be a rounding thing?? Thanks -- OneFineDay |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula computing problem
"M Thompson" wrote:
It seems to me to be too big a difference to be a rounding thing Yes. But as an aside, I think you will want to add some judicious rounding if you want the __displayed__ values to sum to expectations. Remember: although Excel will round according to the specified format, the underlying values might have greater precision, unless you set the calculation option "Precision as displayed". Did you? Anyway, returning to your question.... Can someone explain what's happening This is one of those mathematical problems like "the average the sum is not necessarily the same as the sum of the averages". In this case, we are talking about tiered percentage amounts, not averages. Basically, for each line item, your "results" column (J) is ostensibly 5% of the first 100 plus 2% of the excess above 100 of the corresponding value in column I. But you seem to want to limit the sum of the tiered percentage amounts to the tiered percentage amount of the sum. Your formula does not guarantee that. Consider this simple example: I9 = 1000, and I10 = 1000; then J9 = -23, and J10 = -23 (100*5% + 900*2%), and they sum to -46. But if I11 is the total, 2000, then J11 is -43 (100*5% + 1900*2%). Do you see the problem? In the last first sum, you have 100*5% + 100*5% = 200*5%. But in J11, you have only 100*5%. (With concomitant differences in the 2% term, too.) is there a way to fix it so that the results are reasonably in line. The answer is "yes". But the specific solution depends on your definition of "reasonable". There is no a priori requirement that is "most reasonable". It depends on your application. If you need help in deciding which of the following is the right "reasonable" requirement, we will need to know your application. In other words, what do the numbers in column I represent, and what do the tier percentage amounts in column J represent? The first "reasonable" solution is: do not expect the sum of the tiered percentage amounts to equal the tiered percentage amount of the sum. In other words, the only error is your expectation in the first place. A second "resonable" requirement is: the cumulative sum of tiered percentage amounts should not exceed the tiered percentage amount of the cumulative sum. The formula might be: =-MAX(0,MIN(SUM($I$9:I9),100)*0.05)-MAX(0,(SUM($I$9:I9)-100)*0.02)-SUM($J$8:J8) Note: This assumes that J8 is blank or text. A third "reasonable" requirement is: the tiered percentage amount should not exceed the remainder of the tiered percentage amount of the total less the cumulative sum of the tiered percentage amounts. =MAX(-MAX(0,MIN(I9,100)*0.05)-MAX(0,(I9-100)*0.02),$J$12-SUM($J$8:J8)) Note: This additionally assumes that the tiered percentage amount of the total is in J12. There may be other alternative "reasonable" requirements. Some additional notes: 1. You need MAX(0,MIN(I9,100)*0.05) only if I9 might be negative. Otherwise, MIN(I9,100)*0.05 should suffice. 2. I changed the needless +-MAX(...) in the second term of the expression to simply -MAX(...). The "+" is superfluous. 3. Dealing with negative numbers can be confusing. The outermost MAX in third possible solution actually selects the smaller __magnitude__ of the numbers. For example, -2 is less than -1, but -1 is the smaller magnitude. HTH. ----- original message ----- "M Thompson" wrote in message ... I'm hoping someone somewhere can tell me what's happening and how to fix it. This is the formula I'm using: =-MAX(0,MIN(I9,100)*0.05)+-MAX(0,(I9-100)*0.02) and copy down through I11 Starting in I9 I have the following amounts. I Results i'm getting 570.75 (14.42) 246.36 (7.93) 38.18 (1.91) If I do a Total cell and use the same calculation on it, the answer will be 20.10, which is what I expect. The results of 9-11 are obviously going to total to more than 20.10. Can someone explain what's happening and is there a way to fix it so that the results are reasonably in line. It seems to me to be too big a difference to be a rounding thing?? Thanks -- OneFineDay |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula computing problem
Joe-
You gave me much to consider and what you said made me realize that there was no way I could reconcile the two. Figuring each one separately takes 5% (or 5) off of each line and 2% of the rest of each line, whereas doing the calculation on the whole 855.29 only takes 5% one time and 2% off the rest. Since it is a quarterly calculation, the latter needs to be the solution. Sorry it took up so much of everyone's time and effort for me to realize it. Thus a classic case of "the nut behind the wheel", so to speak! Thanks -- OneFineDay "JoeU2004" wrote: "M Thompson" wrote: It seems to me to be too big a difference to be a rounding thing Yes. But as an aside, I think you will want to add some judicious rounding if you want the __displayed__ values to sum to expectations. Remember: although Excel will round according to the specified format, the underlying values might have greater precision, unless you set the calculation option "Precision as displayed". Did you? Anyway, returning to your question.... Can someone explain what's happening This is one of those mathematical problems like "the average the sum is not necessarily the same as the sum of the averages". In this case, we are talking about tiered percentage amounts, not averages. Basically, for each line item, your "results" column (J) is ostensibly 5% of the first 100 plus 2% of the excess above 100 of the corresponding value in column I. But you seem to want to limit the sum of the tiered percentage amounts to the tiered percentage amount of the sum. Your formula does not guarantee that. Consider this simple example: I9 = 1000, and I10 = 1000; then J9 = -23, and J10 = -23 (100*5% + 900*2%), and they sum to -46. But if I11 is the total, 2000, then J11 is -43 (100*5% + 1900*2%). Do you see the problem? In the last first sum, you have 100*5% + 100*5% = 200*5%. But in J11, you have only 100*5%. (With concomitant differences in the 2% term, too.) is there a way to fix it so that the results are reasonably in line. The answer is "yes". But the specific solution depends on your definition of "reasonable". There is no a priori requirement that is "most reasonable". It depends on your application. If you need help in deciding which of the following is the right "reasonable" requirement, we will need to know your application. In other words, what do the numbers in column I represent, and what do the tier percentage amounts in column J represent? The first "reasonable" solution is: do not expect the sum of the tiered percentage amounts to equal the tiered percentage amount of the sum. In other words, the only error is your expectation in the first place. A second "resonable" requirement is: the cumulative sum of tiered percentage amounts should not exceed the tiered percentage amount of the cumulative sum. The formula might be: =-MAX(0,MIN(SUM($I$9:I9),100)*0.05)-MAX(0,(SUM($I$9:I9)-100)*0.02)-SUM($J$8:J8) Note: This assumes that J8 is blank or text. A third "reasonable" requirement is: the tiered percentage amount should not exceed the remainder of the tiered percentage amount of the total less the cumulative sum of the tiered percentage amounts. =MAX(-MAX(0,MIN(I9,100)*0.05)-MAX(0,(I9-100)*0.02),$J$12-SUM($J$8:J8)) Note: This additionally assumes that the tiered percentage amount of the total is in J12. There may be other alternative "reasonable" requirements. Some additional notes: 1. You need MAX(0,MIN(I9,100)*0.05) only if I9 might be negative. Otherwise, MIN(I9,100)*0.05 should suffice. 2. I changed the needless +-MAX(...) in the second term of the expression to simply -MAX(...). The "+" is superfluous. 3. Dealing with negative numbers can be confusing. The outermost MAX in third possible solution actually selects the smaller __magnitude__ of the numbers. For example, -2 is less than -1, but -1 is the smaller magnitude. HTH. ----- original message ----- "M Thompson" wrote in message ... I'm hoping someone somewhere can tell me what's happening and how to fix it. This is the formula I'm using: =-MAX(0,MIN(I9,100)*0.05)+-MAX(0,(I9-100)*0.02) and copy down through I11 Starting in I9 I have the following amounts. I Results i'm getting 570.75 (14.42) 246.36 (7.93) 38.18 (1.91) If I do a Total cell and use the same calculation on it, the answer will be 20.10, which is what I expect. The results of 9-11 are obviously going to total to more than 20.10. Can someone explain what's happening and is there a way to fix it so that the results are reasonably in line. It seems to me to be too big a difference to be a rounding thing?? Thanks -- OneFineDay |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Computing mortgage APR | Excel Worksheet Functions | |||
Computing Time from One Day to Another | Excel Discussion (Misc queries) | |||
COUNTIF not computing | Excel Discussion (Misc queries) | |||
Formula for computing day of the week and time | Excel Worksheet Functions | |||
Formula for computing work time in Excel | New Users to Excel |