AVERAGEIF Formula
Category: Multiple Formulas
Level: Intermediate
Excel has a formula to count sum with any condition, SUMIF. But there isn't built-in formula to count average with any condition, e.g. said AVERAGEIF. Then u yourself must write function with macro. But any other solution without macro?
The anwser is absolutly.... yes! *Horrayy*
U can use the advantage of array function, which is by pressing Ctrl+Shift+Enter after u write the formula. Then u can see that the formula shows in the bracket {..}.
The formula is
=AVERAGE(IF(array of condition = condition, array of average))
and followed by pressing Ctrl+Shift+Enter
For example. This table below shows the rate of subject by student. Our mission is find the average of each subject.
U just write the formula in D16, D17, and D18, respectively:
=AVERAGE(IF($C$3:$C$13=C16,$D$3:$D$13))
=AVERAGE(IF($C$3:$C$13=C17,$D$3:$D$13))
=AVERAGE(IF($C$3:$C$13=C18,$D$3:$D$13))
and don't forget to press Ctrl+Shift+Enter.
Level: Intermediate
Excel has a formula to count sum with any condition, SUMIF. But there isn't built-in formula to count average with any condition, e.g. said AVERAGEIF. Then u yourself must write function with macro. But any other solution without macro?
The anwser is absolutly.... yes! *Horrayy*
U can use the advantage of array function, which is by pressing Ctrl+Shift+Enter after u write the formula. Then u can see that the formula shows in the bracket {..}.
The formula is
=AVERAGE(IF(array of condition = condition, array of average))
and followed by pressing Ctrl+Shift+Enter
For example. This table below shows the rate of subject by student. Our mission is find the average of each subject.
U just write the formula in D16, D17, and D18, respectively:
=AVERAGE(IF($C$3:$C$13=C16,$D$3:$D$13))
=AVERAGE(IF($C$3:$C$13=C17,$D$3:$D$13))
=AVERAGE(IF($C$3:$C$13=C18,$D$3:$D$13))
and don't forget to press Ctrl+Shift+Enter.
posted by F-Xtudent at
11:56 AM
0 Comments:
Post a Comment
<< Home