Need help with formula

[zazybang]

I have 3 columns, column A, B, and C. Column A has names while column B and C have numbers. There are 10 rows in total. I wish to find the highest sum of a row to see which employee did the best for what i am doing. Once the highest sum is found i wish for the employees name, that is linked with that sum to appear in the cell that i type the formula in.

 

[Legacy 143009]

I have 3 columns, column A, B, and C. Column A has names while column B and C have numbers. There are 10 rows in total. I wish to find the highest sum of a row to see which employee did the best for what i am doing. Once the highest sum is found i wish for the employees name, that is linked with that sum to appear in the cell that i type the formula in.

It's an array formula so you have to Ctrl+Shift+Enter after paste.

{ =INDEX($A$1:$A$10,MATCH(MAX($B$1:$B$10+$C$1:$C$10),$B$1:$B$10+$C$1:$C$10,0)) }

Have a nice day

 

[zazybang]

Thanks my Dude!

 

[Legacy 143009]

Thanks my Dude!

But I don't know what will happen if there are two same maximum sums.

 

[Aladin Akyurek]

@ zazybang
@Flashbond

There can be more than one employee which achive the same maximum sum...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]kad[/td][td]

5​

[/td][td]

4​

[/td][td][/td][td]kad[/td][/tr]
[tr][td]

3​

[/td][td]jad[/td][td]

4​

[/td][td]

4​

[/td][td][/td][td]vad[/td][/tr]
[tr][td]

4​

[/td][td]vad[/td][td]

4​

[/td][td]

5​

[/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]nad[/td][td]

3​

[/td][td]

1​

[/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td]vad[/td][td]

2​

[/td][td]

6​

[/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In E2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$6,SMALL(IF($B$2:$B$6+$C$2:$C$6=MAX($B$2:$B$6+$C$2:$C$6),
    ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($E$2:E2))),"")

 

[zazybang]

Thanks! Though if it doesnt bother you, may you breakdown the use of the SMALL function and what it means to (ROW)-(ROW)+1. Ive never understood ROW functions nor SMALL

 

[Aladin Akyurek]

Thanks! Though if it doesnt bother you, may you breakdown the use of the SMALL function and what it means to (ROW)-(ROW)+1. Ive never understood ROW functions nor SMALL

INDEX(resultrange,SMALL(IF(testrange=test,ROW(testrange)-ROW(INDEX(testrange,1,1))+1),ROWS(expandingimplementationrange))

Filled in with a concrete example...

INDEX($A$2:$A$6,SMALL(IF($B$2:$B$6="qad",ROW($B$2:$B$6)-ROW($B$2)+1),ROWS($E$2:E2)

where

[1] $A$2:$A$6 >> resultrange

[2] $B$2:$B$6 >> testrange

[3] "qad" >> test (more precisely: equality test)

[4] ROW($B$2:$B$6)-ROW($B$2)+1 = ROW($B$2:$B$6)-ROW(INDEX($B$2:$B$6,1,1))+1 >> ROW(testrange)-ROW(INDEX(testrange,1,1))+1

The [4] means an array of integers startin at 1 going up to the size of the testrange. For our example, this would give us {1;2;3;4;5}, the relative row numbers. ROW returns the native row numbers of the range it's fed with. ROW($B$2:$B$6)-ROW($B$2)+1 makes them relative.

[5] $E$2:E2, which grows when copied down to $E$2:E3, $E$2:E4, etc. >> expandingimplementationrange. Applyin ROWS to this, we get 1, 2, 3, 4, and 5, the progressive size of the range. These numbers serve as k of SMALL(array, k).

This translates as: get me from [4] the first smallest relative row number, the second smallest relative row number

So INDEX/SMALL formula says effectively: Hey SMALL fed INDEX with the next relative row number; hey INDEX return a the result from resultrange from that relative row position.

Hope this helps.


Feed INDEX with row numbers one by one which obtain when testrange=test, i.e. $B$2:$B$6="qad"