Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
GEOMEAN Function
First of all, I'm no software expert, however, I have a question. I am
trying to run the geomean for a list of numbers. The numbers are in a range that includes more than 200 numbers. However, I get an error message whenever I use the GEOMEAN function for more than 117 cells. Is there a way to get past this limit?? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
GEOMEAN Function
GEOMEAN
is for positive numbers, be sure there are no negatives in your range.this will throw you an error, but you should be able to get past 117 cells in a column easily. "KD" wrote: First of all, I'm no software expert, however, I have a question. I am trying to run the geomean for a list of numbers. The numbers are in a range that includes more than 200 numbers. However, I get an error message whenever I use the GEOMEAN function for more than 117 cells. Is there a way to get past this limit?? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
GEOMEAN Function
KD wrote:
First of all, I'm no software expert, however, I have a question. I am trying to run the geomean for a list of numbers. The numbers are in a range that includes more than 200 numbers. However, I get an error message whenever I use the GEOMEAN function for more than 117 cells. Is there a way to get past this limit?? Excel appears to flake out whenever the product of the numbers in GEOMEAN overflows a basic IEEE floating point representation. 1.7E+308 Odd really since in other algorithms they hold intermediate results in temporary reals which would give the naieve algorithm a lot more headroom 1.1E+4932 Provided your numbers are all positive and non zero (and if they are not it is pretty weird to use GEOMEAN) you can compute the same result fairly easily. Take logs compute average, then exponentiate. {=10^AVERAGE(LOG(A$1:A200)) Should do the trick entered as an array formula. Regards, Martin Brown |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
GEOMEAN Function
"KD" wrote:
I am trying to run the geomean for a list of numbers. The numbers are in a range that includes more than 200 numbers. However, I get an error message whenever I use the GEOMEAN function for more than 117 cells. Is there a way to get past this limit?? What exactly are you doing? That is, exactly what does your GEOMEAN() function usage look like? What version of Excel are you using? Exactly what error message do you get? And what is the range of your values (min, max)? I have no problem with GEOMEAN(A1:A200) using Excel 2003, where A1:A200 ranges from 0.00543 to 1.964284. By the way, if the parameters of GEOMEAN() represent a series of ratios y[1]=a[1]/a[0], y[2]=a[2]/a[1],..., y[n]=a[n]/a[n-1], the geometric mean can be computed simply by (a[n]/a[0])^(1/n). This might reduce computational error due to computer arithmetic that involves a series of n multiplications (y[1]*y[2]*...*y[n]). |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
GEOMEAN Function
Martin Brown wrote...
.... Excel appears to flake out whenever the product of the numbers in GEOMEAN overflows a basic IEEE floating point representation. 1.7E+308 Odd really since in other algorithms they hold intermediate results in temporary reals which would give the naieve algorithm a lot more headroom 1.1E+4932 .... Odder still since the geometric mean is just e raised to power of the average of the logarithms or the product of the Nth roots of N values. 80-bit reals aren't necessary for better results. Better algorithms would have provided ample robustness. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
GEOMEAN Function
Harlan Grove wrote:
Martin Brown wrote... ... Excel appears to flake out whenever the product of the numbers in GEOMEAN overflows a basic IEEE floating point representation. 1.7E+308 Odd really since in other algorithms they hold intermediate results in temporary reals which would give the naieve algorithm a lot more headroom 1.1E+4932 ... Odder still since the geometric mean is just e raised to power of the average of the logarithms or the product of the Nth roots of N values. 80-bit reals aren't necessary for better results. Better algorithms would have provided ample robustness. Yes. What I was commenting on was that they have implemented a very bad algorithm in an absolutely disastrous fashion. Two mistakes compounded! Another one to add to the Mickeysoft hall of statistical infamy. Regards, Martin Brown |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
GEOMEAN Function
I'm using Excel 2002. Probably need to upgrade. . .The numbers are a range
of simple numbers, all positive, no zeros or blank cells, The ranges I'm using the geomean function are between a range of three numbers and 234 numbers. . .works every time except when the number of cells go over 117. The error message I get is the #NUM! message. I will try using the log and exponent approach suggested by another input. Thanks, " wrote: "KD" wrote: I am trying to run the geomean for a list of numbers. The numbers are in a range that includes more than 200 numbers. However, I get an error message whenever I use the GEOMEAN function for more than 117 cells. Is there a way to get past this limit?? What exactly are you doing? That is, exactly what does your GEOMEAN() function usage look like? What version of Excel are you using? Exactly what error message do you get? And what is the range of your values (min, max)? I have no problem with GEOMEAN(A1:A200) using Excel 2003, where A1:A200 ranges from 0.00543 to 1.964284. By the way, if the parameters of GEOMEAN() represent a series of ratios y[1]=a[1]/a[0], y[2]=a[2]/a[1],..., y[n]=a[n]/a[n-1], the geometric mean can be computed simply by (a[n]/a[0])^(1/n). This might reduce computational error due to computer arithmetic that involves a series of n multiplications (y[1]*y[2]*...*y[n]). |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
GEOMEAN Function
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
GEOMEAN Function
"Harlan Grove" wrote:
And with a mix straddling unity you shouldn't have much of a problem with it. But it's EASY to break. Fill A1:A20 with the formula =RAND()^25. [...] GEOMEAN not only chokes on overflow (in which case it returns #NUM!), it also chokes on underflow (it returns 0). Sure! But since the OP failed to explain the error he got, I wanted to address his apparent belief that there was limit of 117 per se. The OP wrote: "whenever I use the GEOMEAN function for more than 117 cells. Is there a way to get past this limit?". I also wanted to emphasize the general need for people who encounter problems to provide sufficient details in order to reasonably identify the "root cause" of the problem. (I sometimes forget myself.) That said, I personally never use GEOMEAN(). I prefer to compute the average of the log values. That lends itself easily to also computing the std dev and std err, which are useful in some applications of the geometric mean. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date & Time | New Users to Excel | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions | |||
Find a Function to use accross different worksheets | Excel Worksheet Functions |