作者:乔山办公网日期:
返回目录:excel表格制作
在N2输入复:
=sum(if(iserror(lookup(d1:m1,{"A","A-","B+","B","B-","C+","C","C-","D+","D","D-"},{4,3.67,3.33,3,2.67,2.33,2,1.67,1.33,1,0.67})),0,lookup(d1:m1,{"A","A-","B+","B","B-","C+","C","C-","D+","D","D-"},{4,3.67,3.33,3,2.67,2.33,2,1.67,1.33,1,0.67}))) 数组制公百式以CTRL+SHIFT+ENTER结束度。下知拉填充道。
你需要的是单科GPA还是均分GPA?你的公式好像两种都没办法算。
单科:把“L2:L58”改为“L2”,公式下拉
均分:把“L2:L58”改为“AVERAGE(L2:L58)”,
将学zd分单拉出来放一行,然后用函数sumproduct(),具体用法直接百度。
平均成绩公式为:专
=sumproduct(成绩行,学分属行)/总学分
平均绩点公式为:
=sumproduct(绩点行,学分行)/总学分
绩点需要用if函数判断出来,这个小case不累赘了
给表格,我给你做