ExcelBanter - Excel - Geomean - Ignoring Empty Cells

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Excel - Geomean - Ignoring Empty Cells (https://www.excelbanter.com/excel-worksheet-functions/211695-excel-geomean-ignoring-empty-cells.html)

Excel - Geomean - Ignoring Empty Cells

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)

Excel - Geomean - Ignoring Empty Cells

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)

Excel - Geomean - Ignoring Empty Cells

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)

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)

Excel - Geomean - Ignoring Empty Cells

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" wrote:

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

Excel - Geomean - Ignoring Empty Cells

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" wrote:

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" wrote:

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

Excel - Geomean - Ignoring Empty Cells

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:AZ30,E3:AZ3))

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

Mike

"NC2A''''DAM" wrote:

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" wrote:

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" wrote:

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

Excel - Geomean - Ignoring Empty Cells

NC2A''''DAM wrote:
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:A40,A1:A4)))

- - -
HTH
Dana DeLouis

Excel - Geomean - Ignoring Empty Cells

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( ??)))

=Exp(Average(If(A1:A40,A1:A4)))

- - -
Dana DeLouis

Excel - Geomean - Ignoring Empty Cells

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

"Dana DeLouis" wrote:

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( ??)))

=Exp(Average(If(A1:A40,A1:A4)))

- - -
Dana DeLouis

Excel - Geomean - Ignoring Empty Cells

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" wrote:

NC2A''''DAM wrote:
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:A40,A1:A4)))

- - -
HTH
Dana DeLouis

Excel - Geomean - Ignoring Empty Cells

Dana DeLouis wrote:
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( ??)))

=Exp(Average(If(A1:A40,A1:A4)))

- - -
Dana DeLouis

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(rng0,LN(rng)))/COUNT(rng))

- - -
HTH
Dana DeLouis

Excel - Geomean - Ignoring Empty Cells

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

On Nov 26, 4:01*pm, NC2A''''DAM
wrote:
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" wrote:
NC2A''''DAM wrote:
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:A40,A1:A4)))

- - -
HTH
Dana DeLouis

Excel - Geomean - Ignoring Empty Cells

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.
--
David Biddulph

"NC2A''''DAM" wrote in message
...
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" wrote:

NC2A''''DAM wrote:
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:A40,A1:A4)))

- - -
HTH
Dana DeLouis