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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
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 |