Forum Discussion
Show average of cells which display X text.
Hi Please can you help create a formula for the below:.. In column M, what is the formula i would need if i wanted to take an average of column L, based on what text is based in colu...
thanks for quick response, will have a play round and see what works best.
The formula in M2, copied down to M7, is:
=SUMPRODUCT((D$2:D$7=D2)*K$2:K$7,L$2:L$7)/
SUMIF(D$2:D$7,D2,K$2:K$7)
The foregoing formula returns the weighted average, taking into consideration the maximum number of hours that can be claimed by each employee. The AVERAGE, AVERAGEIF, and AVERAGEIFS functions in Excel only return the simple average, ignoring any weighting factor.
=SUMPRODUCT((D$2:D$7=D2)*K$2:K$7,L$2:L$7)/
SUMIF(D$2:D$7,D2,K$2:K$7)
The foregoing formula returns the weighted average, taking into consideration the maximum number of hours that can be claimed by each employee. The AVERAGE, AVERAGEIF, and AVERAGEIFS functions in Excel only return the simple average, ignoring any weighting factor.