Multiple Worksheets & Column addition error
Posted by Diana on January 30, 2002 8:27 AM
I have a rather large workbook/sheet that combines (totals) 3 other workbooks with 17 worksheets each.
It is working well transferring all the info to the one workbook/sheet, but when I have the columns total the sum is off anywhere by anywhere from 1 to 4. I thought it might be a rounding problem, so I changed it to see and it doesn't appear to affect the final total.
Does anyone know why this worksheet won't total the actual numbers in each cell (even though they refer to other workbooks/cells)?
Thanks in advance for any help at all! :)
Posted by Mark W. on January 30, 2002 8:41 AM
What formula are you using for your totals? [nt]
Posted by Diana on January 30, 2002 9:02 AM
Re: What formula are you using for your totals? [nt]
=SUM(E3:E19)-E19-E4
The more I work with this, the more totals I find are wrong! The actual number showing in each cell is correct, but when a formula is applied in a different column, the answer comes out wrong. It's not just addition, either.
Posted by Diana on January 30, 2002 9:02 AM
Re: What formula are you using for your totals? [nt]
=SUM(E3:E19)-E19-E4
The more I work with this, the more totals I find are wrong! The actual number showing in each cell is correct, but when a formula is applied in a different column, the answer comes out wrong. It's not just addition, either.
Posted by Mark W. on January 30, 2002 9:30 AM
Re: What formula are you using for your totals? [nt]
After selecting the cell containing your formula,
select (highlight) E3:E19 on the formula bar and
press F9. This produce an array constant
representing the contents of E3:E19. Are any
of the values enclosed by quotes (e.g. "10")?
Posted by Diana on January 30, 2002 10:01 AM
Re: What formula are you using for your totals? [nt]
After doing as you suggested, I see that all the numbers in that column have 9 decimal places! I think the original numbers, which are on other worksheets in other workbooks were entered with 3 decimal places.
Is there any way to get Excel to just use the numbers that are input, instead of it "over thinking" it? <G></div>
Posted by Mark W. on January 30, 2002 10:58 AM
Re: What formula are you using for your totals? [nt]
Excel doesn't "over think". If you enter the
formula =1/3 Excel will return 0.333333333333333
which IS one third. If you want to control the
precision that Excel employs consider incorporating
the ROUND worksheet function into your formula.
=ROUND(1/3,2) will turn 0.33.
