Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding Problem
Good Afternoon, Everyone
I have a rounding problem. My s.s. figures quarter payments with monthly pre-pays. Excel is rounding the 2 monthly pre-pays from, for example, 8.625 to 8.63 ea., which is fine by me. That's the way we would obviously go when we make a payment, so I want it to show that. The problem arises when the 2 monthly pre-pays are added together to get the pre-pay figure to use in the quarterly calculation on the sheet. Excel gives the accurate calculation of 17.25, but I need it to show the 17.26 which we paid. Other than writing some kind of code, is there anyway to get around this, so that I'm not continually taking formulas out and doing hard inputs? The pre-pay portion of my ss has these Totals formulas on row 17: Col. E Col. F Col. G =e15+e16 =f15+f16 =e17+f17 Thanks for your help, -- smither fan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding Problem
You should enter the rounded figure in the paid cell... enter what you
actually paid if you want to sum the actual payments... Or If you want to sum the rounded figures then use the formula =Round(e15,2)+Round(e16,2) You have to somehow tell Excel that you want to sum the rounded payment and not 8.625 "Ross" wrote: Good Afternoon, Everyone I have a rounding problem. My s.s. figures quarter payments with monthly pre-pays. Excel is rounding the 2 monthly pre-pays from, for example, 8.625 to 8.63 ea., which is fine by me. That's the way we would obviously go when we make a payment, so I want it to show that. The problem arises when the 2 monthly pre-pays are added together to get the pre-pay figure to use in the quarterly calculation on the sheet. Excel gives the accurate calculation of 17.25, but I need it to show the 17.26 which we paid. Other than writing some kind of code, is there anyway to get around this, so that I'm not continually taking formulas out and doing hard inputs? The pre-pay portion of my ss has these Totals formulas on row 17: Col. E Col. F Col. G =e15+e16 =f15+f16 =e17+f17 Thanks for your help, -- smither fan |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding Problem
Go to Excel Options/ Advanced/ When Calculating This Workbook/ then tick Set
Precision as Displayed. -- Happy Cat "Ross" wrote: Good Afternoon, Everyone I have a rounding problem. My s.s. figures quarter payments with monthly pre-pays. Excel is rounding the 2 monthly pre-pays from, for example, 8.625 to 8.63 ea., which is fine by me. That's the way we would obviously go when we make a payment, so I want it to show that. The problem arises when the 2 monthly pre-pays are added together to get the pre-pay figure to use in the quarterly calculation on the sheet. Excel gives the accurate calculation of 17.25, but I need it to show the 17.26 which we paid. Other than writing some kind of code, is there anyway to get around this, so that I'm not continually taking formulas out and doing hard inputs? The pre-pay portion of my ss has these Totals formulas on row 17: Col. E Col. F Col. G =e15+e16 =f15+f16 =e17+f17 Thanks for your help, -- smither fan |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding Problem
Hello
Thanks for responding. I've read somewhere about the potential problem of instability if you do this. Do you know if any problems would be confined to the specific workbook, or would it affect all Excel files if I changed the setting? Thanks -- smither fan "Happy Cat" wrote: Go to Excel Options/ Advanced/ When Calculating This Workbook/ then tick Set Precision as Displayed. -- Happy Cat "Ross" wrote: Good Afternoon, Everyone I have a rounding problem. My s.s. figures quarter payments with monthly pre-pays. Excel is rounding the 2 monthly pre-pays from, for example, 8.625 to 8.63 ea., which is fine by me. That's the way we would obviously go when we make a payment, so I want it to show that. The problem arises when the 2 monthly pre-pays are added together to get the pre-pay figure to use in the quarterly calculation on the sheet. Excel gives the accurate calculation of 17.25, but I need it to show the 17.26 which we paid. Other than writing some kind of code, is there anyway to get around this, so that I'm not continually taking formulas out and doing hard inputs? The pre-pay portion of my ss has these Totals formulas on row 17: Col. E Col. F Col. G =e15+e16 =f15+f16 =e17+f17 Thanks for your help, -- smither fan |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding Problem
"Ross" wrote:
"Happy Cat" wrote: Go to Excel Options/ Advanced/ When Calculating This Workbook/ then tick Set Precision as Displayed. [....] I've read somewhere about the potential problem of instability if you do this. Do you know if any problems would be confined to the specific workbook, or would it affect all Excel files if I changed the setting? First, the option is limited to the specific workbook. Second, I would not say that "Precision as displayed" is unstable. There are some risks. But as I learn more about PAD, I would say that those risks have been overstated, by me and others. As with all of Excel's features, knowledge and understanding usually ameliorates the problems people complain about. The biggest risk -- a real one -- has to do with constants in the workbook. Note: constants only, not values resulting from formulas. Moreover, the risk applies only to cells with an explicit numeric format, not General (the default). Risk #1: Setting PAD affects all worksheets in the workbook, not just the selected worksheet. Consequently, the changes mentioned below can happen without your noticing. Risk #2: If you already have constants in cells with an explicit numeric format, when you set PAD, the underlying value of the cells will be forever changed. Consider your example: you have 8.625 in a cell, but it displays as 8.63. You probably set the cell to a numeric format with 2 decimal places. Despite what you see, the underlying value is truly 8.625. When you PAD, the underlying value will become 8.63. You say that's what you think you want now; but I wonder. The point is: if you change your mind later, the PAD change will be irreversible, except by manual editing, of course. Or suppose you are focusing on 8.625 when you decide to set PAD, but after setting PAD, you realize it changed another cell that you wish it hadn't. Again, the change irreversible except by manual editing. Word to the wise: Make a copy of the workbook as a back-up before you set PAD. Risk #3: After you set PAD, you enter constant numeric data into existing cells already have an explicit numeric format, unbeknownst to you. You enter 8.625; you see 8.63; so you decide to change the format to 3 decimal places. Too late! The underlying value is truly 8.63 now. The other "risks" in using PAD are really anomalies, and all are reversible or you can work around them. The key thing to know is: PAD affects only the final result of a numeric formula. It does not affect the precision of values used or referenced in formulas; and it does not affect intermediate calculations. PAD should be called "Precision of the result as the cell is formatted". For example, assume you have 8.625 in A1 and A2 with a numeric format with 3 decimal places. Assume you have =A1+A2 in A3 with a numeric format with 2 decimal places. With PAD set, you might think that each 8.625 is converted 8.63, the precision of A3, then added to give you 17.26. No. The result is 17.25 because the exact values in A1 and A2 (8.625) are used, then the result is rounded to the PAD for A3. The good news with PAD is: its effect on the results of formulas is reversible. If you simply change the format of A1 and A2, you will get 17.26. Or you could change the format of A3 to numeric with 1 decimal place and get 17.3 in either case. Or you can disable PAD, and formulas will resume their normal behavior. Finally, PAD does not completely insulate you from other anomalies due to the internal format used by Excel (binary floating-point). Most decimal fractions, like "63" in 8.63, cannot be represented exactly. So complex formulas can have surprising results that will still require you to use ROUND or some other means to work around them. For example, suppose PAD is set, and you have 0.5 in A1 and 0.4 in A2. In A3, you have =IF(A1-A2-0.1=0,TRUE). It might surprise you that the result is FALSE. The explanation is complex; it has been addressed in other threads. But the point here is: PAD did not protect you. So PAD is not the cure-all for all arithmetic anomalies in Excel. But it does avoid your having to explicitly ROUND every numeric formula to the number of decimal places in the cell format, and to change each final ROUND when you decide to change the cell format. Nonetheless, I still avoid PAD because of the risks with constants. I am too prone to error, too quick to do something without thinking of the consequences elsewhere in the workbook, and too dependent on undoing my own mistakes manually or with ctrl-Z. The effect of PAD on constants is irreversible. But if you are more confident in yourself (arguably a misguided feeling), perhaps you are less worried than I am, and PAD is the right thing for you to use. HTH. ----- original message ----- "Ross" wrote in message ... Hello Thanks for responding. I've read somewhere about the potential problem of instability if you do this. Do you know if any problems would be confined to the specific workbook, or would it affect all Excel files if I changed the setting? Thanks -- smither fan "Happy Cat" wrote: Go to Excel Options/ Advanced/ When Calculating This Workbook/ then tick Set Precision as Displayed. -- Happy Cat "Ross" wrote: Good Afternoon, Everyone I have a rounding problem. My s.s. figures quarter payments with monthly pre-pays. Excel is rounding the 2 monthly pre-pays from, for example, 8.625 to 8.63 ea., which is fine by me. That's the way we would obviously go when we make a payment, so I want it to show that. The problem arises when the 2 monthly pre-pays are added together to get the pre-pay figure to use in the quarterly calculation on the sheet. Excel gives the accurate calculation of 17.25, but I need it to show the 17.26 which we paid. Other than writing some kind of code, is there anyway to get around this, so that I'm not continually taking formulas out and doing hard inputs? The pre-pay portion of my ss has these Totals formulas on row 17: Col. E Col. F Col. G =e15+e16 =f15+f16 =e17+f17 Thanks for your help, -- smither fan |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding Problem
Thanks for the explanation. For myself...it sounds like I probably should
just live with it! Although I might experiment later on with something not as critical. Thanks again... -- smither fan "JoeU2004" wrote: "Ross" wrote: "Happy Cat" wrote: Go to Excel Options/ Advanced/ When Calculating This Workbook/ then tick Set Precision as Displayed. [....] I've read somewhere about the potential problem of instability if you do this. Do you know if any problems would be confined to the specific workbook, or would it affect all Excel files if I changed the setting? First, the option is limited to the specific workbook. Second, I would not say that "Precision as displayed" is unstable. There are some risks. But as I learn more about PAD, I would say that those risks have been overstated, by me and others. As with all of Excel's features, knowledge and understanding usually ameliorates the problems people complain about. The biggest risk -- a real one -- has to do with constants in the workbook. Note: constants only, not values resulting from formulas. Moreover, the risk applies only to cells with an explicit numeric format, not General (the default). Risk #1: Setting PAD affects all worksheets in the workbook, not just the selected worksheet. Consequently, the changes mentioned below can happen without your noticing. Risk #2: If you already have constants in cells with an explicit numeric format, when you set PAD, the underlying value of the cells will be forever changed. Consider your example: you have 8.625 in a cell, but it displays as 8.63. You probably set the cell to a numeric format with 2 decimal places. Despite what you see, the underlying value is truly 8.625. When you PAD, the underlying value will become 8.63. You say that's what you think you want now; but I wonder. The point is: if you change your mind later, the PAD change will be irreversible, except by manual editing, of course. Or suppose you are focusing on 8.625 when you decide to set PAD, but after setting PAD, you realize it changed another cell that you wish it hadn't. Again, the change irreversible except by manual editing. Word to the wise: Make a copy of the workbook as a back-up before you set PAD. Risk #3: After you set PAD, you enter constant numeric data into existing cells already have an explicit numeric format, unbeknownst to you. You enter 8.625; you see 8.63; so you decide to change the format to 3 decimal places. Too late! The underlying value is truly 8.63 now. The other "risks" in using PAD are really anomalies, and all are reversible or you can work around them. The key thing to know is: PAD affects only the final result of a numeric formula. It does not affect the precision of values used or referenced in formulas; and it does not affect intermediate calculations. PAD should be called "Precision of the result as the cell is formatted". For example, assume you have 8.625 in A1 and A2 with a numeric format with 3 decimal places. Assume you have =A1+A2 in A3 with a numeric format with 2 decimal places. With PAD set, you might think that each 8.625 is converted 8.63, the precision of A3, then added to give you 17.26. No. The result is 17.25 because the exact values in A1 and A2 (8.625) are used, then the result is rounded to the PAD for A3. The good news with PAD is: its effect on the results of formulas is reversible. If you simply change the format of A1 and A2, you will get 17.26. Or you could change the format of A3 to numeric with 1 decimal place and get 17.3 in either case. Or you can disable PAD, and formulas will resume their normal behavior. Finally, PAD does not completely insulate you from other anomalies due to the internal format used by Excel (binary floating-point). Most decimal fractions, like "63" in 8.63, cannot be represented exactly. So complex formulas can have surprising results that will still require you to use ROUND or some other means to work around them. For example, suppose PAD is set, and you have 0.5 in A1 and 0.4 in A2. In A3, you have =IF(A1-A2-0.1=0,TRUE). It might surprise you that the result is FALSE. The explanation is complex; it has been addressed in other threads. But the point here is: PAD did not protect you. So PAD is not the cure-all for all arithmetic anomalies in Excel. But it does avoid your having to explicitly ROUND every numeric formula to the number of decimal places in the cell format, and to change each final ROUND when you decide to change the cell format. Nonetheless, I still avoid PAD because of the risks with constants. I am too prone to error, too quick to do something without thinking of the consequences elsewhere in the workbook, and too dependent on undoing my own mistakes manually or with ctrl-Z. The effect of PAD on constants is irreversible. But if you are more confident in yourself (arguably a misguided feeling), perhaps you are less worried than I am, and PAD is the right thing for you to use. HTH. ----- original message ----- "Ross" wrote in message ... Hello Thanks for responding. I've read somewhere about the potential problem of instability if you do this. Do you know if any problems would be confined to the specific workbook, or would it affect all Excel files if I changed the setting? Thanks -- smither fan "Happy Cat" wrote: Go to Excel Options/ Advanced/ When Calculating This Workbook/ then tick Set Precision as Displayed. -- Happy Cat "Ross" wrote: Good Afternoon, Everyone I have a rounding problem. My s.s. figures quarter payments with monthly pre-pays. Excel is rounding the 2 monthly pre-pays from, for example, 8.625 to 8.63 ea., which is fine by me. That's the way we would obviously go when we make a payment, so I want it to show that. The problem arises when the 2 monthly pre-pays are added together to get the pre-pay figure to use in the quarterly calculation on the sheet. Excel gives the accurate calculation of 17.25, but I need it to show the 17.26 which we paid. Other than writing some kind of code, is there anyway to get around this, so that I'm not continually taking formulas out and doing hard inputs? The pre-pay portion of my ss has these Totals formulas on row 17: Col. E Col. F Col. G =e15+e16 =f15+f16 =e17+f17 Thanks for your help, -- smither fan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rounding Problem | Excel Discussion (Misc queries) | |||
Problem with rounding? | Excel Discussion (Misc queries) | |||
Rounding Problem | New Users to Excel | |||
Rounding Problem | Excel Worksheet Functions | |||
Rounding Problem | Excel Discussion (Misc queries) |