Excel - Geomean - Ignoring Empty Cells

[NC2A'DAM]

Hello All:

I am finishing up calculations on my masters thesis. I have about 2000
observations for about 150 bid-ask spreads for REITs (Real Estate Investment
Trusts) (way too much data for manual input). There are random empty cells
throughtout. I am trying to take a GEOMEAN for all 150 column variables. It
automatically ignores the empties when using average, but not in GEOMEAN.
What kind of formula can I use that will recognize the empty cells and ignore
them (not turn them into zeros) without registering as an error?

Thanks in advance.

(for Excel 2003)

 

[Mike H]

Maybe

=GEOMEAN(IF(A1:F3<>"",A1:F3))

Change the range to suit

'This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.



Mike

 

[Mike H]

Hi,

What I should have done is tested whether you are correct in suggesting
geomean doesn't ignore empty cells. I have now and find that a standard
geomean formula

=GEOMEAN(A1:F3)

Returns the same answer is the array one I posted so geomean does ignore
empty cells.

Mike

 

[NC2A''''DAM]

Hi Mike H:

Thanks for helping me out with this questions. I tried using the array
formula you recommended. It gave me the same #NUM error. I did see the curly
brackets when using control+shift+enter. I copied a small sample of the data
below. If you scroll down, you will notice that some of the formulas were
calculated but most showed up with errors. Can you make any sense of this? Is
there any way I can send you the whole data set?

Date Average Geometric Mean ACAN ANF BEFB
1/3/2000 0.071921426 #NUM! 0.01538
1/4/2000 0.046757454 #NUM! 0.13421 0.03799
1/5/2000 0.033988263 #NUM! 0.07658 0.02137
1/6/2000 0.01642232 #NUM! 0.12855 0.02919
1/7/2000 0.019578266 #NUM! 0.03876
1/10/2000 0.030260012 #NUM! 0.03077
1/11/2000 0.021868877 #NUM! 0.07658 0.03727
1/12/2000 0.020441252 #NUM! - 0.01578
1/13/2000 0.028710715 #NUM! 0.01703 0.05438 0.01468
1/14/2000 0.031088367 #NUM! 0.05438 0.01395
1/17/2000 0.075714333 #NUM! 0.11507 0.01802
1/18/2000 0.039160134 #NUM! 0.30666 0.01568
1/19/2000 0.035647758 #NUM! 0.00027 0.03219 0.02273
1/20/2000 0.018561487 #NUM! 0.10237 0.01449
1/21/2000 0.024801193 #NUM! - 0.05438 0.02295
1/24/2000 0.026663461 #NUM! 0.00081 0.03261 0.02521
1/25/2000 0.021085771 #NUM! - 0.03104
1/26/2000 0.039730788 #NUM! 0.14514 0.03205 0.11019
1/27/2000 0.025747521 #NUM! 0.03750
1/28/2000 0.02298788 #NUM! 0.09145 0.03333
1/31/2000 0.023802039 #NUM! 0.03447 0.04025
2/1/2000 0.02194532 #NUM! 0.03535 0.02833
2/2/2000 0.034504546 #NUM! 0.00357 0.02500
2/3/2000 0.029362397 #NUM! 0.00357 0.01356
2/4/2000 0.036673889 #NUM! - 0.04068
2/7/2000 0.045591742 #NUM! 0.00500
2/8/2000 0.035841006 #NUM! 0.01785 0.02000
2/9/2000 0.025702611 #NUM! 0.07368 0.06593 0.02857
2/10/2000 0.030793852 #NUM! 0.01785 0.00943
2/11/2000 0.024760022 #NUM! 0.01785 0.04199
2/14/2000 0.03062517 #NUM! 0.01817 0.01425
2/15/2000 0.031762652 #NUM! 0.03635 0.03333
2/16/2000 0.025483912 #NUM! 0.00311 0.00927
2/17/2000 0.025393458 #NUM! 0.01686 0.00168
2/18/2000 0.024417408 #NUM! 0.03481 0.00503
2/21/2000 0.029851473 #NUM! 0.15864 0.10701 0.01523
2/22/2000 0.014310039 #NUM! - 0.00169
2/23/2000 0.0229727 #NUM! - 0.01549
2/24/2000 0.025153135 #NUM! 0.01653 0.05455
2/25/2000 0.032117953 #NUM! 0.04476 0.05814 0.03571
2/28/2000 0.025868243 #NUM! 0.02030 0.03777
2/29/2000 0.03506574 #NUM! 0.17612 0.06558 0.02046
3/1/2000 0.037898653 #NUM! 0.01392 0.02637
3/2/2000 0.020514482 #NUM! - 0.01543
3/3/2000 0.0302314 #NUM! 0.00286 0.00657
3/6/2000 0.021287341 #NUM! 0.01111 - 0.02612
3/7/2000 0.031282371 #NUM! - 0.05691 0.03516
3/8/2000 0.040006675 #NUM! - 0.02476
3/9/2000 0.027710688 #NUM! - 0.05691 0.01482
3/10/2000 0.029594022 #NUM! - 0.04530 0.01989
3/13/2000 0.035652433 #NUM! 0.05142 0.04530 0.01852
3/14/2000 0.029552755 #NUM! - 0.05263 0.00917
3/15/2000 0.021800703 #NUM! - 0.02752
3/16/2000 0.024996127 #NUM! 0.09378 0.02752
3/17/2000 0.019693803 #NUM! - 0.04651 0.00926
3/20/2000 0.032462281 #NUM! - 0.04651 0.03050
3/21/2000 0.023494868 #NUM! - 0.02804
3/22/2000 0.031435236 #NUM! 0.03551
3/23/2000 0.028348555 #NUM! - 0.04651 0.02336
3/24/2000 0.12476659 #NUM! - 0.02377
3/27/2000 0.017969023 #NUM! - 0.01818
3/28/2000 0.026479134 #NUM! 0.02421
3/29/2000 0.019829062 #NUM! 0.03774
3/30/2000 0.033302168 #NUM! 0.04651 0.06773
3/31/2000 0.025436132 #NUM! 0.04651 0.05556
4/3/2000 0.032030295 #NUM! - 0.03448
4/4/2000 0.019753802 #NUM! 0.00990 0.02500
4/5/2000 0.039082616 #NUM! 0.09890 0.03512
4/6/2000 0.025784897 #NUM! - 0.01786
4/7/2000 0.032095964 #NUM! 0.01330 0.02727
4/10/2000 0.037271528 #NUM! 0.04415 0.25000 0.01802
4/11/2000 0.019127323 #NUM! 0.07609 0.01789
4/12/2000 0.007784064 #NUM! 0.02062 0.02154
4/13/2000 0.027404188 #NUM! 0.15553 0.01975
4/14/2000 0.019971333 #NUM! - 0.08672 0.01426
4/17/2000 0.02025032 #NUM! - 0.01786
4/18/2000 0.029929408 0.014624046 0.01607
4/19/2000 0.021234107 #NUM! 0.00885
4/20/2000 0.019641329 0.013288676 0.01786
4/25/2000 0.044887222 #NUM! 0.77273 0.01786
4/26/2000 0.025726843 #NUM! 0.01786
4/27/2000 0.028511867 #NUM! 0.11112 0.07574 0.01429
4/28/2000 0.021337006 #NUM! 0.08672 0.01429
5/2/2000 0.023528487 #NUM! 0.00983
5/3/2000 0.025011568 #NUM! 0.01802
5/4/2000 0.022252386 #NUM! 0.02173 0.00885
5/5/2000 0.048305049 #NUM! 0.66668 0.01975
5/8/2000 0.020144561 #NUM! - 0.00708
5/9/2000 0.039479588 #NUM! 0.12439 0.00708
5/10/2000 0.022105876 #NUM! 0.07976 0.00531
5/11/2000 0.016924071 #NUM! 0.00885
5/12/2000 0.0283676 #NUM! 0.07976 0.00885
5/15/2000 0.026738239 #NUM! - 0.00888
5/16/2000 0.021547407 #NUM! 0.01961 0.00533
5/17/2000 0.022328397 #NUM! 0.04515 0.00355
5/18/2000 0.019684628 #NUM! 0.01667 0.00536
5/19/2000 0.021737715 #NUM! 0.01667 0.01077
5/22/2000 0.025656797 #NUM! 0.06000 0.00355
5/23/2000 0.030827314 #NUM! 0.02020 0.00893
5/24/2000 0.044513939 #NUM! 0.83326 0.01818 0.00799
5/25/2000 0.019807523 #NUM! 0.01010 0.00443
5/26/2000 0.021450051 #NUM! 0.02020 0.00354
5/29/2000 0.028946439 0.019713654 0.04210 0.00531
5/30/2000 0.025044312 #NUM! 0.06316 0.00354
5/31/2000 0.021945883 #NUM! 0.06105 0.00353
6/2/2000 0.028310913 0.015452282 0.02740 0.00176
6/5/2000 0.035458032 #NUM! 0.49998 0.03896 0.00978
6/6/2000 0.019302745 #NUM! 0.04902 0.01429
6/7/2000 0.033487173 0.01404215 0.44779 0.01607
6/8/2000 0.022857316 #NUM! 0.00885
6/9/2000 0.027616646 #NUM! 0.09677 0.00530
6/13/2000 0.02124521 #NUM! 0.03570 0.08421 0.01514
6/14/2000 0.032945707 #NUM! 0.49820 0.00708
6/15/2000 0.028424506 #NUM! 0.15055 0.00353
6/16/2000 0.031776266 #NUM! - 0.00530
6/19/2000 0.016582072 #NUM! - 0.00529
6/20/2000 0.023791611 #NUM! 0.00529
6/21/2000 0.025735847 #NUM! 0.02279 0.00708
6/22/2000 0.024655968 #NUM! - 0.01423
6/23/2000 0.024444683 #NUM! 0.03667 0.01156
6/26/2000 0.046260251 #NUM! 0.02671 0.01336
6/27/2000 0.045980987 #NUM! 0.01443 0.01607
6/28/2000 0.020129894 #NUM! 0.03499 0.00446
6/29/2000 0.029551799 #NUM! 0.01944 0.01156
6/30/2000 0.026365476 #NUM! 0.02941 0.01156
7/3/2000 0.075097954 #NUM! 0.01064
7/4/2000 0.016899256 #NUM! 0.00707
7/5/2000 0.033736969 #NUM! 0.05662 0.00799
7/6/2000 0.05530716 #NUM! 0.00866 0.00443
7/7/2000 0.057858025 #NUM! 0.02374 0.01069
7/10/2000 0.032499104 0.013317813 0.05056 0.00179
7/11/2000 0.026605874 #NUM! 0.06168 0.00265
7/12/2000 0.024128771 #NUM! 0.01339
7/13/2000 0.02113384 #NUM! - 0.00089
7/14/2000 0.02078192 0.012943604 0.01339
7/17/2000 0.033844756 #NUM! - 0.01339
7/18/2000 0.046678975 #NUM! 0.01607
7/19/2000 0.04562027 #NUM! 0.02941 0.01607
7/20/2000 0.038014337 #NUM! 0.07216 0.02941 0.01066
7/24/2000 0.028150905 #NUM! 0.00943 0.01243
7/25/2000 0.042857758 #NUM! 0.02170 0.00444
7/26/2000 0.038204622 #NUM! - 0.00444
7/27/2000 0.028320242 #NUM! 0.09091 0.01156
7/28/2000 0.019119863 #NUM! 0.01426
7/31/2000 0.028975644 #NUM! 0.09298 0.01517
8/1/2000 0.029079815 0.012964724 0.09207 0.00893
8/2/2000 0.033646408 #NUM! 0.05640 0.00708
8/3/2000 0.040916388 0.01874168 0.01423
8/4/2000 0.030257649 0.015695106 0.01243
8/7/2000 0.025842086 #NUM! 0.05640 0.01421
8/8/2000 0.033684237 0.014213908 0.07273 0.01243
8/9/2000 0.030905949 #NUM! 0.01066
8/10/2000 0.041055286 #NUM! - 0.02188 0.00882
8/11/2000 0.024198213 #NUM! 0.02188 0.00439
8/14/2000 0.024810521 #NUM! 0.05264 0.00793
8/16/2000 0.039809377 #NUM! 0.37000 0.06655 0.00529
8/17/2000 0.024632203 #NUM! - 0.01725 0.00795
8/18/2000 0.045350748 #NUM! 0.06449 0.07566 0.00351
8/21/2000 0.032092386 #NUM! 0.16669 0.00351
8/22/2000 0.01762053 #NUM! 0.04991 0.00439
8/23/2000 0.129352899 #NUM! 0.04991 0.00175
8/24/2000 0.04336717 #NUM! 0.04991 0.00263
8/25/2000 0.041549662 #NUM! - 0.00263
8/28/2000 0.05926174 #NUM! 0.00877
8/29/2000 0.041212871 #NUM! - 0.00263
8/30/2000 0.042529391 #NUM! 0.01439 0.00263
8/31/2000 0.0301256 #NUM! 0.04114 0.01226
9/1/2000 0.042494198 #NUM! 0.04642 0.01315
9/4/2000 0.024485361 #NUM! - 0.01013 0.00176
9/5/2000 0.0265048 #NUM! 0.00877
9/6/2000 0.033262467 #NUM! 0.01111 0.01665
9/7/2000 0.03172069 #NUM! 0.01316
9/8/2000 0.038897097 #NUM! 0.08750 0.05485 0.00261
9/11/2000 0.028824329 0.011262269 0.01013 0.01228
9/12/2000 0.02696912 #NUM! 0.00928 0.01043
9/13/2000 0.025503136 #NUM! 0.01013 0.01724
9/14/2000 0.026509198 #NUM! 0.03120
9/15/2000 0.029999038 #NUM! 0.05264 0.17327 0.02253
9/18/2000 0.032956669 #NUM! 0.10178 0.02586
9/19/2000 0.033626901 #NUM! - 0.02155
9/20/2000 0.030987632 0.014173253 0.04738
9/21/2000 0.035566703 #NUM! 0.03986
9/22/2000 0.036597993 #NUM! 0.11321 0.01610
9/25/2000 0.030834413 #NUM! 0.01045
9/26/2000 0.02610966 #NUM! 0.02609
9/27/2000 0.038587162 #NUM! - 0.01724
9/28/2000 0.032868434 #NUM! 0.13810 0.02414
9/29/2000 0.03182619 #NUM! 0.08182 0.01724
10/2/2000 0.030169216 #NUM! 0.02918
10/3/2000 0.038400819 #NUM! 0.16299 0.01637
10/4/2000 0.043512242 #NUM! - 0.13010 0.00769
10/5/2000 0.045429035 0.018410117 0.09633 0.05172 0.00769
10/6/2000 0.08384685 0.018147827 0.01376
10/9/2000 0.081496043 #NUM! - 0.01115
10/10/2000 0.067506152 #NUM! - 0.01115
10/11/2000 0.024234422 #NUM! 0.10281 0.00855
10/12/2000 0.061089407 0.019960601 0.21752 0.08224 0.01709
10/13/2000 0.064739702 0.019098791 0.05264 0.09159 0.02609
10/16/2000 0.039179376 #NUM! 0.00716 0.07871 0.02168
10/17/2000 0.033635574 0.015133705 0.09856 0.01828
10/18/2000 0.044410466 #NUM! 0.21284 0.02533
10/19/2000 0.068159053 #NUM! 0.01221
10/20/2000 0.065134949 #NUM! 0.08037 0.01401
10/23/2000 0.030569885 #NUM! 0.08037 0.00350
10/24/2000 0.139261 #NUM! 0.04144 0.02969
10/25/2000 0.105212699 #NUM! 0.04054 0.00524
10/26/2000 0.073378825 #NUM! 0.15154 0.04144 0.01525
10/27/2000 0.107689815 0.018641772 0.02738 0.05946 0.01525
10/30/2000 0.083385834 0.019981769 0.10000 0.02076
10/31/2000 0.085511817 #NUM! 0.09372 0.04817 0.02253
11/1/2000 0.031358182 0.016748735 0.17645 0.15719 0.02253
11/2/2000 0.054845015 #NUM! 0.00683
11/3/2000 0.02702765 #NUM! 0.15719 0.00855
11/6/2000 0.033451038 #NUM! 0.08818 0.00862
11/7/2000 0.029419674 #NUM! 0.02030 0.00750 0.00862
11/8/2000 0.051585805 #NUM! - 0.00515
11/9/2000 0.02803265 #NUM! 0.04964 0.02981 0.00171
11/10/2000 0.008127765 #NUM! 0.03302 0.00769
11/13/2000 0.033096977 #NUM! 0.01639 0.00503
11/14/2000 0.026597943 #NUM! 0.02295 0.00671
11/15/2000 0.058605065 #NUM! - 0.00079 0.01695
11/16/2000 0.028441829 #NUM! 0.04904 0.02381 0.00671
11/17/2000 0.03804193 #NUM! 0.00781 0.01610
11/20/2000 0.034744231 #NUM! 0.00078 0.00661
11/21/2000 0.027425563 #NUM! 0.00775 0.00827
11/22/2000 0.046962698 #NUM! 0.06667 0.00494
11/23/2000 0.51552134 #NUM! 0.04227 0.07743 0.02500
11/24/2000 0.40925164 #NUM! 0.02698 0.02500
11/27/2000 0.048532625 #NUM! 0.00128 0.02540 0.02909
11/28/2000 0.57520972 #NUM! 0.02570
11/29/2000 0.022681854 #NUM! 0.07619 0.01392
11/30/2000 0.031745435 #NUM! 0.00253 0.07692 0.00655
12/1/2000 0.020294433 #NUM! 0.00024 0.00820
12/4/2000 0.059675157 #NUM! - 0.04615 0.00750
12/5/2000 0.020417411 #NUM! 0.13524 0.00994
12/6/2000 0.028664254 #NUM! 0.05280 0.13524 0.01241
12/7/2000 0.0295231 #NUM! 0.04045 0.06154 0.00813
12/8/2000 0.039883278 #NUM! - 0.10656 0.01639
12/11/2000 0.022445262 #NUM! 0.10492 0.00655
12/12/2000 0.018400271 #NUM! 0.00655
12/13/2000 0.043887014 #NUM! 0.10246 0.00326
12/14/2000 0.054691565 #NUM! 0.08448 0.00645
12/15/2000 0.029970397 #NUM! 0.03748 0.08049 0.00644
12/18/2000 0.035190442 #NUM! 0.01219 0.01562 0.01932
12/19/2000 0.02051913 #NUM! 0.06371 0.00960
12/20/2000 0.027773414 #NUM! - 0.02114
12/21/2000 0.025125016 #NUM! 0.00720 0.01401
12/22/2000 0.077155042 #NUM! 0.00800 0.06695
12/27/2000 0.038663115 #NUM! 0.01520 0.02397
12/28/2000 0.022753477 #NUM! 0.00800 0.03333
1/2/2001 0.042727221 #NUM! 0.46876 0.00171
1/3/2001 0.049494951 0.017259024 0.46876 0.05520 0.03448
1/4/2001 0.025448215 #NUM! 0.03200 0.04220
1/5/2001 0.051763716 #NUM! 0.43437 0.03978
1/8/2001 0.025144765 #NUM! 0.02320 0.02587
1/9/2001 0.023948197 #NUM! 0.04763 0.02478
1/10/2001 0.027797089 #NUM! 0.37501 0.02318 0.02478
1/11/2001 0.029069062 #NUM! 0.00719 0.01652
1/12/2001 0.039222045 #NUM! 0.52189 0.00877
1/15/2001 0.042621127 #NUM! 0.52189 0.03038 0.01604
1/16/2001 0.035568917 #NUM! 0.52189 0.02955 0.01604
1/17/2001 0.044501246 #NUM! 0.52189 0.02302 0.02812
1/18/2001 0.033136951 #NUM! 0.50000 0.02566
1/19/2001 0.037589789 #NUM! 0.49377 0.03038 0.02632
1/22/2001 0.031732603 #NUM! 0.41905 0.00877
1/23/2001 0.046377231 #NUM! 0.46876 0.02009
1/24/2001 0.035337959 #NUM! 0.48127 0.00719 0.01829
1/25/2001 0.058444972 #NUM! 0.48127 0.01217
1/26/2001 0.046177962 #NUM! 0.48624 0.02381 0.02005
1/29/2001 0.030081793 #NUM! 0.27062 0.02696 0.02308
1/30/2001 0.017372956 #NUM! 0.19891 0.00833
1/31/2001 0.019504109 #NUM! 0.18887 0.04720 0.01345
2/1/2001 0.026315418 #NUM! 0.03014 0.02118
2/2/2001 0.036969712 #NUM! 0.02389
2/5/2001 0.023645688 #NUM! 0.05392 0.00084
2/6/2001 0.024110543 #NUM! 0.00678
2/7/2001 0.018692415 #NUM! - 0.00683
2/8/2001 0.010340122 #NUM! 0.04758 0.00769
2/9/2001 0.024176777 #NUM! 0.03837 0.01552
2/12/2001 0.030914831 0.016914481 0.03837 0.01637
2/13/2001 0.024833097 0.015087265 0.03837 0.01463
2/14/2001 0.016475739 #NUM! 0.00541 0.01550
2/15/2001 0.029345549 #NUM! 0.08974 0.04077 0.00172
2/16/2001 0.019249526 #NUM! 0.03917 0.00335
2/19/2001 0.020876745 #NUM! 0.04636 0.01017
2/20/2001 0.019536149 #NUM! 0.02266 0.01017
2/21/2001 0.019614783 #NUM! 0.05356 0.00678
2/22/2001 0.027853243 0.013892434 0.04520 0.00169
2/23/2001 0.031814846 #NUM! 0.01179 0.02969 0.00846
2/26/2001 0.023426995 #NUM! 0.00847
2/27/2001 0.051119011 #NUM! 0.05356 0.00846
2/28/2001 0.018334477 #NUM! 0.01269
3/1/2001 0.01980421 0.013927562 0.05356 0.01017
3/2/2001 0.017058579 #NUM! - 0.00847
3/5/2001 0.025926746 #NUM! 0.05557 0.00083
3/6/2001 0.018473638 #NUM! 0.05356 0.01695
3/7/2001 0.022506362 #NUM! 0.02738 0.05356 0.02389
3/8/2001 0.029169713 #NUM! 0.05356 0.01523
3/9/2001 0.036906059 0.019654025 0.07114 0.01523
3/12/2001 0.04247122 #NUM! 0.02389
3/13/2001 0.024784061 #NUM! 0.01029
3/14/2001 0.024034521 #NUM! 0.00855
3/15/2001 0.011030916 #NUM! 0.01288
3/16/2001 0.026925022 #NUM! 0.00597
3/19/2001 0.029113556 #NUM! 0.07114 0.02431
3/20/2001 0.023394968 #NUM! 0.00769
3/21/2001 0.041183307 #NUM! 0.07114 0.01356
3/22/2001 0.058314186 #NUM! 0.10294 0.01724
3/23/2001 0.03091889 #NUM! 0.16311 0.07029 0.02137
3/26/2001 0.084590653 #NUM! - 0.01449
3/27/2001 0.034198031 #NUM! 0.08108 0.01028
3/28/2001 0.037723186 0.021796391 0.08108 0.00426
3/29/2001 0.021724956 #NUM! 0.00763 0.00339
3/30/2001 0.026261189 #NUM! 0.01701
4/2/2001 0.035774017 0.014760466 0.02041
4/3/2001 0.054893781 #NUM! 0.01525
4/4/2001 -0.006635335 #NUM! 0.01356
4/5/2001 0.130541566 #NUM! 0.03557 0.01549
4/6/2001 0.018162717 0.010865715 0.01116
4/9/2001 0.022202669 #NUM! 0.01464
4/10/2001 0.067731927 #NUM! 0.00855
4/11/2001 0.020226037 #NUM! 0.03068 0.01549
4/12/2001 0.018383985 #NUM! 0.00687 0.02489
4/17/2001 0.079548493 #NUM! - 0.01537
4/18/2001 0.024325237 #NUM! 0.07765 0.01624
4/19/2001 0.017875691 #NUM! 0.01191
4/20/2001 0.056832189 0.015383863 0.01536
4/23/2001 0.065525736 #NUM! 0.00678
4/24/2001 0.082377307 #NUM! 0.01695
4/25/2001 0.027561199 #NUM! 0.00847
4/26/2001 0.033729621 #NUM! 0.01607
4/27/2001 0.055594346 #NUM! 0.02694
4/30/2001 0.066004961 #NUM! 0.11112 0.02441
5/2/2001 0.018728375 #NUM! - 0.02282
5/3/2001 0.014451321 #NUM! 0.01683
5/4/2001 0.036526763 #NUM! 0.00085
5/7/2001 0.025508008 #NUM! 0.00667
5/8/2001 0.028744139 #NUM! - 0.00749
5/10/2001 0.026252284 #NUM! 0.15943 0.00833
5/11/2001 0.028353034 #NUM! 0.09728 0.02500
5/14/2001 0.02756431 #NUM! 0.11891 0.01167
5/15/2001 0.031390699 #NUM! 0.00833
5/16/2001 0.026926855 #NUM! 0.08108 - 0.00671
5/17/2001 0.028844509 #NUM! 0.07534 0.09199 0.00671
5/18/2001 0.037669025 #NUM! 0.12181 0.00949 0.03269
5/21/2001 0.028278964 #NUM! 0.01010
5/22/2001 0.040839709 #NUM! 0.06062 0.01180
5/23/2001 0.020418712 #NUM! 0.01010
5/24/2001 0.34969601 #NUM! 9.14835 0.10760 0.00927
5/25/2001 0.367135409 #NUM! 9.30159 0.00840
5/28/2001 0.283243899 0.020924512 5.18147 0.01557
5/29/2001 0.07105409 #NUM! 0.47830 0.09163 0.00082
5/30/2001 0.155030167 #NUM! 4.66659 0.10395 0.03946
5/31/2001 0.168247093 #NUM! 5.18147 0.08367 0.00335
6/1/2001 0.019499002 #NUM! - 0.00833
6/5/2001 0.075393213 #NUM! 0.02905

 

[Mike H]

Hi,

Geomean will produce a num error if any number is <=0 so check your data.
You can post your workbook here and post the link

http://www.savefile.com/

Mike

 

[NC2A''''DAM]

Hey Mike:

I posted the file to savefile.com just so you can check it out. You are
right though, I must take out all the negative numbers and zeros before it is
willing to calculate the geometric mean. I will get right on it.

Thank you very much for your help!!

http://www.savefile.com/projects/808715143

http://www.savefile.com/files/1905047

PS- Savefile.com is going to be a very valuable resource to me as I am
finishing my thesis from across the Atlantic Ocean.

 

[Mike H]

Hi,

Deleting data to satisfy a formula is in my view a fundamentally flawed
approach. If you have zero and negative values then you have them. Why not
leave them there and modify the formula

=GEOMEAN(IF(E3:AZ3>0,E3:AZ3))

Will calculate the geometric mean and ignore zeroes and negatives in the
range. It's an array formula.

Mike

 

[Dana DeLouis]

Hey Mike:

I posted the file to savefile.com just so you can check it out. You are
right though, I must take out all the negative numbers and zeros before it is
willing to calculate the geometric mean. I will get right on it.

Thank you very much for your help!!

<snip>

As a side note, it doesn't take many numbers for GeoMean to return an
error because of overflow.
Perhaps use the alternate equation for GeoMean...
It's an array formula. Samll test area A1:A4

=Exp(Average(If(A1:A4>0,A1:A4)))

- - -
HTH
Dana DeLouis

 

[Dana DeLouis]

Oops. I Made a mistake. I left out the Ln() function, but it's still
not working. Let me think about this...

=Exp(Average(Ln( ??)))

 

[Mike H]

Hi,

A couple of ways
=GEOMEAN(A1:A10)

=EXP(SUMPRODUCT(LN(A1:A10)/COUNT(A1:A10)))

Array
=PRODUCT(A1:A10^(1/COUNT(A1:A10)))

Mike

 

[NC2A''''DAM]

Mike - I understand it is fundamentally flawed. I am simply planning on
replacing the 0s with 0.00000000001, so that the data will not be
meaningfully altered. I will be able to work around the negatives for the
time being. All of my steps will be disclosed. And I do appreciate you
looking after the integrity of academics. However, I will see if I can get
your recommended formula to give me error-free results.

Dana - Thank you. I will see if there is too much data to take a GEOMEAN,
but I believe we should be okay. At most a hundred cells are necessary for me
to find each required GEOMEAN. There will just be a bunch of these geometric
means.

 

[Dana DeLouis]

Oops. I Made a mistake. I left out the Ln() function, but it's still
not working. Let me think about this...

=Exp(Average(Ln( ??)))

Ok. Let me try that again.
The Average function doesn't work well in Array formulas.

Here, rng is a small test area like A1:A10.

=EXP(SUM(IF(rng>0,LN(rng)))/COUNT(rng))

- - -
HTH
Dana DeLouis

 

[vezerid]

Don't change your 0's to 0.00000001. You are multiplying. If you want
to ignore 0's change them to 1, the neutral element of multiplication.

HTH
Kostis Vezerides

 

[David Biddulph]

It seems to me that if you change 0s to 0.00000000001 the data for your
GEOMEAN function will be "meaningfully altered".
If you've got 99 1s, the GEOMEAN would of course be 1, but if you add one
sample of 0.00000000001 to the series the GEOMEAN becomes 0.776247.