Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extra comma at the end of a function
I have the following simple funtion in a spreadsheet and noticed that it did
not produce the anticipated result when checking my numbers: =AVERAGE('specimen data'!B5,'specimen data'!B11,'specimen data'!B17,'specimen data'!B23,'specimen data'!B29,'specimen data'!B35,'specimen data'!B41,) The comma after the B41 cell seems to produce a different answer than anticipated and correct answer is produced when the comma is removed: =AVERAGE('specimen data'!B5,'specimen data'!B11,'specimen data'!B17,'specimen data'!B23,'specimen data'!B29,'specimen data'!B35,'specimen data'!B41) How is the last comma in the first function affecting the calculation? Is it including other cells, or is it ignoring cells in the function? Thanks, Scott |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extra comma at the end of a function
The extra comma creates an additional value of 0 for the AVERAGE function.
AVERAGE is the Sum of the listed values divided by the Count of the listed values. In this case, the Sum remains the same, since you're only adding 0, but the count is increased by 1. Let's say your values on your "specimen data" sheet are 1, 2, 3, 4, 5, 6 and 7. The sum of this range is 28. Divided by the number of values, 7. The AVERAGE would be 4. But, adding that extra comma, you now have the values 1, 2, 3, 4, 5, 6, 7 and 0. The sum is still 28, but the number of values is now 8. Thus, the AVERAGE would be 3.5. Make sense? HTH, Elkar "SYerby" wrote: I have the following simple funtion in a spreadsheet and noticed that it did not produce the anticipated result when checking my numbers: =AVERAGE('specimen data'!B5,'specimen data'!B11,'specimen data'!B17,'specimen data'!B23,'specimen data'!B29,'specimen data'!B35,'specimen data'!B41,) The comma after the B41 cell seems to produce a different answer than anticipated and correct answer is produced when the comma is removed: =AVERAGE('specimen data'!B5,'specimen data'!B11,'specimen data'!B17,'specimen data'!B23,'specimen data'!B29,'specimen data'!B35,'specimen data'!B41) How is the last comma in the first function affecting the calculation? Is it including other cells, or is it ignoring cells in the function? Thanks, Scott |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extra comma at the end of a function
It's probably looking for the next cell to include in the average but there
is no more information thus you are getting the weird results. You would have to look at the result the bad formula gave you and then the correct result to try and figure out what data was being added or withheld from the bad formula result. "SYerby" wrote: I have the following simple funtion in a spreadsheet and noticed that it did not produce the anticipated result when checking my numbers: =AVERAGE('specimen data'!B5,'specimen data'!B11,'specimen data'!B17,'specimen data'!B23,'specimen data'!B29,'specimen data'!B35,'specimen data'!B41,) The comma after the B41 cell seems to produce a different answer than anticipated and correct answer is produced when the comma is removed: =AVERAGE('specimen data'!B5,'specimen data'!B11,'specimen data'!B17,'specimen data'!B23,'specimen data'!B29,'specimen data'!B35,'specimen data'!B41) How is the last comma in the first function affecting the calculation? Is it including other cells, or is it ignoring cells in the function? Thanks, Scott |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extra comma at the end of a function
AVEARGE is effectively SUM/COUNT. By having the extra comma, you are
increasing the COUNT by 1. SUM is not altered because it isn't referencing anything, but you get the wrong answer. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "SYerby" wrote in message ... I have the following simple funtion in a spreadsheet and noticed that it did not produce the anticipated result when checking my numbers: =AVERAGE('specimen data'!B5,'specimen data'!B11,'specimen data'!B17,'specimen data'!B23,'specimen data'!B29,'specimen data'!B35,'specimen data'!B41,) The comma after the B41 cell seems to produce a different answer than anticipated and correct answer is produced when the comma is removed: =AVERAGE('specimen data'!B5,'specimen data'!B11,'specimen data'!B17,'specimen data'!B23,'specimen data'!B29,'specimen data'!B35,'specimen data'!B41) How is the last comma in the first function affecting the calculation? Is it including other cells, or is it ignoring cells in the function? Thanks, Scott |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
extra results in the MAX function | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |