High, Low and Average based on two separate criteria

[MrRosco]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Price[/TD]
[TD]Classification[/TD]
[TD]A High[/TD]
[TD]A Low[/TD]
[TD]A Average[/TD]
[TD]B High[/TD]
[TD]B Low[/TD]
[TD]B Average[/TD]
[/TR]
[TR]
[TD]Item A[/TD]
[TD]£4.50[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item B[/TD]
[TD]£6.50[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item C[/TD]
[TD]£5.75[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item A[/TD]
[TD]£3.60[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item B[/TD]
[TD]£10.05[/TD]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item C[/TD]
[TD]£6.80[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item A[/TD]
[TD]£4.75[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item B[/TD]
[TD]£7.20[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item C[/TD]
[TD]£5.45[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Hi Everyone,

I need help with a formula to populate my table which looks at both item and classification and adds the value into High Low and average.

Any help will be greatly appreciated.

Rosco

 

[Peter_SSs]

Could you post the same table again but include the expected results so that we might be able to better interpret what you are asking?

 

[MrRosco]

Could you post the same table again but include the expected results so that we might be able to better interpret what you are asking?

Sure not a problem. it might be worth noting the data will be on a separate sheet.

I Envisage it looking like this, a table which has all items as a list the referencing the entire price file give the highest, lowest and average price per SKU

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Customer Classification[/TD]
[TD]A High[/TD]
[TD]A Low[/TD]
[TD]A Average.[/TD]
[/TR]
[TR]
[TD]Item A[/TD]
[TD]A[/TD]
[TD]7.85[/TD]
[TD]7.20[/TD]
[TD]7.56[/TD]
[/TR]
[TR]
[TD]Item B[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item C[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item D[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item E[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item F[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I hope this make a little more sense?

Rosco

 

[Peter_SSs]

I've done this on a single sheet but you should be able to adapt to another.
My actual results don't make a lot of sense as I don't have sufficient sample data but hopefully it will put you on the right track.
If you have the latest version of excel you could use the formulas in columns G:I
If Excel 2010 or later columns M:O
If older, post back with your version.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Item	Price	Classification		Item	Customer Classification	A High	A Low	A Average.		Item	Customer Classification	A High	A Low	A Average.
2	Item A	4.5	A		Item A	A	4.5	4.5	4.5		Item A	A	4.5	4.5	4.5
3	Item B	6.5	B		Item B	A	7.2	7.2	7.2		Item B	A	7.2	7.2	7.2
4	Item C	5.75	B		Item C	A	0	0	#DIV/0!		Item C	A	#NUM!	#NUM!	#DIV/0!
5	Item A	3.6	C		Item D	A	0	0	#DIV/0!		Item D	A	#NUM!	#NUM!	#DIV/0!
6	Item B	10.05	E		Item E	B	0	0	#DIV/0!		Item E	B	#NUM!	#NUM!	#DIV/0!
7	Item C	6.8	C		Item F	B	0	0	#DIV/0!		Item F	B	#NUM!	#NUM!	#DIV/0!
8	Item A	4.75	B
9	Item B	7.2	A
10	Item C	5.45	B
HLA

 

[MrRosco]

I've done this on a single sheet but you should be able to adapt to another.
My actual results don't make a lot of sense as I don't have sufficient sample data but hopefully it will put you on the right track.
If you have the latest version of excel you could use the formulas in columns G:I
If Excel 2010 or later columns M:O
If older, post back with your version.

HLA

	G	H	I	J	K	L	M	N	O
	Item A	A

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:58px;"><col style="width:55px;"><col style="width:82px;"><col style="width:14px;"><col style="width:57px;"><col style="width:159px;"><col style="width:58px;"><col style="width:58px;"><col style="width:82px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"]4.5[/TD]

[TD="align: right"]4.5[/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"]4.5[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula G2 =MAXIFS(B$2:B$10,A$2:A$10,E2,C$2:C$10,F2) H2 =MINIFS(B$2:B$10,A$2:A$10,E2,C$2:C$10,F2) I2 =AVERAGEIFS(B$2:B$10,A$2:A$10,E2,C$2:C$10,F2) M2 =AGGREGATE(14,6,B$2:B$10/((A$2:A$10=K2)*(C$2:C$10=L2)),1) N2 =AGGREGATE(15,6,B$2:B$10/((A$2:A$10=K2)*(C$2:C$10=L2)),1) O2 =SUMIFS(B$2:B$10,A$2:A$10,K2,C$2:C$10,L2)/COUNTIFS(A$2:A$10,K2,C$2:C$10,L2) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thanks for this ill give it a shot and report back (o_o)