Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If function not working correctly
Hi I'm using Excel 2003
Formula =IF(F2=SUM(G2:U2),"Balanced","Not Balanced") F2 and G2:U2 are all sum functions. The formula seems to work fine then all of a sudden I get a "Not Balanced" when the values are correct. I'm using 2 decimal places and have ensured the whole page is set as this. I use a separate spreadsheet each month and the problem normally comes up towards the bottom of the page, (normally about 300 row entries), but this month it's cropped up after 6 rows. Previously I've been able to delete a few rows, type in a few other entries, then re-type the amounts that were causing the problem again and it seems to fix it. Not today though. Any suggestions. Thanks -- Relle |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
If function not working correctly
Hi,
Because you have your cells set to 2 decimal places the sums may 'appear' balanced but may not be. For example 1.0234 1.0235 set to 2 decimal places bith display in the cell as 1.02 but they arent the same. So it depends on what level of equality you want. If 2 decimal places is enough try =IF(ROUND(F2,2)=ROUND(SUM(G2:U2),2),"Balanced","No t Balanced") You can increase the rounding to 3 if you want greater accuracy. Mike "Relle" wrote: Hi I'm using Excel 2003 Formula =IF(F2=SUM(G2:U2),"Balanced","Not Balanced") F2 and G2:U2 are all sum functions. The formula seems to work fine then all of a sudden I get a "Not Balanced" when the values are correct. I'm using 2 decimal places and have ensured the whole page is set as this. I use a separate spreadsheet each month and the problem normally comes up towards the bottom of the page, (normally about 300 row entries), but this month it's cropped up after 6 rows. Previously I've been able to delete a few rows, type in a few other entries, then re-type the amounts that were causing the problem again and it seems to fix it. Not today though. Any suggestions. Thanks -- Relle |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
If function not working correctly
"Mike H" wrote:
=IF(ROUND(F2,2)=ROUND(SUM(G2:U2),2),"Balanced","No t Balanced") Just to expand on Mike's comments a bit.... The above solution will probably work. But it might be good practice to use ROUND in formulas prolifically, albeit prudently, throughout the worksheet; for example, in F2 and G2:U2, which Relle says are "all sum functions". Then the formula here can be simplified somewhat: =IF(F2=ROUND(SUM(G2:U2),2),"Balanced","Not Balanced") Relle wrote: I use a separate spreadsheet each month and the problem normally comes up towards the bottom of the page, (normally about 300 row entries), but this month it's cropped up after 6 rows. To appreciate the issue, try this as an experiment: enter =IF(10.1 - 10 = 0.1, TRUE). The result is FALSE (!). As you see, the problem can arise with as few as 2 values being added or subtracted. The reason is: most numbers with decimal fractions cannot be represented exactly. The approximated internal representations cause very subtle differences when combining constants in arithmetic expressions. Sometimes you can see these differences by formatting the cells so that you can see 15 significant digits (i.e. ignoring leading zeros). But sometimes even that does not reveal the differences. Sometimes the differences correct themselves when performing the arithmetic. Sometimes Excel adjusts the arithmetic results in a half-baked attempt to correct some subtle differences. But often, the differences are simply carried along and magnified from computation to computation until they make a significant difference. Relle wrote: I'm using 2 decimal places and have ensured the whole page is set as this. Formatting only affects the appearance of values in cells, not their actual values. Previously I've been able to delete a few rows, type in a few other entries, then re-type the amounts that were causing the problem again and it seems to fix it. Probably just by coincidence, depending on which values you entered manually. What the ROUND function does is: it ensures that the result matches the internal representation of the number as if you had entered it manually. ----- original message ----- "Mike H" wrote in message ... Hi, Because you have your cells set to 2 decimal places the sums may 'appear' balanced but may not be. For example 1.0234 1.0235 set to 2 decimal places bith display in the cell as 1.02 but they arent the same. So it depends on what level of equality you want. If 2 decimal places is enough try =IF(ROUND(F2,2)=ROUND(SUM(G2:U2),2),"Balanced","No t Balanced") You can increase the rounding to 3 if you want greater accuracy. Mike "Relle" wrote: Hi I'm using Excel 2003 Formula =IF(F2=SUM(G2:U2),"Balanced","Not Balanced") F2 and G2:U2 are all sum functions. The formula seems to work fine then all of a sudden I get a "Not Balanced" when the values are correct. I'm using 2 decimal places and have ensured the whole page is set as this. I use a separate spreadsheet each month and the problem normally comes up towards the bottom of the page, (normally about 300 row entries), but this month it's cropped up after 6 rows. Previously I've been able to delete a few rows, type in a few other entries, then re-type the amounts that were causing the problem again and it seems to fix it. Not today though. Any suggestions. Thanks -- Relle |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
If function not working correctly
The decimals is not the problem, the problem even seems to be occuring when
entering whole numbers without decimals - all data entered onto the spreadsheet are only two decimals as its actual money. Any other suggestions? -- Relle "JoeU2004" wrote: "Mike H" wrote: =IF(ROUND(F2,2)=ROUND(SUM(G2:U2),2),"Balanced","No t Balanced") Just to expand on Mike's comments a bit.... The above solution will probably work. But it might be good practice to use ROUND in formulas prolifically, albeit prudently, throughout the worksheet; for example, in F2 and G2:U2, which Relle says are "all sum functions". Then the formula here can be simplified somewhat: =IF(F2=ROUND(SUM(G2:U2),2),"Balanced","Not Balanced") Relle wrote: I use a separate spreadsheet each month and the problem normally comes up towards the bottom of the page, (normally about 300 row entries), but this month it's cropped up after 6 rows. To appreciate the issue, try this as an experiment: enter =IF(10.1 - 10 = 0.1, TRUE). The result is FALSE (!). As you see, the problem can arise with as few as 2 values being added or subtracted. The reason is: most numbers with decimal fractions cannot be represented exactly. The approximated internal representations cause very subtle differences when combining constants in arithmetic expressions. Sometimes you can see these differences by formatting the cells so that you can see 15 significant digits (i.e. ignoring leading zeros). But sometimes even that does not reveal the differences. Sometimes the differences correct themselves when performing the arithmetic. Sometimes Excel adjusts the arithmetic results in a half-baked attempt to correct some subtle differences. But often, the differences are simply carried along and magnified from computation to computation until they make a significant difference. Relle wrote: I'm using 2 decimal places and have ensured the whole page is set as this. Formatting only affects the appearance of values in cells, not their actual values. Previously I've been able to delete a few rows, type in a few other entries, then re-type the amounts that were causing the problem again and it seems to fix it. Probably just by coincidence, depending on which values you entered manually. What the ROUND function does is: it ensures that the result matches the internal representation of the number as if you had entered it manually. ----- original message ----- "Mike H" wrote in message ... Hi, Because you have your cells set to 2 decimal places the sums may 'appear' balanced but may not be. For example 1.0234 1.0235 set to 2 decimal places bith display in the cell as 1.02 but they arent the same. So it depends on what level of equality you want. If 2 decimal places is enough try =IF(ROUND(F2,2)=ROUND(SUM(G2:U2),2),"Balanced","No t Balanced") You can increase the rounding to 3 if you want greater accuracy. Mike "Relle" wrote: Hi I'm using Excel 2003 Formula =IF(F2=SUM(G2:U2),"Balanced","Not Balanced") F2 and G2:U2 are all sum functions. The formula seems to work fine then all of a sudden I get a "Not Balanced" when the values are correct. I'm using 2 decimal places and have ensured the whole page is set as this. I use a separate spreadsheet each month and the problem normally comes up towards the bottom of the page, (normally about 300 row entries), but this month it's cropped up after 6 rows. Previously I've been able to delete a few rows, type in a few other entries, then re-type the amounts that were causing the problem again and it seems to fix it. Not today though. Any suggestions. Thanks -- Relle |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
If function not working correctly
If it's not the decimals, I could only suggest that you've made a mistake in
your data (or that you don't have calculation mode set to Auto). What values do you have for the following formulae: =F2 =ROUND(F2,2) =SUM(G2:U2) =ROUND(SUM(G2:U2),2) =F2-ROUND(SUM(G2:U2),2) =ROUND(F2,2)-ROUND(SUM(G2:U2),2) ? -- David Biddulph "Relle" wrote in message ... The decimals is not the problem, the problem even seems to be occuring when entering whole numbers without decimals - all data entered onto the spreadsheet are only two decimals as its actual money. Any other suggestions? -- Relle "JoeU2004" wrote: "Mike H" wrote: =IF(ROUND(F2,2)=ROUND(SUM(G2:U2),2),"Balanced","No t Balanced") Just to expand on Mike's comments a bit.... The above solution will probably work. But it might be good practice to use ROUND in formulas prolifically, albeit prudently, throughout the worksheet; for example, in F2 and G2:U2, which Relle says are "all sum functions". Then the formula here can be simplified somewhat: =IF(F2=ROUND(SUM(G2:U2),2),"Balanced","Not Balanced") Relle wrote: I use a separate spreadsheet each month and the problem normally comes up towards the bottom of the page, (normally about 300 row entries), but this month it's cropped up after 6 rows. To appreciate the issue, try this as an experiment: enter =IF(10.1 - 10 = 0.1, TRUE). The result is FALSE (!). As you see, the problem can arise with as few as 2 values being added or subtracted. The reason is: most numbers with decimal fractions cannot be represented exactly. The approximated internal representations cause very subtle differences when combining constants in arithmetic expressions. Sometimes you can see these differences by formatting the cells so that you can see 15 significant digits (i.e. ignoring leading zeros). But sometimes even that does not reveal the differences. Sometimes the differences correct themselves when performing the arithmetic. Sometimes Excel adjusts the arithmetic results in a half-baked attempt to correct some subtle differences. But often, the differences are simply carried along and magnified from computation to computation until they make a significant difference. Relle wrote: I'm using 2 decimal places and have ensured the whole page is set as this. Formatting only affects the appearance of values in cells, not their actual values. Previously I've been able to delete a few rows, type in a few other entries, then re-type the amounts that were causing the problem again and it seems to fix it. Probably just by coincidence, depending on which values you entered manually. What the ROUND function does is: it ensures that the result matches the internal representation of the number as if you had entered it manually. ----- original message ----- "Mike H" wrote in message ... Hi, Because you have your cells set to 2 decimal places the sums may 'appear' balanced but may not be. For example 1.0234 1.0235 set to 2 decimal places bith display in the cell as 1.02 but they arent the same. So it depends on what level of equality you want. If 2 decimal places is enough try =IF(ROUND(F2,2)=ROUND(SUM(G2:U2),2),"Balanced","No t Balanced") You can increase the rounding to 3 if you want greater accuracy. Mike "Relle" wrote: Hi I'm using Excel 2003 Formula =IF(F2=SUM(G2:U2),"Balanced","Not Balanced") F2 and G2:U2 are all sum functions. The formula seems to work fine then all of a sudden I get a "Not Balanced" when the values are correct. I'm using 2 decimal places and have ensured the whole page is set as this. I use a separate spreadsheet each month and the problem normally comes up towards the bottom of the page, (normally about 300 row entries), but this month it's cropped up after 6 rows. Previously I've been able to delete a few rows, type in a few other entries, then re-type the amounts that were causing the problem again and it seems to fix it. Not today though. Any suggestions. Thanks -- Relle |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
If function not working correctly
Hi David
I've tried all them formulas - the first four all give me the same answer 2348.84, the last two give me the result 0. Double checked and the calculation mode is set to automatic. Any other suggestions - I feel I'm ready to pull my hair out. -- Relle "David Biddulph" wrote: If it's not the decimals, I could only suggest that you've made a mistake in your data (or that you don't have calculation mode set to Auto). What values do you have for the following formulae: =F2 =ROUND(F2,2) =SUM(G2:U2) =ROUND(SUM(G2:U2),2) =F2-ROUND(SUM(G2:U2),2) =ROUND(F2,2)-ROUND(SUM(G2:U2),2) ? -- David Biddulph "Relle" wrote in message ... The decimals is not the problem, the problem even seems to be occuring when entering whole numbers without decimals - all data entered onto the spreadsheet are only two decimals as its actual money. Any other suggestions? -- Relle "JoeU2004" wrote: "Mike H" wrote: =IF(ROUND(F2,2)=ROUND(SUM(G2:U2),2),"Balanced","No t Balanced") Just to expand on Mike's comments a bit.... The above solution will probably work. But it might be good practice to use ROUND in formulas prolifically, albeit prudently, throughout the worksheet; for example, in F2 and G2:U2, which Relle says are "all sum functions". Then the formula here can be simplified somewhat: =IF(F2=ROUND(SUM(G2:U2),2),"Balanced","Not Balanced") Relle wrote: I use a separate spreadsheet each month and the problem normally comes up towards the bottom of the page, (normally about 300 row entries), but this month it's cropped up after 6 rows. To appreciate the issue, try this as an experiment: enter =IF(10.1 - 10 = 0.1, TRUE). The result is FALSE (!). As you see, the problem can arise with as few as 2 values being added or subtracted. The reason is: most numbers with decimal fractions cannot be represented exactly. The approximated internal representations cause very subtle differences when combining constants in arithmetic expressions. Sometimes you can see these differences by formatting the cells so that you can see 15 significant digits (i.e. ignoring leading zeros). But sometimes even that does not reveal the differences. Sometimes the differences correct themselves when performing the arithmetic. Sometimes Excel adjusts the arithmetic results in a half-baked attempt to correct some subtle differences. But often, the differences are simply carried along and magnified from computation to computation until they make a significant difference. Relle wrote: I'm using 2 decimal places and have ensured the whole page is set as this. Formatting only affects the appearance of values in cells, not their actual values. Previously I've been able to delete a few rows, type in a few other entries, then re-type the amounts that were causing the problem again and it seems to fix it. Probably just by coincidence, depending on which values you entered manually. What the ROUND function does is: it ensures that the result matches the internal representation of the number as if you had entered it manually. ----- original message ----- "Mike H" wrote in message ... Hi, Because you have your cells set to 2 decimal places the sums may 'appear' balanced but may not be. For example 1.0234 1.0235 set to 2 decimal places bith display in the cell as 1.02 but they arent the same. So it depends on what level of equality you want. If 2 decimal places is enough try =IF(ROUND(F2,2)=ROUND(SUM(G2:U2),2),"Balanced","No t Balanced") You can increase the rounding to 3 if you want greater accuracy. Mike "Relle" wrote: Hi I'm using Excel 2003 Formula =IF(F2=SUM(G2:U2),"Balanced","Not Balanced") F2 and G2:U2 are all sum functions. The formula seems to work fine then all of a sudden I get a "Not Balanced" when the values are correct. I'm using 2 decimal places and have ensured the whole page is set as this. I use a separate spreadsheet each month and the problem normally comes up towards the bottom of the page, (normally about 300 row entries), but this month it's cropped up after 6 rows. Previously I've been able to delete a few rows, type in a few other entries, then re-type the amounts that were causing the problem again and it seems to fix it. Not today though. Any suggestions. Thanks -- Relle |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
If function not working correctly
I thought that you were saying that there were problems with whole numbers?
2348.84 doesn't sound like whole numbers (and, of course, 2348.84 cannot be expressed exactly in fixed point binary, just as 1/3 cannot be expressed exactly in fixed point decimal). You will therefore need to look at more decimal places on those numbers. Try formatting to show 15 decimal places. While you are there, what do the following show: =F2=ROUND(SUM(G2:U2),2) =ROUND(F2,2)=ROUND(SUM(G2:U2),2) ? -- David Biddulph "Relle" wrote in message ... Hi David I've tried all them formulas - the first four all give me the same answer 2348.84, the last two give me the result 0. Double checked and the calculation mode is set to automatic. Any other suggestions - I feel I'm ready to pull my hair out. -- Relle "David Biddulph" wrote: If it's not the decimals, I could only suggest that you've made a mistake in your data (or that you don't have calculation mode set to Auto). What values do you have for the following formulae: =F2 =ROUND(F2,2) =SUM(G2:U2) =ROUND(SUM(G2:U2),2) =F2-ROUND(SUM(G2:U2),2) =ROUND(F2,2)-ROUND(SUM(G2:U2),2) ? -- David Biddulph "Relle" wrote in message ... The decimals is not the problem, the problem even seems to be occuring when entering whole numbers without decimals - all data entered onto the spreadsheet are only two decimals as its actual money. Any other suggestions? -- Relle "JoeU2004" wrote: "Mike H" wrote: =IF(ROUND(F2,2)=ROUND(SUM(G2:U2),2),"Balanced","No t Balanced") Just to expand on Mike's comments a bit.... The above solution will probably work. But it might be good practice to use ROUND in formulas prolifically, albeit prudently, throughout the worksheet; for example, in F2 and G2:U2, which Relle says are "all sum functions". Then the formula here can be simplified somewhat: =IF(F2=ROUND(SUM(G2:U2),2),"Balanced","Not Balanced") Relle wrote: I use a separate spreadsheet each month and the problem normally comes up towards the bottom of the page, (normally about 300 row entries), but this month it's cropped up after 6 rows. To appreciate the issue, try this as an experiment: enter =IF(10.1 - 10 = 0.1, TRUE). The result is FALSE (!). As you see, the problem can arise with as few as 2 values being added or subtracted. The reason is: most numbers with decimal fractions cannot be represented exactly. The approximated internal representations cause very subtle differences when combining constants in arithmetic expressions. Sometimes you can see these differences by formatting the cells so that you can see 15 significant digits (i.e. ignoring leading zeros). But sometimes even that does not reveal the differences. Sometimes the differences correct themselves when performing the arithmetic. Sometimes Excel adjusts the arithmetic results in a half-baked attempt to correct some subtle differences. But often, the differences are simply carried along and magnified from computation to computation until they make a significant difference. Relle wrote: I'm using 2 decimal places and have ensured the whole page is set as this. Formatting only affects the appearance of values in cells, not their actual values. Previously I've been able to delete a few rows, type in a few other entries, then re-type the amounts that were causing the problem again and it seems to fix it. Probably just by coincidence, depending on which values you entered manually. What the ROUND function does is: it ensures that the result matches the internal representation of the number as if you had entered it manually. ----- original message ----- "Mike H" wrote in message ... Hi, Because you have your cells set to 2 decimal places the sums may 'appear' balanced but may not be. For example 1.0234 1.0235 set to 2 decimal places bith display in the cell as 1.02 but they arent the same. So it depends on what level of equality you want. If 2 decimal places is enough try =IF(ROUND(F2,2)=ROUND(SUM(G2:U2),2),"Balanced","No t Balanced") You can increase the rounding to 3 if you want greater accuracy. Mike "Relle" wrote: Hi I'm using Excel 2003 Formula =IF(F2=SUM(G2:U2),"Balanced","Not Balanced") F2 and G2:U2 are all sum functions. The formula seems to work fine then all of a sudden I get a "Not Balanced" when the values are correct. I'm using 2 decimal places and have ensured the whole page is set as this. I use a separate spreadsheet each month and the problem normally comes up towards the bottom of the page, (normally about 300 row entries), but this month it's cropped up after 6 rows. Previously I've been able to delete a few rows, type in a few other entries, then re-type the amounts that were causing the problem again and it seems to fix it. Not today though. Any suggestions. Thanks -- Relle |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
If function not working correctly
Sorry for the confusion David. I'm working in 2 decimal places (it's actual
money the figures that I'm using so there's no calculations to obtain the 2 decimals except addition of funds). I tried an example of whole numbers just to ensure it was the formula and not some error I had made somewhere that I couldn't find. I've tried both of those formulas and they respond true even though I'm still getting "Not-balanced" -- Relle "David Biddulph" wrote: I thought that you were saying that there were problems with whole numbers? 2348.84 doesn't sound like whole numbers (and, of course, 2348.84 cannot be expressed exactly in fixed point binary, just as 1/3 cannot be expressed exactly in fixed point decimal). You will therefore need to look at more decimal places on those numbers. Try formatting to show 15 decimal places. While you are there, what do the following show: =F2=ROUND(SUM(G2:U2),2) =ROUND(F2,2)=ROUND(SUM(G2:U2),2) ? -- David Biddulph "Relle" wrote in message ... Hi David I've tried all them formulas - the first four all give me the same answer 2348.84, the last two give me the result 0. Double checked and the calculation mode is set to automatic. Any other suggestions - I feel I'm ready to pull my hair out. -- Relle "David Biddulph" wrote: If it's not the decimals, I could only suggest that you've made a mistake in your data (or that you don't have calculation mode set to Auto). What values do you have for the following formulae: =F2 =ROUND(F2,2) =SUM(G2:U2) =ROUND(SUM(G2:U2),2) =F2-ROUND(SUM(G2:U2),2) =ROUND(F2,2)-ROUND(SUM(G2:U2),2) ? -- David Biddulph "Relle" wrote in message ... The decimals is not the problem, the problem even seems to be occuring when entering whole numbers without decimals - all data entered onto the spreadsheet are only two decimals as its actual money. Any other suggestions? -- Relle "JoeU2004" wrote: "Mike H" wrote: =IF(ROUND(F2,2)=ROUND(SUM(G2:U2),2),"Balanced","No t Balanced") Just to expand on Mike's comments a bit.... The above solution will probably work. But it might be good practice to use ROUND in formulas prolifically, albeit prudently, throughout the worksheet; for example, in F2 and G2:U2, which Relle says are "all sum functions". Then the formula here can be simplified somewhat: =IF(F2=ROUND(SUM(G2:U2),2),"Balanced","Not Balanced") Relle wrote: I use a separate spreadsheet each month and the problem normally comes up towards the bottom of the page, (normally about 300 row entries), but this month it's cropped up after 6 rows. To appreciate the issue, try this as an experiment: enter =IF(10.1 - 10 = 0.1, TRUE). The result is FALSE (!). As you see, the problem can arise with as few as 2 values being added or subtracted. The reason is: most numbers with decimal fractions cannot be represented exactly. The approximated internal representations cause very subtle differences when combining constants in arithmetic expressions. Sometimes you can see these differences by formatting the cells so that you can see 15 significant digits (i.e. ignoring leading zeros). But sometimes even that does not reveal the differences. Sometimes the differences correct themselves when performing the arithmetic. Sometimes Excel adjusts the arithmetic results in a half-baked attempt to correct some subtle differences. But often, the differences are simply carried along and magnified from computation to computation until they make a significant difference. Relle wrote: I'm using 2 decimal places and have ensured the whole page is set as this. Formatting only affects the appearance of values in cells, not their actual values. Previously I've been able to delete a few rows, type in a few other entries, then re-type the amounts that were causing the problem again and it seems to fix it. Probably just by coincidence, depending on which values you entered manually. What the ROUND function does is: it ensures that the result matches the internal representation of the number as if you had entered it manually. ----- original message ----- "Mike H" wrote in message ... Hi, Because you have your cells set to 2 decimal places the sums may 'appear' balanced but may not be. For example 1.0234 1.0235 set to 2 decimal places bith display in the cell as 1.02 but they arent the same. So it depends on what level of equality you want. If 2 decimal places is enough try =IF(ROUND(F2,2)=ROUND(SUM(G2:U2),2),"Balanced","No t Balanced") You can increase the rounding to 3 if you want greater accuracy. Mike "Relle" wrote: Hi I'm using Excel 2003 Formula =IF(F2=SUM(G2:U2),"Balanced","Not Balanced") F2 and G2:U2 are all sum functions. The formula seems to work fine then all of a sudden I get a "Not Balanced" when the values are correct. I'm using 2 decimal places and have ensured the whole page is set as this. I use a separate spreadsheet each month and the problem normally comes up towards the bottom of the page, (normally about 300 row entries), but this month it's cropped up after 6 rows. Previously I've been able to delete a few rows, type in a few other entries, then re-type the amounts that were causing the problem again and it seems to fix it. Not today though. Any suggestions. Thanks -- Relle |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
If function not working correctly
"Relle" wrote:
Any other suggestions - I feel I'm ready to pull my hair out. If you cannot resolve the problem yourself, I suggest that you send me the Excel file. In the email message, let me know where to look in the Excel file. Send email to joeu2004 "at" hotmail.com. the first four all give me the same answer 2348.84 How can you get 84 cents from arithmetic involving only "whole numbers"? (2348 is a "whole number". 2348.84 is not.) Are you no longer talking about "whole numbers"? Does your arithmetic include division? Multiplication with fractional numbers (like interest)? In any case, the real question is: did you try Mike's solution using ROUND? If so, with what result (works or not)? the last two give me the result 0 Perhaps. But be careful. What I think you really mean is: the last two __appear__ to be zero. Remember: what you see displayed is usually __not__ what the actual value is, except for constants that you enter manually. If you enter 0.10, it __is__ 0.10 (within 15 significant digits). But if you subtract 10 from 10.10, it may or may not be exactly the same as the constant 0.10, even though it __appears__ to be, especially when formatted to only 2 decimal places. the problem even seems to be occuring when entering whole numbers without decimals - all data entered onto the spreadsheet are only two decimals as its actual money. As I demonstrated, even simply arithmetic combinations of "actual money" with only two decimal places can have unexpected results. Did you try my example (rewritten to look like "actual money"): =IF(10.10 - 10.00 = 0.01, TRUE) That returns FALSE (!). The following corrects the problem: =IF(ROUND(10.10 - 10.00, 2) = 0.01, TRUE) ----- original message ----- "Relle" wrote in message ... Hi David I've tried all them formulas - the first four all give me the same answer 2348.84, the last two give me the result 0. Double checked and the calculation mode is set to automatic. Any other suggestions - I feel I'm ready to pull my hair out. -- Relle "David Biddulph" wrote: If it's not the decimals, I could only suggest that you've made a mistake in your data (or that you don't have calculation mode set to Auto). What values do you have for the following formulae: =F2 =ROUND(F2,2) =SUM(G2:U2) =ROUND(SUM(G2:U2),2) =F2-ROUND(SUM(G2:U2),2) =ROUND(F2,2)-ROUND(SUM(G2:U2),2) ? -- David Biddulph "Relle" wrote in message ... The decimals is not the problem, the problem even seems to be occuring when entering whole numbers without decimals - all data entered onto the spreadsheet are only two decimals as its actual money. Any other suggestions? -- Relle "JoeU2004" wrote: "Mike H" wrote: =IF(ROUND(F2,2)=ROUND(SUM(G2:U2),2),"Balanced","No t Balanced") Just to expand on Mike's comments a bit.... The above solution will probably work. But it might be good practice to use ROUND in formulas prolifically, albeit prudently, throughout the worksheet; for example, in F2 and G2:U2, which Relle says are "all sum functions". Then the formula here can be simplified somewhat: =IF(F2=ROUND(SUM(G2:U2),2),"Balanced","Not Balanced") Relle wrote: I use a separate spreadsheet each month and the problem normally comes up towards the bottom of the page, (normally about 300 row entries), but this month it's cropped up after 6 rows. To appreciate the issue, try this as an experiment: enter =IF(10.1 - 10 = 0.1, TRUE). The result is FALSE (!). As you see, the problem can arise with as few as 2 values being added or subtracted. The reason is: most numbers with decimal fractions cannot be represented exactly. The approximated internal representations cause very subtle differences when combining constants in arithmetic expressions. Sometimes you can see these differences by formatting the cells so that you can see 15 significant digits (i.e. ignoring leading zeros). But sometimes even that does not reveal the differences. Sometimes the differences correct themselves when performing the arithmetic. Sometimes Excel adjusts the arithmetic results in a half-baked attempt to correct some subtle differences. But often, the differences are simply carried along and magnified from computation to computation until they make a significant difference. Relle wrote: I'm using 2 decimal places and have ensured the whole page is set as this. Formatting only affects the appearance of values in cells, not their actual values. Previously I've been able to delete a few rows, type in a few other entries, then re-type the amounts that were causing the problem again and it seems to fix it. Probably just by coincidence, depending on which values you entered manually. What the ROUND function does is: it ensures that the result matches the internal representation of the number as if you had entered it manually. ----- original message ----- "Mike H" wrote in message ... Hi, Because you have your cells set to 2 decimal places the sums may 'appear' balanced but may not be. For example 1.0234 1.0235 set to 2 decimal places bith display in the cell as 1.02 but they arent the same. So it depends on what level of equality you want. If 2 decimal places is enough try =IF(ROUND(F2,2)=ROUND(SUM(G2:U2),2),"Balanced","No t Balanced") You can increase the rounding to 3 if you want greater accuracy. Mike "Relle" wrote: Hi I'm using Excel 2003 Formula =IF(F2=SUM(G2:U2),"Balanced","Not Balanced") F2 and G2:U2 are all sum functions. The formula seems to work fine then all of a sudden I get a "Not Balanced" when the values are correct. I'm using 2 decimal places and have ensured the whole page is set as this. I use a separate spreadsheet each month and the problem normally comes up towards the bottom of the page, (normally about 300 row entries), but this month it's cropped up after 6 rows. Previously I've been able to delete a few rows, type in a few other entries, then re-type the amounts that were causing the problem again and it seems to fix it. Not today though. Any suggestions. Thanks -- Relle |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
If function not working correctly
FYI, after looking at the Excel file, I can see that the problem was exactly
what Mike, David and I had described. Mike's suggestion was sufficient for the short-term. My suggestion also works as a long-term approach. (Note: Relle sent me the worksheet with the original problem. I have not yet seen a worksheet with the problem she had with "whole numbers".) ----- original message ----- "JoeU2004" wrote in message ... "Relle" wrote: Any other suggestions - I feel I'm ready to pull my hair out. If you cannot resolve the problem yourself, I suggest that you send me the Excel file. In the email message, let me know where to look in the Excel file. Send email to joeu2004 "at" hotmail.com. the first four all give me the same answer 2348.84 How can you get 84 cents from arithmetic involving only "whole numbers"? (2348 is a "whole number". 2348.84 is not.) Are you no longer talking about "whole numbers"? Does your arithmetic include division? Multiplication with fractional numbers (like interest)? In any case, the real question is: did you try Mike's solution using ROUND? If so, with what result (works or not)? the last two give me the result 0 Perhaps. But be careful. What I think you really mean is: the last two __appear__ to be zero. Remember: what you see displayed is usually __not__ what the actual value is, except for constants that you enter manually. If you enter 0.10, it __is__ 0.10 (within 15 significant digits). But if you subtract 10 from 10.10, it may or may not be exactly the same as the constant 0.10, even though it __appears__ to be, especially when formatted to only 2 decimal places. the problem even seems to be occuring when entering whole numbers without decimals - all data entered onto the spreadsheet are only two decimals as its actual money. As I demonstrated, even simply arithmetic combinations of "actual money" with only two decimal places can have unexpected results. Did you try my example (rewritten to look like "actual money"): =IF(10.10 - 10.00 = 0.01, TRUE) That returns FALSE (!). The following corrects the problem: =IF(ROUND(10.10 - 10.00, 2) = 0.01, TRUE) ----- original message ----- "Relle" wrote in message ... Hi David I've tried all them formulas - the first four all give me the same answer 2348.84, the last two give me the result 0. Double checked and the calculation mode is set to automatic. Any other suggestions - I feel I'm ready to pull my hair out. -- Relle "David Biddulph" wrote: If it's not the decimals, I could only suggest that you've made a mistake in your data (or that you don't have calculation mode set to Auto). What values do you have for the following formulae: =F2 =ROUND(F2,2) =SUM(G2:U2) =ROUND(SUM(G2:U2),2) =F2-ROUND(SUM(G2:U2),2) =ROUND(F2,2)-ROUND(SUM(G2:U2),2) ? -- David Biddulph "Relle" wrote in message ... The decimals is not the problem, the problem even seems to be occuring when entering whole numbers without decimals - all data entered onto the spreadsheet are only two decimals as its actual money. Any other suggestions? -- Relle "JoeU2004" wrote: "Mike H" wrote: =IF(ROUND(F2,2)=ROUND(SUM(G2:U2),2),"Balanced","No t Balanced") Just to expand on Mike's comments a bit.... The above solution will probably work. But it might be good practice to use ROUND in formulas prolifically, albeit prudently, throughout the worksheet; for example, in F2 and G2:U2, which Relle says are "all sum functions". Then the formula here can be simplified somewhat: =IF(F2=ROUND(SUM(G2:U2),2),"Balanced","Not Balanced") Relle wrote: I use a separate spreadsheet each month and the problem normally comes up towards the bottom of the page, (normally about 300 row entries), but this month it's cropped up after 6 rows. To appreciate the issue, try this as an experiment: enter =IF(10.1 - 10 = 0.1, TRUE). The result is FALSE (!). As you see, the problem can arise with as few as 2 values being added or subtracted. The reason is: most numbers with decimal fractions cannot be represented exactly. The approximated internal representations cause very subtle differences when combining constants in arithmetic expressions. Sometimes you can see these differences by formatting the cells so that you can see 15 significant digits (i.e. ignoring leading zeros). But sometimes even that does not reveal the differences. Sometimes the differences correct themselves when performing the arithmetic. Sometimes Excel adjusts the arithmetic results in a half-baked attempt to correct some subtle differences. But often, the differences are simply carried along and magnified from computation to computation until they make a significant difference. Relle wrote: I'm using 2 decimal places and have ensured the whole page is set as this. Formatting only affects the appearance of values in cells, not their actual values. Previously I've been able to delete a few rows, type in a few other entries, then re-type the amounts that were causing the problem again and it seems to fix it. Probably just by coincidence, depending on which values you entered manually. What the ROUND function does is: it ensures that the result matches the internal representation of the number as if you had entered it manually. ----- original message ----- "Mike H" wrote in message ... Hi, Because you have your cells set to 2 decimal places the sums may 'appear' balanced but may not be. For example 1.0234 1.0235 set to 2 decimal places bith display in the cell as 1.02 but they arent the same. So it depends on what level of equality you want. If 2 decimal places is enough try =IF(ROUND(F2,2)=ROUND(SUM(G2:U2),2),"Balanced","No t Balanced") You can increase the rounding to 3 if you want greater accuracy. Mike "Relle" wrote: Hi I'm using Excel 2003 Formula =IF(F2=SUM(G2:U2),"Balanced","Not Balanced") F2 and G2:U2 are all sum functions. The formula seems to work fine then all of a sudden I get a "Not Balanced" when the values are correct. I'm using 2 decimal places and have ensured the whole page is set as this. I use a separate spreadsheet each month and the problem normally comes up towards the bottom of the page, (normally about 300 row entries), but this month it's cropped up after 6 rows. Previously I've been able to delete a few rows, type in a few other entries, then re-type the amounts that were causing the problem again and it seems to fix it. Not today though. Any suggestions. Thanks -- Relle |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
If function not working correctly
Thank you all for your help - sorry it took me so long to get the
understanding of what you were suggesting..... I'm having one of those weeks........ -- Relle "Joe User" wrote: FYI, after looking at the Excel file, I can see that the problem was exactly what Mike, David and I had described. Mike's suggestion was sufficient for the short-term. My suggestion also works as a long-term approach. (Note: Relle sent me the worksheet with the original problem. I have not yet seen a worksheet with the problem she had with "whole numbers".) ----- original message ----- "JoeU2004" wrote in message ... "Relle" wrote: Any other suggestions - I feel I'm ready to pull my hair out. If you cannot resolve the problem yourself, I suggest that you send me the Excel file. In the email message, let me know where to look in the Excel file. Send email to joeu2004 "at" hotmail.com. the first four all give me the same answer 2348.84 How can you get 84 cents from arithmetic involving only "whole numbers"? (2348 is a "whole number". 2348.84 is not.) Are you no longer talking about "whole numbers"? Does your arithmetic include division? Multiplication with fractional numbers (like interest)? In any case, the real question is: did you try Mike's solution using ROUND? If so, with what result (works or not)? the last two give me the result 0 Perhaps. But be careful. What I think you really mean is: the last two __appear__ to be zero. Remember: what you see displayed is usually __not__ what the actual value is, except for constants that you enter manually. If you enter 0.10, it __is__ 0.10 (within 15 significant digits). But if you subtract 10 from 10.10, it may or may not be exactly the same as the constant 0.10, even though it __appears__ to be, especially when formatted to only 2 decimal places. the problem even seems to be occuring when entering whole numbers without decimals - all data entered onto the spreadsheet are only two decimals as its actual money. As I demonstrated, even simply arithmetic combinations of "actual money" with only two decimal places can have unexpected results. Did you try my example (rewritten to look like "actual money"): =IF(10.10 - 10.00 = 0.01, TRUE) That returns FALSE (!). The following corrects the problem: =IF(ROUND(10.10 - 10.00, 2) = 0.01, TRUE) ----- original message ----- "Relle" wrote in message ... Hi David I've tried all them formulas - the first four all give me the same answer 2348.84, the last two give me the result 0. Double checked and the calculation mode is set to automatic. Any other suggestions - I feel I'm ready to pull my hair out. -- Relle "David Biddulph" wrote: If it's not the decimals, I could only suggest that you've made a mistake in your data (or that you don't have calculation mode set to Auto). What values do you have for the following formulae: =F2 =ROUND(F2,2) =SUM(G2:U2) =ROUND(SUM(G2:U2),2) =F2-ROUND(SUM(G2:U2),2) =ROUND(F2,2)-ROUND(SUM(G2:U2),2) ? -- David Biddulph "Relle" wrote in message ... The decimals is not the problem, the problem even seems to be occuring when entering whole numbers without decimals - all data entered onto the spreadsheet are only two decimals as its actual money. Any other suggestions? -- Relle "JoeU2004" wrote: "Mike H" wrote: =IF(ROUND(F2,2)=ROUND(SUM(G2:U2),2),"Balanced","No t Balanced") Just to expand on Mike's comments a bit.... The above solution will probably work. But it might be good practice to use ROUND in formulas prolifically, albeit prudently, throughout the worksheet; for example, in F2 and G2:U2, which Relle says are "all sum functions". Then the formula here can be simplified somewhat: =IF(F2=ROUND(SUM(G2:U2),2),"Balanced","Not Balanced") Relle wrote: I use a separate spreadsheet each month and the problem normally comes up towards the bottom of the page, (normally about 300 row entries), but this month it's cropped up after 6 rows. To appreciate the issue, try this as an experiment: enter =IF(10.1 - 10 = 0.1, TRUE). The result is FALSE (!). As you see, the problem can arise with as few as 2 values being added or subtracted. The reason is: most numbers with decimal fractions cannot be represented exactly. The approximated internal representations cause very subtle differences when combining constants in arithmetic expressions. Sometimes you can see these differences by formatting the cells so that you can see 15 significant digits (i.e. ignoring leading zeros). But sometimes even that does not reveal the differences. Sometimes the differences correct themselves when performing the arithmetic. Sometimes Excel adjusts the arithmetic results in a half-baked attempt to correct some subtle differences. But often, the differences are simply carried along and magnified from computation to computation until they make a significant difference. Relle wrote: I'm using 2 decimal places and have ensured the whole page is set as this. Formatting only affects the appearance of values in cells, not their actual values. Previously I've been able to delete a few rows, type in a few other entries, then re-type the amounts that were causing the problem again and it seems to fix it. Probably just by coincidence, depending on which values you entered manually. What the ROUND function does is: it ensures that the result matches the internal representation of the number as if you had entered it manually. ----- original message ----- "Mike H" wrote in message ... Hi, Because you have your cells set to 2 decimal places the sums may 'appear' balanced but may not be. For example 1.0234 1.0235 set to 2 decimal places bith display in the cell as 1.02 but they arent the same. So it depends on what level of equality you want. If 2 decimal places is enough try =IF(ROUND(F2,2)=ROUND(SUM(G2:U2),2),"Balanced","No t Balanced") You can increase the rounding to 3 if you want greater accuracy. Mike "Relle" wrote: Hi I'm using Excel 2003 Formula =IF(F2=SUM(G2:U2),"Balanced","Not Balanced") F2 and G2:U2 are all sum functions. The formula seems to work fine then all of a sudden I get a "Not Balanced" when the values are correct. I'm using 2 decimal places and have ensured the whole page is set as this. I use a separate spreadsheet each month and the problem normally comes up towards the bottom of the page, (normally about 300 row entries), but this month it's cropped up after 6 rows. Previously I've been able to delete a few rows, type in a few other entries, then re-type the amounts that were causing the problem again and it seems to fix it. Not today though. Any suggestions. Thanks -- Relle |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Offset function not working correctly | Excel Discussion (Misc queries) | |||
Fill down function not working correctly and producing weird resul | Excel Worksheet Functions | |||
Subtotal function is not working correctly | Excel Discussion (Misc queries) | |||
Lookup function still not working correctly | Excel Worksheet Functions | |||
Sum function not working correctly in Excel (Skips Cell) | Excel Worksheet Functions |