Excel Formula 需要幫手----平均成績

2007-06-24 4:25 am

如何計算平均成績+該科目ge比率(e.g. 主科:附科=2:1)?應該使用什麼 formula le?
一千ge感謝....想要計算考試成績姐 =.=''

更新1:

Want to calculate my average marks applied with the ratio of different subjects ,e.g.:I have got 98 in Eng,95 in Math,80.5 in Chi,89 in Phy,82/90 in Chem,77/90 in Bio.Eng,Math and Chin are main subjects;compared to regular subjects the ratio is 2:1.What formulae can calculate the average mark?

更新2:

Data arranged like so; Marks Full Marks Ratio 90 100 3 83 100 2 79 90 1 98 .. .. ..

回答 (3)

joe

2007-06-27 11:51 pm

✔ 最佳答案

1) 首先, 要加多 column D, 作為每科的 full mark 為 100分時, 你在該科的得分, D1 的FORMULA 是 =(A1*100)/B1. 再將公式複製到下面的 CELLS.
2) 再下來, 就是計算加權平均數. 假定各科的分數到 D20, 在D21 輸入下列公式:
=sumproduct(b1:b20*c1:c20)/sum(c1:c20)
Now, D21 holds the "average" that you want.

👍 0 👎 0

Andy Chan

2007-06-24 6:35 pm

Marks Full Marks Ratio
90 100 3
83 100 2
79 90 1

設一輔助欄D
D1=A1/B1*100*C1 (公式往下拉)

平均成績=SUM(D1:D10)/SUM(C1:C10)

👍 0 👎 0

[匿名]

2007-06-24 8:13 am

how is your data arranged ??

let me say::
=average(A2:C2)

... ..."
or you want sumproduct ?? not quite clear about your problem..

參考: give me 10 bugs..

👍 0 👎 0

收錄日期: 2021-04-27 13:12:27
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20070623000051KK03956

檢視 Wayback Machine 備份