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