Feb 09 2023 06:07 PM
I'm getting the #NUM! error when I try to calculate the Geometric Mean of a column of values. I've confirmed there's no values <= 0 and cleared blanks, but it doesn't help. I can successfully calculate Geomean up to a certain row ( e.g. A2:A2062), but as soon as I expend past that row (e.g. A2:A2065), I get the num error. I have three columns of data that I am calculating the geomean of and this error occurs for two of them. In the two "bad" columns, he error is triggered at different cells(Cell A2063 and Cell C2076 in the other). The third column works fine and will allow me to extend the range past the end of the data without giving an error.
I can't see any issues with the values in the cells and have tried changing their format from "general' to "number" and also tried to delete the values and retype them in.
Any suggestions greatly appreciated.
Regards,
Paul
Feb 09 2023 08:58 PM
You first step should be to consult the help article on the function and not asking a question on a forum.
if any data point ≤ 0, GEOMEAN returns the #NUM! error value.
Feb 09 2023 10:23 PM
Hi Detlef, thank you for this advice. However, I did read the help article and that was the first thing that I checked. NONE of the input values were <= 0! If you check my post, you will see that I actually said this in my explanation. For the record, the values vary between 0.02 and 39.64.
So, if you have a more useful solution, I'd be glad to hear it.
Feb 09 2023 11:13 PM
Feb 10 2023 12:45 AM
Happy to share, but can't see how to do this. Is there supposed to be an icon to upload files in this system?
Can't create a link due to organisation restrictions.
Feb 10 2023 01:00 AM - edited Feb 10 2023 11:32 PM
Solution
(Edited with a simpler demonstration)
It does not take much to demonstrate a situation where GEOMEAN of 2060+ data points returns #NUM.
Select A2:A2067, type 1.41, then press ctrl+Enter (not ctrl+shift+Enter) to enter 1.41 into all cells.
Note that =GEOMEAN(A2:A2066) returns 1.41. But =GEOMEAN(A2:A2067) returns #NUM.
The reason is =PRODUCT(A2:A2066) returns 1.372E+308 (rounded).
But =PRODUCT(A2:A2067) returns #NUM because 1.41*1.372E+308 would be 1.935E+308 (rounded), which exceeds the limit of 64-bit binary floating-point of less than 1.80E+308.
A work-around is:
=10^AVERAGE(LOG(A2:A2067))
If the range might have non-numeric data or empty cells:
=10^AVERAGE(IF(ISNUMBER(A2:A3000),LOG(A2:A3000)))
The latter formula must be array-entered in some versions of Excel.
-----
If that does not solve your problem, you should be able to attach an example Excel file by clicking "browse" at the bottom of the Reply window.
Alternatively, upload an example Excel file that demonstrates the problem to a file-sharing website, and post a download URL that does not require that we log in.
I like box.net/files; others like dropbox.com. You might like onedrive.live.com because it uses the same login as this forum.
If you cannot post the actual URL, edit the beginning of it so that it does not look like a bona fide URL. For example, the URLfor this thread is
techcommunity dot microsoft dot com /t5/excel/geomean-producing-unexplained-num/m-p/3739059#M180397 .
PS.... But if you ``can't create a link due to organisation restrictions``, I would imagine you cannot click "browse", either. The only other option might be to email the example Excel file. But your org might restrict even those attachments. (sigh)
Feb 12 2023 04:52 PM
Feb 12 2023 05:51 PM - edited Feb 12 2023 05:52 PM
@P_Leandri wrote: ``my data does have the occasional blank cell, so I'll just have to clean it up first``
Why? Doesn't this work for you?
=10^AVERAGE(IF(ISNUMBER(A2:A3000),LOG(A2:A3000)))
Feb 12 2023 06:41 PM
Feb 12 2023 11:24 PM
@P_Leandri wrote: ``as long as the blanks are in the middle of the array it's all happy``
It should not matter where the empty and non-numeric cells are.
I suspect that you did not array-enter the formula (press ctrl+shift+Enter instead of just Enter), and you need to do that in your version of Excel.
I have attached an example Excel file. But you might not be able to download it due to "organization restrictions", just as you cannot upload files.
So I will demonstrate.
The formula in C2 appears as follows in the Formula Bar (in my version of Excel):
{=10^AVERAGE(IF(ISNUMBER(A2:A10),LOG(A2:A10)))}
Note the curly braces around the formula (in my version of Excel). We cannot type them ourselves. Instead, Excel displays them to indicate an array-entered formula (again, in my version).
Select C2, use the cursor to select just the red-highlighted text shown above, then press f9.
You should see:
=10^AVERAGE({FALSE;FALSE;2.0899051114394;2.65896484266443;FALSE;FALSE;2.89707700320942;FALSE;FALSE})
Be sure to press Esc when you are done in order to restore the original formula.
The FALSE values demonstrate that the formula correctly recognizes all empty and non-numeric cells in the beginning, middle and end of the range.
The AVERAGE function ignores FALSE values in ranges and arrays.
Feb 12 2023 11:50 PM
Thank you Joe,
You are correct, I didn't ctrl+shift+Enter. Seems to be working now.
Also, good tip on the use of f9, I'll keep that in my back pocket for later.
Feb 10 2023 01:00 AM - edited Feb 10 2023 11:32 PM
Solution
(Edited with a simpler demonstration)
It does not take much to demonstrate a situation where GEOMEAN of 2060+ data points returns #NUM.
Select A2:A2067, type 1.41, then press ctrl+Enter (not ctrl+shift+Enter) to enter 1.41 into all cells.
Note that =GEOMEAN(A2:A2066) returns 1.41. But =GEOMEAN(A2:A2067) returns #NUM.
The reason is =PRODUCT(A2:A2066) returns 1.372E+308 (rounded).
But =PRODUCT(A2:A2067) returns #NUM because 1.41*1.372E+308 would be 1.935E+308 (rounded), which exceeds the limit of 64-bit binary floating-point of less than 1.80E+308.
A work-around is:
=10^AVERAGE(LOG(A2:A2067))
If the range might have non-numeric data or empty cells:
=10^AVERAGE(IF(ISNUMBER(A2:A3000),LOG(A2:A3000)))
The latter formula must be array-entered in some versions of Excel.
-----
If that does not solve your problem, you should be able to attach an example Excel file by clicking "browse" at the bottom of the Reply window.
Alternatively, upload an example Excel file that demonstrates the problem to a file-sharing website, and post a download URL that does not require that we log in.
I like box.net/files; others like dropbox.com. You might like onedrive.live.com because it uses the same login as this forum.
If you cannot post the actual URL, edit the beginning of it so that it does not look like a bona fide URL. For example, the URLfor this thread is
techcommunity dot microsoft dot com /t5/excel/geomean-producing-unexplained-num/m-p/3739059#M180397 .
PS.... But if you ``can't create a link due to organisation restrictions``, I would imagine you cannot click "browse", either. The only other option might be to email the example Excel file. But your org might restrict even those attachments. (sigh)