Combine 'AverageIfs' and 'or'?

[chinglnc]

Hi

Is there anyway to combine 'averageifs' and 'or'

For example:

Year	Product	Quality	Country	Score
2013	A	Good	UK	10
2013	A	Good	USA	10
2014	B	Bad	UK	20
2014	C	Good	UK	20

<tbody>
</tbody>

Dropdown
Year: 2013
Product: A
Quality:Good
Country: UK

Average score: xxxx

So if someone choose the dropdown of Year and Product only, it will only calculate the average of these two criteria only; if someone chosen 3 criteria, the averages core will narrow down 3 criteria so on and so forth.

How do I do that?

It's a little bit like

averageifs(2013, yeardata, A, productdata) or averageifs(2013, yeardata, A, productdata, UK, country), or.....

Please help.

 

[pgc01]

The only records I see with

Year: 2013
Product: A
Quality: Good
Country: Any (-Select-)

are the records in
- row 2, with score 10
- row 5, with no score

Since you want to discard the records with no score the average is 10.

Please comment.

 

[chinglnc]

My apology!!! Let me try again!

 

[chinglnc]

Hi pgc01

I am trying to work out how different formulas work- I can't figure why this formula requires 'AverageIfNumber' to force it to average the number only while.....

I'd only consider the records where the score is a number:

=IFERROR(AVERAGE(IF(ISNUMBER(E2:E7),IF(MMULT((A2:D7<>H3:K3)*(H3:K3<>"-Select-"),0+(TRANSPOSE(COLUMN(H3:K3)>0)))=0,E2:E7))),0)

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Year	Product	Quality	Country	Score			Dropdown
2	2013	A	Good	UK	10			Year:	Product:	Quality:	Country:	Average:
3	2013	A	Bad	USA	20			2013	A	Good	-Select-	10
4	2013	C	Good	UK	30
5	2013	A	Good	USA
6	2014	D	Good	UK	20
7	2014	E	Bad	UK	10
8
[Book1]Sheet3

<tbody>
</tbody>

...this one below just work with the normal averageif formula? Can you share the insight please. It's really fascinating.

Try this array formula in I7:


=AVERAGE(IF((A2:A7=I2)+(I2="-Select-"),IF((B2:B7=I3)+(I3="-Select-"),IF((C2:C7=I4)+(I4="-Select-"),IF((D2:D7=I5)+(I5="-Select-"),E2:E7)))))


This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER and not just ENTER.




A B C D E F G H I J
1 Year Product Quality Country Score Dropdown
2 2013 A Good UK 10 Year: 2013
3 2013 A Bad USA 20 Product: A
4 2013 C Good UK 30 Quality: Good
5 2013 A Good USA 40 Country: -Select-
6 2014 D Good UK 20
7 2014 E Bad UK 10 Average: 25
8
[Book1]Sheet4

 

[chinglnc]

Actually, may be it does not work. My mistake. How do I make combine the AverageIfIsNumberplease

[TABLE]
<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 72px;"><col style="width: 72px;"><col style="width: 72px;"><col style="width: 72px;"><col style="width: 72px;"><col style="width: 72px;"><col style="width: 72px;"><col style="width: 72px;"><col style="width: 72px;"><col style="width: 72px;"></colgroup><tbody>[TR="bgcolor: #CACACA"]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD]Year[/TD]
[TD]Product[/TD]
[TD]Quality[/TD]
[TD]Country[/TD]
[TD]Score[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Dropdown[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: right"]2013[/TD]
[TD]A[/TD]
[TD]Good[/TD]
[TD]UK[/TD]
[TD="align: right"]10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Year:[/TD]
[TD="align: center"]2013[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: right"]2013[/TD]
[TD]A[/TD]
[TD]Bad[/TD]
[TD]USA[/TD]
[TD="align: right"]20[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Product:[/TD]
[TD="align: center"]Good[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Quality:[/TD]
[TD="align: center"]-Select-[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: right"]2013[/TD]
[TD]B[/TD]
[TD]Good[/TD]
[TD]USA[/TD]
[TD="align: right"]40[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Country:[/TD]
[TD="align: center"]-Select-[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: right"]2014[/TD]
[TD]D[/TD]
[TD]Good[/TD]
[TD]UK[/TD]
[TD="align: right"]20[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD="align: right"]2014[/TD]
[TD]E[/TD]
[TD]Bad[/TD]
[TD]UK[/TD]
[TD="align: right"]10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Average:[/TD]
[TD="bgcolor: #FFFF00"]#DIV/0![/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]10[/TD]
[TD="align: right"]2013[/TD]
[TD]A[/TD]
[TD]Good[/TD]
[TD]UK[/TD]
[TD="align: right"]10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

[TABLE]
<tbody>[TR]
[TD][B]Formeln der Tabelle[/B][/TD]
[/TR]
[TR]
[TD][TABLE]
<tbody>[TR="bgcolor: #CACACA"]
[TD]Zelle[/TD]
[TD]Formel[/TD]
[/TR]
[TR]
[TD]I7[/TD]
[TD]{=AVERAGE([COLOR=#008000][COLOR=#0000FF]IF((A2:A10=I2)[/COLOR][COLOR=#0000FF]+(I2="-Select-")[/COLOR],[COLOR=#0000FF][COLOR=#FF0000]IF((B2:B10=I3)[/COLOR][COLOR=#FF0000]+(I3="-Select-")[/COLOR],[COLOR=#FF0000][COLOR=#804000]IF((C2:C10=I4)[/COLOR][COLOR=#804000]+(I4="-Select-")[/COLOR],[COLOR=#804000][COLOR=#FF7837]IF((D2:D10=I5)[/COLOR][COLOR=#FF7837]+(I5="-Select-")[/COLOR],E2:E10)[/COLOR])[/COLOR])[/COLOR])[/COLOR])}[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][FONT=Arial][B]Enthält Matrixformel:[/B][/FONT][FONT=Arial]
Umrandende [/FONT][FONT=Arial][B]{ }[/B][/FONT][FONT=Arial] nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen![/FONT][/TD]
[/TR]
[TR]
[TD][URL="http://www.online-excel.de/excel/singsel.php?f=26"]Matrix verstehen[/URL][/TD]
[/TR]
</tbody>[/TABLE]

[URL="http://www.excel-jeanie-html.de/"] [/URL]

 

[pgc01]

In your formula you used "Good" for Product. That is invalid. "Good" is a value for "Quality" not for "Product".