Excel 條件式的幾何平均

有以下表格

A B C D E FG

1 2/1 2/2 2/4 2/5 2/6 2/72/6

2 0.8 0.5 0 0.6 0.7 0.9

第1列代表日期(但不是連續的日期)

而現在G1格內顯示2/6

我希望能在G2格子內做出2/1~2/6所對應的數值的幾何平均

也就是:

((1+0.8)*(1+0.5)*(1+0)*(1+0.6)*(1+0.7))^(1/5)-1

但是我G1的格子會變動,會需要三個月的量,所以懶惰不想一個一個點來相乘開根號(因為要做的資料很龐大)

想請教各位大大有沒有辦法能夠做出判斷的條件出來做幾何平均

這是我原本做的公式

=(((1+A1)*(1+B1)*(1+C1)*(1+D1)*(1+E1)^(1/COUNTA(A1:E1))-1)

但就是因為每個都要點,會怕點錯~~

Update:

KK大師您好

謝謝您~~兩種方式幾乎是我所需要的公式了!!

但有一個問題,就是我第二列的資料,有可能是小於-1的

因為我使用您的公式,只要資料內的數值小於-1,數據就跑不出來

Update 2:

對不起KK大大,剛剛所說的小於-1的數後來發現是我眼殘

看錯了.....根本不能小於-1

我有點理解這串公式了!

但我還有個小小請求(抱歉...點數一定會給您的><)

因為您這串公式的起頭都是從A2開始

我要算是有區間的(比如說從2/1~2/6的算一個,另一個是從2/3~2/7的再算一個)

我目前有設定的如下

A B

3 (起始日)2/1 (結束日)2/6

4 ( 起始日)2/3 (結束日)2/7

而我在第1列的日期不一定每天都有(但確定的是遞增的)

第2列就是數據並且可能是五六年份的數據

Update 3:

感謝KK大師

已解決我所有的問題了,而且我也學到許多新的東西

對我來說~這些函數跟用法真的幫助我很多!!!

謝謝您幫我上了一課

非常非常感謝您~~

我要怎麼把您的回答作為最佳解答呢??

1 Answer

Rating
  • KK
    Lv 7
    9 years ago
    Favorite Answer

    G2公式如下

    =PRODUCT(OFFSET(A2,,,,MATCH(G1,1:1))+1)^(1/MATCH(G1,1:1))-1

    不能用ENTER輸入,要用CTRL + SHIFT + ENTER 三鍵齊按輸入)

    貴公式修正為

    =((1+A2)*(1+B2)*(1+C2)*(1+D2)*(1+E2))^(1/COUNTA(A2:E2))-1

    2012-05-16 22:59:05 補充:

    G2公式如下

    =GEOMEAN(OFFSET(A2,,,,MATCH(G1,1:1))+1)-1

    不能用ENTER輸入,要用CTRL + SHIFT + ENTER 三鍵齊按輸入)

    2012-05-18 17:18:59 補充:

    小於 -1 的數值不要嗎?

    或是

    只要有小於 -1 的數值就顯示 "有數值小於-1無法計算"

    2012-05-19 00:04:42 補充:

    A B

    3 (起始日)2/1 (結束日)2/6

    4 ( 起始日)2/3 (結束日)2/7

    D3

    =GEOMEAN(IF((A$1:F$1>=A3)*(A$1:F$1<=B3),A$2:F$2+1))-1

    不能用ENTER輸入,要用CTRL + SHIFT + ENTER 三鍵齊按輸入)

    2012-05-19 11:29:07 補充:

    1:1 是日期

    2:2 是數據

    A B

    3 (起始日)2/1 (結束日)2/6

    4 ( 起始日)2/3 (結束日)2/7

    D3

    =GEOMEAN(IF((A$1:F$1>=A3)*(A$1:F$1<=B3),A$2:F$2+1))-1

    不能用ENTER輸入,要用CTRL + SHIFT + ENTER 三鍵齊按輸入)

Still have questions? Get your answers by asking now.