High, Low and Average based on two separate criteria

MrRosco

New Member
Joined
May 12, 2016
Messages
47
ItemPriceClassificationA HighA LowA AverageB HighB LowB Average
Item A£4.50A
Item B£6.50B
Item C£5.75B
Item A£3.60C
Item B£10.05E
Item C£6.80C
Item A£4.75B
Item B£7.20A
Item C£5.45B

<tbody>
</tbody>
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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?
 
Upvote 0
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

ItemCustomer ClassificationA HighA LowA Average.
Item AA7.857.207.56
Item BA
Item CA
Item DA
Item EB
Item FB

<tbody>
</tbody>


I hope this make a little more sense?

Rosco
 
Upvote 0
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
ABCDEFGHIJKLMNO
1ItemPriceClassificationItemCustomer ClassificationA HighA LowA Average.ItemCustomer ClassificationA HighA LowA Average.
2Item A4.5AItem AA4.54.54.5Item AA4.54.54.5
3Item B6.5BItem BA7.27.27.2Item BA7.27.27.2
4Item C5.75BItem CA00#DIV/0!Item CA#NUM!#NUM!#DIV/0!
5Item A3.6CItem DA00#DIV/0!Item DA#NUM!#NUM!#DIV/0!
6Item B10.05EItem EB00#DIV/0!Item EB#NUM!#NUM!#DIV/0!
7Item C6.8CItem FB00#DIV/0!Item FB#NUM!#NUM!#DIV/0!
8Item A4.75B
9Item B7.2A
10Item C5.45B
HLA
 
Last edited:
Upvote 0
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

GHIJKLMNO
24.54.54.5 Item AA4.54.54.5

<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>
</tbody>

Spreadsheet Formulas
CellFormula
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)
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top