Excel - Geomean - Ignoring Empty Cells
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" wrote:
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
"Mike H" wrote:
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
"NC2A'DAM" wrote:
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" wrote:
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
"Mike H" wrote:
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
"NC2A'DAM" wrote:
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)
|