| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel - Min function over 4 separate cells, but ignoring negatives | Excel Worksheet Functions | |||
| Ignoring empty cells | Excel Worksheet Functions | |||
| Ignoring empty cells | Excel Discussion (Misc queries) | |||
| Excel - Autom. Filter "Empty / Non Empty cells" should come first | Excel Discussion (Misc queries) | |||
| geomean ignoring blank cells and chars | Excel Worksheet Functions | |||
Hybrid Mode
