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!

 

[JustynaMK]

Hi,
Try the following Array formula (Ctrl+Shift+Enter):
=SUMPRODUCT(IF($A$2:$A$458=$E$9,$C$2:$C$458/$B$2:$B$458,0)/COUNTIF($A$2:$A$458,$E$9))

 

[JustynaMK]

Actually we can use the AVERAGE function as an array formula:
=AVERAGE(IF(A2:A10=F3,C2:C10/B2:B10))
Confirm with Control+Shift+Enter

 

[Aladin Akyurek]

Care to post an Excel readable small sample along with the expected result for that sample?

 

[Gijs]

Thanks JustynaMK! Good morning. I love the simplicity of the AVERAGE IF solution.

A bug with empty cells in Column B, I have fixed with IFERROR:
=IFERROR(AVERAGE(IF(A2:A10=F2,C2:C10/B2:B10)),"")

Whilst the error message has been resolved, what I have not been able to fix is zeros or blanks in Column C, which distorts the result. Is there a way to squeeze that condition in?

 

[Gijs]

Hartelijk dank Aladin, for reaching out. I've copy/pasted the page; I can't upload a sample xls - if you can provide me with your email, I gladly send it to you.


[TABLE="width: 671"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"][/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD][/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: right"]Condition[/TD]
[TD="align: right"]frequency[/TD]
[TD="align: right"]sum[/TD]
[TD="align: right"]average[/TD]
[TD="align: right"][/TD]
[TD="align: right"]criteria[/TD]
[TD="align: right"]average[/TD]
[TD="align: right"][/TD]
[TD]average w/helper
column D
(needed result)[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD]John[/TD]
[TD="align: right"]17.5[/TD]
[TD][/TD]
[TD]23.33333333[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD]Peter[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The formula in Column G reads: =IFERROR(AVERAGE(IF($A$3:$A$11=$F3,$C$3:$C$11/$B$3:$B$11)),"")
The desired result is in Column I (with helper Column D): =IFERROR(AVERAGEIF($A$3:$A$6,F3,$D$3:$D$6),"")

Whilst the error message has been resolved, what I have not been able to fix is zeros or blanks in Column C, which distorts the result. Is there a way to squeeze that condition in?

 

[KolGuyXcel]

How is I3 displaying 23.33333?

I'm getting 17.5 with =AVERAGEIF($A$3:$A$11,F3,$D$3:$D$11) in I3, assuming F3 has the same value as E9! Also, some of the bottom rows in the data set is completely blank! So I must be missing some data to get 23.333!

 

[AhoyNC]

Maybe:

Excel Workbook
	A	B	C	D	E	F	G
1	Condition	frequency	sum	average		criteria	average
2							w/helper
3	John	1	20	20		John	23.3333333
4	John	2	40	20		Peter	40
5	John	3	90	30
6	Peter	5	200	40
7	John	100		0
Sheet

 

[Gijs]

Thanks for helping out KolGuyXcel

I get the same (17.5) result as you just described with =AVERAGEIF($A$3:$A$11,F3,$D$3:$D$11). The formula I however used in the post is =IFERROR(AVERAGEIF($A$3:$A$6,F3,$D$3:$D$6),"") i.e. a shorter range to illustrate the 23.33333 result I'm looking for when there are no zeros or blanks in Column C.

Is there a possibility to build on =IFERROR(AVERAGE(IF($A$3:$A$11=$F3,$C$3:$C$11/$B$3:$B$11)),""), i.e. delivering the required 23.33333 result, eliminate the zeros or blanks in Column C and WITHOUT support column D? Or do I need to look for an entirely different approach (or in desperation stay with support column D)?

 

[Gijs]

Maybe:

	A	B	C	D	E	F	G
						w/helper
John		John
John		Peter
John
Peter
John

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: #c0c0c0"]Condition[/TD]
[TD="bgcolor: #c0c0c0"]frequency[/TD]
[TD="bgcolor: #c0c0c0"]sum[/TD]
[TD="bgcolor: #c0c0c0"]average[/TD]
[TD="bgcolor: #c0c0c0"][/TD]
[TD="bgcolor: #c0c0c0"]criteria[/TD]
[TD="bgcolor: #c0c0c0"]average[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]23.3333333[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]20[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]40[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]90[/TD]
[TD="align: center"]30[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]40[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: center"]100[/TD]

[TD="align: center"]0[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula G3 =AVERAGEIFS($D$3:$D$7,$A$3:$A$7,$F3,$D$3:$D$7,">0") G4 =AVERAGEIFS($D$3:$D$7,$A$3:$A$7,$F4,$D$3:$D$7,">0") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

AhoyNC, that formula will definitely work, but it requires utilizing support column D, which I was hoping to avoid.

Whilst =IFERROR(AVERAGE(IF($A$3:$A$11=$F3,$C$3:$C$11/$B$3:$B$11)),"") does the trick without support column D, it includes zeros or blanks in Column C, which I need to avoid as it distorts the result. Do I need to look for an entirely different approach or in desperation stay with support column D?

Thanks for your help and efforts AhoyNC, greatly appreciated!