|
Strange and inconsistent result from a simple formula
Check through your formulas as it may require quite close scrutiny. Also,
be sure you don't have circular references as this could very well be causing such symptoms. Without us knowing what the formulas are that you are attempting to use in columns G through R, it would be rather tough to help find the issue. As you trace this backwards, it can be rather time consuming and tedious depending on the scale and complexity of your formulas and data. Ronald R. Dodge, Jr. Master MOUS 2000 "Jack Sadie" wrote in message ... In order to assist in explaining this problem, I have been trying to add a jpg extract of the spreadsheet, but this seems to result in the message not arriving on the newsgroup - presumably because of the danger of virus in an attachment. I am trying again without the attachment, but sorry - it will need your close attention to understand it. I have a cash analysis book in the form of an Excel spreadsheet. The Cell F64 at the bottom of page 1 is the sum of a column of figures (Actual value £12484.70) Each cell in column F is the sum of any items from columns G to R in the same row Likewise each cell G64 to R64 inclusive is the sum of that relevant column. In order to check the validity of each row I have a dedicated check column "E", every cell of which carries the same relative formula. Thus the simple formula in E64 reads :- =IF(F64-SUM(G64:R64)=0,"","Problem") With certain exceptions, the value of each cell in column E therefore reads blank as it should do. The exceptions include E64 which reads "Problem" BUT...I have introduced a formula in cell S64 : "=SUM(G64:R64)" and this gives value of £12484.70 which equals the value of Cell F64 and therefore means that there is no problem !! I am therefore totally perplexed why Cell E64 indicates there is a problem ? This difficulty does not occur at row 127, the similar row at the bottom of the next page (page 2), but does again at row 155 which is the row of totals at the end of the year in the middle of the 3rd page. Hope I have explained adequately. What is my error? Can anyone help please ? -- Regards, Jack Sadie |
Strange and inconsistent result from a simple formula
Try to wrap the cells with ROUND
=IF(ROUND(F64,2)-ROUND(SUM(G64:R64),2)=0,"","Problem") -- Regards, Peo Sjoblom "Jack Sadie" wrote in message ... In order to assist in explaining this problem, I have been trying to add a jpg extract of the spreadsheet, but this seems to result in the message not arriving on the newsgroup - presumably because of the danger of virus in an attachment. I am trying again without the attachment, but sorry - it will need your close attention to understand it. I have a cash analysis book in the form of an Excel spreadsheet. The Cell F64 at the bottom of page 1 is the sum of a column of figures (Actual value £12484.70) Each cell in column F is the sum of any items from columns G to R in the same row Likewise each cell G64 to R64 inclusive is the sum of that relevant column. In order to check the validity of each row I have a dedicated check column "E", every cell of which carries the same relative formula. Thus the simple formula in E64 reads :- =IF(F64-SUM(G64:R64)=0,"","Problem") With certain exceptions, the value of each cell in column E therefore reads blank as it should do. The exceptions include E64 which reads "Problem" BUT...I have introduced a formula in cell S64 : "=SUM(G64:R64)" and this gives value of £12484.70 which equals the value of Cell F64 and therefore means that there is no problem !! I am therefore totally perplexed why Cell E64 indicates there is a problem ? This difficulty does not occur at row 127, the similar row at the bottom of the next page (page 2), but does again at row 155 which is the row of totals at the end of the year in the middle of the 3rd page. Hope I have explained adequately. What is my error? Can anyone help please ? -- Regards, Jack Sadie |
Strange and inconsistent result from a simple formula
Thanks ever so much, Peo.
Well I copied that off and pasted it in place of my formula, and I am pleased to say it has been effective; at least it now shows a blank cell where previously it read "Problem". Likewise I have copied that to the other problem cells with similar success. And as a check if I change one of the cells in a random position to deliberately force an error, it clearly gives the required result and delivers the word "Problem" !!!! So again my very sincere thanks. BUT, please can you tell me why that is so ? What was wrong with the formula I was using? I am not familiar with the function "Round", but I thought it had to do with rounding to a given number of digits after the decimal place, or is that off the mark? Also should I therefore be using that formula for every cell in the column or just those cells which are dealing with column totals at the bottom of each page? -- Regards, Jack Sadie "Peo Sjoblom" wrote in message ... Try to wrap the cells with ROUND =IF(ROUND(F64,2)-ROUND(SUM(G64:R64),2)=0,"","Problem") -- Regards, Peo Sjoblom "Jack Sadie" wrote in message ... In order to assist in explaining this problem, I have been trying to add a jpg extract of the spreadsheet, but this seems to result in the message not arriving on the newsgroup - presumably because of the danger of virus in an attachment. I am trying again without the attachment, but sorry - it will need your close attention to understand it. I have a cash analysis book in the form of an Excel spreadsheet. The Cell F64 at the bottom of page 1 is the sum of a column of figures (Actual value £12484.70) Each cell in column F is the sum of any items from columns G to R in the same row Likewise each cell G64 to R64 inclusive is the sum of that relevant column. In order to check the validity of each row I have a dedicated check column "E", every cell of which carries the same relative formula. Thus the simple formula in E64 reads :- =IF(F64-SUM(G64:R64)=0,"","Problem") With certain exceptions, the value of each cell in column E therefore reads blank as it should do. The exceptions include E64 which reads "Problem" BUT...I have introduced a formula in cell S64 : "=SUM(G64:R64)" and this gives value of £12484.70 which equals the value of Cell F64 and therefore means that there is no problem !! I am therefore totally perplexed why Cell E64 indicates there is a problem ? This difficulty does not occur at row 127, the similar row at the bottom of the next page (page 2), but does again at row 155 which is the row of totals at the end of the year in the middle of the 3rd page. Hope I have explained adequately. What is my error? Can anyone help please ? -- Regards, Jack Sadie |
Strange and inconsistent result from a simple formula
Most likely you had extra decimals due to the way computers treat numbers
If you use the sum formula where you had a discrepancy without the ROUND, then copy it as values and format as general and widen the column you might find some extra decimals http://www.mcgimpsey.com/excel/pennyoff.html http://www.cpearson.com/excel/rounding.htm -- Regards, Peo Sjoblom "Jack Sadie" wrote in message ... Thanks ever so much, Peo. Well I copied that off and pasted it in place of my formula, and I am pleased to say it has been effective; at least it now shows a blank cell where previously it read "Problem". Likewise I have copied that to the other problem cells with similar success. And as a check if I change one of the cells in a random position to deliberately force an error, it clearly gives the required result and delivers the word "Problem" !!!! So again my very sincere thanks. BUT, please can you tell me why that is so ? What was wrong with the formula I was using? I am not familiar with the function "Round", but I thought it had to do with rounding to a given number of digits after the decimal place, or is that off the mark? Also should I therefore be using that formula for every cell in the column or just those cells which are dealing with column totals at the bottom of each page? -- Regards, Jack Sadie "Peo Sjoblom" wrote in message ... Try to wrap the cells with ROUND =IF(ROUND(F64,2)-ROUND(SUM(G64:R64),2)=0,"","Problem") -- Regards, Peo Sjoblom "Jack Sadie" wrote in message ... In order to assist in explaining this problem, I have been trying to add a jpg extract of the spreadsheet, but this seems to result in the message not arriving on the newsgroup - presumably because of the danger of virus in an attachment. I am trying again without the attachment, but sorry - it will need your close attention to understand it. I have a cash analysis book in the form of an Excel spreadsheet. The Cell F64 at the bottom of page 1 is the sum of a column of figures (Actual value £12484.70) Each cell in column F is the sum of any items from columns G to R in the same row Likewise each cell G64 to R64 inclusive is the sum of that relevant column. In order to check the validity of each row I have a dedicated check column "E", every cell of which carries the same relative formula. Thus the simple formula in E64 reads :- =IF(F64-SUM(G64:R64)=0,"","Problem") With certain exceptions, the value of each cell in column E therefore reads blank as it should do. The exceptions include E64 which reads "Problem" BUT...I have introduced a formula in cell S64 : "=SUM(G64:R64)" and this gives value of £12484.70 which equals the value of Cell F64 and therefore means that there is no problem !! I am therefore totally perplexed why Cell E64 indicates there is a problem ? This difficulty does not occur at row 127, the similar row at the bottom of the next page (page 2), but does again at row 155 which is the row of totals at the end of the year in the middle of the 3rd page. Hope I have explained adequately. What is my error? Can anyone help please ? -- Regards, Jack Sadie |
Strange and inconsistent result from a simple formula
OK thanks. I'll have a look into that.
Cheers. -- Regards, Jack Sadie "Peo Sjoblom" wrote in message ... Most likely you had extra decimals due to the way computers treat numbers If you use the sum formula where you had a discrepancy without the ROUND, then copy it as values and format as general and widen the column you might find some extra decimals http://www.mcgimpsey.com/excel/pennyoff.html http://www.cpearson.com/excel/rounding.htm -- Regards, Peo Sjoblom "Jack Sadie" wrote in message ... Thanks ever so much, Peo. Well I copied that off and pasted it in place of my formula, and I am pleased to say it has been effective; at least it now shows a blank cell where previously it read "Problem". Likewise I have copied that to the other problem cells with similar success. And as a check if I change one of the cells in a random position to deliberately force an error, it clearly gives the required result and delivers the word "Problem" !!!! So again my very sincere thanks. BUT, please can you tell me why that is so ? What was wrong with the formula I was using? I am not familiar with the function "Round", but I thought it had to do with rounding to a given number of digits after the decimal place, or is that off the mark? Also should I therefore be using that formula for every cell in the column or just those cells which are dealing with column totals at the bottom of each page? -- Regards, Jack Sadie "Peo Sjoblom" wrote in message ... Try to wrap the cells with ROUND =IF(ROUND(F64,2)-ROUND(SUM(G64:R64),2)=0,"","Problem") -- Regards, Peo Sjoblom "Jack Sadie" wrote in message ... In order to assist in explaining this problem, I have been trying to add a jpg extract of the spreadsheet, but this seems to result in the message not arriving on the newsgroup - presumably because of the danger of virus in an attachment. I am trying again without the attachment, but sorry - it will need your close attention to understand it. I have a cash analysis book in the form of an Excel spreadsheet. The Cell F64 at the bottom of page 1 is the sum of a column of figures (Actual value £12484.70) Each cell in column F is the sum of any items from columns G to R in the same row Likewise each cell G64 to R64 inclusive is the sum of that relevant column. In order to check the validity of each row I have a dedicated check column "E", every cell of which carries the same relative formula. Thus the simple formula in E64 reads :- =IF(F64-SUM(G64:R64)=0,"","Problem") With certain exceptions, the value of each cell in column E therefore reads blank as it should do. The exceptions include E64 which reads "Problem" BUT...I have introduced a formula in cell S64 : "=SUM(G64:R64)" and this gives value of £12484.70 which equals the value of Cell F64 and therefore means that there is no problem !! I am therefore totally perplexed why Cell E64 indicates there is a problem ? This difficulty does not occur at row 127, the similar row at the bottom of the next page (page 2), but does again at row 155 which is the row of totals at the end of the year in the middle of the 3rd page. Hope I have explained adequately. What is my error? Can anyone help please ? -- Regards, Jack Sadie |
Strange and inconsistent result from a simple formula
Assuming that you are only adding and subtracting numbers with no more than 2
decimal places, your original formulas would have been fine if you had used numbers like 1248470 (hundredths of pounds) instead of 12484.70 (pounds). Excel's arithmetic is correct, but most decimal fractions can only be approximated in binary (as discussed in Peo's 2nd link), and your final formula is detecting the residual effects of these initial approximations to your inputs. Integers (up to 15 digits) can be exactly represented, but the only 2-place decimal fractions that can be exactly represented are .00, .25, ..50, and .75. Peo's suggestion of rounding intermediate results then works because it is reducing the impact of these approximations. If you throw multiplication/division into the mix (such as interest calculations, etc) then you also need to be aware that Excel retains its full precision despite how you may have formatted the cells (as discussed in Peo's first link). Jerry "Jack Sadie" wrote: Thanks ever so much, Peo. Well I copied that off and pasted it in place of my formula, and I am pleased to say it has been effective; at least it now shows a blank cell where previously it read "Problem". Likewise I have copied that to the other problem cells with similar success. And as a check if I change one of the cells in a random position to deliberately force an error, it clearly gives the required result and delivers the word "Problem" !!!! So again my very sincere thanks. BUT, please can you tell me why that is so ? What was wrong with the formula I was using? I am not familiar with the function "Round", but I thought it had to do with rounding to a given number of digits after the decimal place, or is that off the mark? Also should I therefore be using that formula for every cell in the column or just those cells which are dealing with column totals at the bottom of each page? -- Regards, Jack Sadie "Peo Sjoblom" wrote in message ... Try to wrap the cells with ROUND =IF(ROUND(F64,2)-ROUND(SUM(G64:R64),2)=0,"","Problem") -- Regards, Peo Sjoblom "Jack Sadie" wrote in message ... In order to assist in explaining this problem, I have been trying to add a jpg extract of the spreadsheet, but this seems to result in the message not arriving on the newsgroup - presumably because of the danger of virus in an attachment. I am trying again without the attachment, but sorry - it will need your close attention to understand it. I have a cash analysis book in the form of an Excel spreadsheet. The Cell F64 at the bottom of page 1 is the sum of a column of figures (Actual value £12484.70) Each cell in column F is the sum of any items from columns G to R in the same row Likewise each cell G64 to R64 inclusive is the sum of that relevant column. In order to check the validity of each row I have a dedicated check column "E", every cell of which carries the same relative formula. Thus the simple formula in E64 reads :- =IF(F64-SUM(G64:R64)=0,"","Problem") With certain exceptions, the value of each cell in column E therefore reads blank as it should do. The exceptions include E64 which reads "Problem" BUT...I have introduced a formula in cell S64 : "=SUM(G64:R64)" and this gives value of £12484.70 which equals the value of Cell F64 and therefore means that there is no problem !! I am therefore totally perplexed why Cell E64 indicates there is a problem ? This difficulty does not occur at row 127, the similar row at the bottom of the next page (page 2), but does again at row 155 which is the row of totals at the end of the year in the middle of the 3rd page. Hope I have explained adequately. What is my error? Can anyone help please ? -- Regards, Jack Sadie |
Strange and inconsistent result from a simple formula
Jack
If you have many of these to add the ROUND function to, here's a bit of code to help. Sub RoundAdd() Dim myStr As String Dim cel As Range For Each cel In Selection If cel.HasFormula = True Then If Not cel.Formula Like "=ROUND(*" Then myStr = Right(cel.Formula, Len(cel.Formula) - 1) cel.Value = "=ROUND(" & myStr & ",2)" End If End If Next End Sub Gord Dibben MS Excel MVP On Tue, 15 May 2007 01:05:25 +0100, "Jack Sadie" wrote: OK thanks. I'll have a look into that. Cheers. |
Strange and inconsistent result from a simple formula
As it happens there aren't many, but thanks so much for your trouble. I'll
store it for the future. -- Regards, Jack Sadie "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Jack If you have many of these to add the ROUND function to, here's a bit of code to help. Sub RoundAdd() Dim myStr As String Dim cel As Range For Each cel In Selection If cel.HasFormula = True Then If Not cel.Formula Like "=ROUND(*" Then myStr = Right(cel.Formula, Len(cel.Formula) - 1) cel.Value = "=ROUND(" & myStr & ",2)" End If End If Next End Sub Gord Dibben MS Excel MVP On Tue, 15 May 2007 01:05:25 +0100, "Jack Sadie" wrote: OK thanks. I'll have a look into that. Cheers. |
Strange and inconsistent result from a simple formula
Really grateful for that generous explanation. I think that also answers my
question regarding the other cells - seems like a good idea to use the rounding in each cell of the check column. -- Regards, Jack Sadie "Jerry W. Lewis" wrote in message ... Assuming that you are only adding and subtracting numbers with no more than 2 decimal places, your original formulas would have been fine if you had used numbers like 1248470 (hundredths of pounds) instead of 12484.70 (pounds). Excel's arithmetic is correct, but most decimal fractions can only be approximated in binary (as discussed in Peo's 2nd link), and your final formula is detecting the residual effects of these initial approximations to your inputs. Integers (up to 15 digits) can be exactly represented, but the only 2-place decimal fractions that can be exactly represented are .00, .25, .50, and .75. Peo's suggestion of rounding intermediate results then works because it is reducing the impact of these approximations. If you throw multiplication/division into the mix (such as interest calculations, etc) then you also need to be aware that Excel retains its full precision despite how you may have formatted the cells (as discussed in Peo's first link). Jerry "Jack Sadie" wrote: Thanks ever so much, Peo. Well I copied that off and pasted it in place of my formula, and I am pleased to say it has been effective; at least it now shows a blank cell where previously it read "Problem". Likewise I have copied that to the other problem cells with similar success. And as a check if I change one of the cells in a random position to deliberately force an error, it clearly gives the required result and delivers the word "Problem" !!!! So again my very sincere thanks. BUT, please can you tell me why that is so ? What was wrong with the formula I was using? I am not familiar with the function "Round", but I thought it had to do with rounding to a given number of digits after the decimal place, or is that off the mark? Also should I therefore be using that formula for every cell in the column or just those cells which are dealing with column totals at the bottom of each page? -- Regards, Jack Sadie "Peo Sjoblom" wrote in message ... Try to wrap the cells with ROUND =IF(ROUND(F64,2)-ROUND(SUM(G64:R64),2)=0,"","Problem") -- Regards, Peo Sjoblom "Jack Sadie" wrote in message ... In order to assist in explaining this problem, I have been trying to add a jpg extract of the spreadsheet, but this seems to result in the message not arriving on the newsgroup - presumably because of the danger of virus in an attachment. I am trying again without the attachment, but sorry - it will need your close attention to understand it. I have a cash analysis book in the form of an Excel spreadsheet. The Cell F64 at the bottom of page 1 is the sum of a column of figures (Actual value £12484.70) Each cell in column F is the sum of any items from columns G to R in the same row Likewise each cell G64 to R64 inclusive is the sum of that relevant column. In order to check the validity of each row I have a dedicated check column "E", every cell of which carries the same relative formula. Thus the simple formula in E64 reads :- =IF(F64-SUM(G64:R64)=0,"","Problem") With certain exceptions, the value of each cell in column E therefore reads blank as it should do. The exceptions include E64 which reads "Problem" BUT...I have introduced a formula in cell S64 : "=SUM(G64:R64)" and this gives value of £12484.70 which equals the value of Cell F64 and therefore means that there is no problem !! I am therefore totally perplexed why Cell E64 indicates there is a problem ? This difficulty does not occur at row 127, the similar row at the bottom of the next page (page 2), but does again at row 155 which is the row of totals at the end of the year in the middle of the 3rd page. Hope I have explained adequately. What is my error? Can anyone help please ? -- Regards, Jack Sadie |
| All times are GMT +1. The time now is 05:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com