Index Match from a SUM

barney_t

New Member
Joined
Feb 24, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Hi,

I am trying with no success to find the product with the largest week on week gain without using a variance column.
Therefore the formula is in one cell having looked at all the individual sums on each row.
In the attached I have left in the variance column so that you can easily see that the result i am after is product8. I would also need to repeat for the 2nd largest unit gain (so Large function somewhere in the formula would be good).

I assume the Index would be the product column but I cant work out what would go with the Match to make it work.
I thought this would be a fairly common task within excel but i cant seem to find a tutorial on it. E.G from a calculation of data give the best seller.
I have been working at this for days so any help would be much appreciated.
I am new to the forum so apologies if i have not been clear in my request.
Many Thanks
Barney

Book1
ABCD
1productunits wk1units wk2var
2product1253
3product2253
4product3451
5product4550
6product585-3
7product695-4
8product7105-5
9product8055
10product9154
11product1065-1
Sheet1
Cell Formulas
RangeFormula
D2:D11D2=C2-B2
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Why don't you want to keep the variance column? It will make the formula far more efficient than getting rid of it.
 

barney_t

New Member
Joined
Feb 24, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Hi Fluff
In essence, on the actual data set i am working with, there are a lot more rows and i only need the top few product names so my thought was to only have a few formulas rather than one for every row and be pulling from them all.

kind regards
barney
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
You can use
+Fluff 1.xlsm
ABCDEF
1productunits wk1units wk2var
2product1253product8
3product2253product9
4product3451product1
5product4550product2
6product585-3product3
7product695-4product4
8product7105-5product10
9product8055product5
10product9154product6
11product1065-1product7
Main
Cell Formulas
RangeFormula
D2:D11D2=C2-B2
F2:F11F2=INDEX($A$2:$A$11,AGGREGATE(15,6,(ROW($A$2:$A$11)-ROW($A$2)+1)/(($C$2:$C$11-$B$2:$B$11)=LARGE($C$2:$C$11-$B$2:$B$11,ROWS(F$2:F2)))/(ISNA(MATCH(A$2:A11,F$1:F1,0))),1))


But it may not be very fast if you have a lot of data.
 
Solution

barney_t

New Member
Joined
Feb 24, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Hi Fluff,
You're a genius. Thank you so much for your help it works perfectly.
Many Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,781
Messages
5,626,837
Members
416,204
Latest member
Perzo

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
Top