Average of a range, based on (i) a condition and (ii) the average per row

[Gijs]

To calculate the average of a range, based on (i) a condition and (ii) the average per row, I have:

Column A, the condition (John, Peter etc)
Column B, the frequency
Column C, is the total
I've looked at combining IF with =SUMPRODUCT, but SUMPRODUCT seems to only work with multiplying (percentages) whilst I first need to determine C divided by B. In desperation I created a helper,
Column D, to calculate the average per row =C/B
​

To calculate the required average of a range based on a condition I have formula =AVERAGEIF($A$2:$A$458,E9,$D$2:$D$458)

<style> <!-- /* Font Definitions */ @font-face {font-family:"ＭＳ 明朝"; mso-font-charset:78; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:1 134676480 16 0 131072 0;} @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:-536870145 1107305727 0 0 415 0;} @font-face {font-family:Cambria; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:-536870145 1073743103 0 0 415 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:Cambria; mso-ascii-font-family:Cambria; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"ＭＳ 明朝"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Cambria; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-ansi-language:EN-US;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-family:Cambria; mso-ascii-font-family:Cambria; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"ＭＳ 明朝"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Cambria; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-ansi-language:EN-US;} @Page WordSection1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.WordSection1 {page:WordSection1;} --> </style>The formula works, but given the dimensions of the data it makes the xls complex to work, hence is there a formula to calculate directly the above conditional average – WITHOUT support column D?

Thanks in advance for the help!

 

[Aladin Akyurek]

In G3 control+shift+enter, not just enter, and copy down:

=AVERAGE(IF($A$3:$A$7=F3,IF(ISNUMBER(1/($C$3:$C$7/$B$3:$B$7)),$C$3:$C$7/$B$3:$B$7)))

 

[Gijs]

Works like a charm! Thanks.

 

[Aladin Akyurek]

Works like a charm! Thanks.

Glad to help.