CODEWITHSUNDEEP
excelvbaexcel-formula
user3428508
user3428508

Reputation: 73

To retrieve the maximum data from 2 columns based on a logic

I have a table where in I need to get the Maximum count. Here from below table there Packet 1 should be picked up. The logic is as below - It should pick up the maximum from the sum of the items in Column B and C. - If this sum matches with another raw then it should pick the content from maximum of Column C

So in the below example it should pick packet 1.

enter image description here

Also I need to retrieve these 2 column values based on this selection so that I can use them for another calculation as below

Calculation = Amount Apple (Max picked up from Column C) + Mango Amount ((Max picked up from Column C)

Say for eg: Cell R1=Amount Apple, R2=Amount Mango

Original effort (not working yet):

=MAX(SUM(B2:C2),SUM(B3:C3),SUM(B4:C4),SUM(B5:C5))

Upvotes: 1

Views: 76

Answers (2)

XOR LX
XOR LX

Reputation: 7742

=LOOKUP(1,0/FREQUENCY(0,1/(1+MMULT(B2:C5,{1;1})+C2:C5/10^6)),A2:A5)

Note that the part {1;1} represents a 2-row-by-1-column (i.e. vertical) array, and also that the separator therein (semicolon) may require amending depending upon the locale of the version of Excel being used.

Regards

Upvotes: 1

user4039065
user4039065

Reputation:

You're going to require a formula that involves nested lookups. First, gather a set of rows (packets) that contain the maximum sum of columns B and C. From that set, determine which contains the maximum value in column C.

=INDEX(A:A, AGGREGATE(15, 7, ROW(2:5)/((B2:B5+C2:C5=AGGREGATE(14, 7, (B2:B5+C2:C5), 1))*(C2:C5=AGGREGATE(14, 7, C2:C5/(B2:B5+C2:C5=AGGREGATE(14, 7, (B2:B5+C2:C5), 1)), 1))), 1))

If two or more contain similar maximum values in column C, this returns the first one.

enter image description here

Upvotes: 0

Related Questions