Hi
I am trying in vain to add a further criteria to a formula that will enable the view of the results by group.
I was helped earlier this week on this site to get the formula that would give the product name with the largest week on week gain without the help of the variance column. This works perfectly.
=INDEX($C$2:$C$11,AGGREGATE(15,6,(ROW($C$2:$C$11)-ROW($C$2)+1)/(($E$2:$E$11-$D$2:$D$11)=LARGE($E$2:$E$11-$D$2:$D$11,ROWS(H$2:H2)))/(ISNA(MATCH(C$2:C11,H$1:H1,0))),1))
I thought i would be able to work out how to add in a further match or group or if criteria to it but have finally given up and I am now seeking further help.
On the attached, I have the original formula that shows that product8 having the highest week on week gain, what I need to know now is with the addition of the type and material columns what the highest gain would be (for example) for a nut and metal product (result would be product1). (yet again without the variance column)
I hope the request is clear and would appreciate any help.
Many thanks
Barney
I am trying in vain to add a further criteria to a formula that will enable the view of the results by group.
I was helped earlier this week on this site to get the formula that would give the product name with the largest week on week gain without the help of the variance column. This works perfectly.
=INDEX($C$2:$C$11,AGGREGATE(15,6,(ROW($C$2:$C$11)-ROW($C$2)+1)/(($E$2:$E$11-$D$2:$D$11)=LARGE($E$2:$E$11-$D$2:$D$11,ROWS(H$2:H2)))/(ISNA(MATCH(C$2:C11,H$1:H1,0))),1))
I thought i would be able to work out how to add in a further match or group or if criteria to it but have finally given up and I am now seeking further help.
On the attached, I have the original formula that shows that product8 having the highest week on week gain, what I need to know now is with the addition of the type and material columns what the highest gain would be (for example) for a nut and metal product (result would be product1). (yet again without the variance column)
I hope the request is clear and would appreciate any help.
Many thanks
Barney
| Book1.xlsx | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | type | material | product | units wk1 | units wk2 | var | ||||
| 2 | nut | metal | product1 | 2 | 5 | 3 | ||||
| 3 | bolt | plastic | product2 | 2 | 5 | 3 | ||||
| 4 | washer | metal | product3 | 4 | 5 | 1 | top gain | |||
| 5 | nut | plastic | product4 | 5 | 5 | 0 | product8 | |||
| 6 | bolt | metal | product5 | 8 | 5 | -3 | ||||
| 7 | washer | plastic | product6 | 9 | 5 | -4 | nut in metal top gain? | |||
| 8 | nut | metal | product7 | 10 | 5 | -5 | nut (ref this cell) | |||
| 9 | bolt | plastic | product8 | 0 | 5 | 5 | metal (ref this cell) | |||
| 10 | washer | metal | product9 | 1 | 5 | 4 | product? | |||
| 11 | nut | plastic | product10 | 6 | 5 | -1 | ||||
Sheet1 (2) | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H5 | H5 | =INDEX($C$2:$C$11,AGGREGATE(15,6,(ROW($C$2:$C$11)-ROW($C$2)+1)/(($E$2:$E$11-$D$2:$D$11)=LARGE($E$2:$E$11-$D$2:$D$11,ROWS(H$2:H2)))/(ISNA(MATCH(C$2:C11,H$1:H1,0))),1)) |
| F2:F11 | F2 | =E2-D2 |
