Forum Discussion
Geomean producing unexplained #NUM!
(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)
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.
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.
(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)
- Thank you Joe,
That makes sense. I've tried both your solutions and they work well. Unfortunately, my data does have the occasional blank cell, so I'll just have to clean it up first - but at least I know the solution now :).
P.S. you are correct - no browse (sigh)
