Baffling Sum/Average Behavior
I am seeing some strange behavior from the SUM() and AVERAGE()
functions. To most efficiently convey the problem here is a summary of the calculations I've tried within excel: Cell A1: 24 Cell B1: 6 SUM(A1:B1) 6 SUM(A1,B1) 6 A1+B1 30 AVERAGE(A1:B1) 6 (A1+B1)/2 15 SIN(A1) -0.905578362 SIN(24) -0.905578362 A1 / 2 12 A1 + 2 26 So everything works fine for arithmetic operations, and even a SIN() function but the SUM() and AVERAGE() functions are not providing accurate results. I can cut/paste cells into a clean sheet or ‘paste special…' as values and get the same behavior. I've also tried changing everything to general format with no effect. If I just retype 24 into the A1 suddenly everything works fine. The obvious answer is that cell A1 is a text value but the question is how did it get that way? I manually typed 10 rows of data each with 25 columns and when I went through and did a check there were about 10 values at various locations exhibiting this behavior. These values were all in the different rows/columns and everything was in general format. Also, if it was actually a text value, why does the SIN() function and simple arithmetic operations work but the summation and average do not? This is quite disturbing. I've used these functions in the same way on countless worksheets! It's scary to thing that this was happening all along and I just never caught it. Any insights? Any suggestions as far as checking existing sheets for cells that are behaving this way? Thanks, Russ D. |
A1 is seen as text, it can be coerced by calculating thus the A1+B1 will
work but =SUM(A1:B1) ignores text. Copy an empty cell, select A1, do editpaste special and select add, now the SUM should work -- Regards, Peo Sjoblom "Russ" wrote in message m... I am seeing some strange behavior from the SUM() and AVERAGE() functions. To most efficiently convey the problem here is a summary of the calculations I've tried within excel: Cell A1: 24 Cell B1: 6 SUM(A1:B1) 6 SUM(A1,B1) 6 A1+B1 30 AVERAGE(A1:B1) 6 (A1+B1)/2 15 SIN(A1) -0.905578362 SIN(24) -0.905578362 A1 / 2 12 A1 + 2 26 So everything works fine for arithmetic operations, and even a SIN() function but the SUM() and AVERAGE() functions are not providing accurate results. I can cut/paste cells into a clean sheet or 'paste special.' as values and get the same behavior. I've also tried changing everything to general format with no effect. If I just retype 24 into the A1 suddenly everything works fine. The obvious answer is that cell A1 is a text value but the question is how did it get that way? I manually typed 10 rows of data each with 25 columns and when I went through and did a check there were about 10 values at various locations exhibiting this behavior. These values were all in the different rows/columns and everything was in general format. Also, if it was actually a text value, why does the SIN() function and simple arithmetic operations work but the summation and average do not? This is quite disturbing. I've used these functions in the same way on countless worksheets! It's scary to thing that this was happening all along and I just never caught it. Any insights? Any suggestions as far as checking existing sheets for cells that are behaving this way? Thanks, Russ D. |
All times are GMT +1. The time now is 12:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com