# Refining inefficient formula - for personal growth

#### Whatamk

##### New Member
I am rather proud of myself for the the following bit of formula, but with so many nested commands, I can't help but feel that I am doing this in a rather convoluted way. For the sake of growth, would you have approached this same question differently or more succinctly?

Drawing from this list of data...

 G H I J K L M N O P 2 Axis I Axis II Axis III Axis IV Axis V Blank Dx ≈ FI Ep. Dx TP Other 3 3 0 0 0 2 0 1 2 1 0 4 13 8 20 12 32 5 28 18 8 2 5 20 7 7 7 14 4 25 23 11 1 6 0 2 1 4 3 1 2 14 14 0

<tbody>
</tbody>

...I set about to create a list of the 3 highest categories (columns) for each row, resulting in...

 T U V 2 #1 error #2 error #3 error 3 Axis I Axis V Axis V 4 Axis V Dx ≈ FI Axis III 5 Dx ≈ FI Ep. Dx Axis I 6 Ep. Dx Ep. Dx Axis IV

<tbody>
</tbody>

As you can see, it's a little weird about equal numbers, but I can live with that. The formula I used was
Code:
``=IF(SUM(\$G3:\$P3)=0,"",INDIRECT(CONCATENATE(LEFT(CELL("address",INDEX(\$G3:\$P3,MATCH(LARGE(\$G3:\$P3,1),\$G3:\$P3,0))),2),"2")))``

Any suggestions or thoughts?

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
To avoid repeats for duplicate numbers try this array formula in T3

=IFERROR(INDEX(\$G\$2:\$P\$2,MATCH(1,(\$G3:\$P3=LARGE(\$G3:\$P3,COLUMNS(\$T3:T3)))*(COUNTIF(\$S3:S3,\$G\$2:\$P\$2)=0),0)),"")

confirmed with CTRL+SHIFT+ENTER and copied across and down.......I'm assuming column S is empty (or it can be populated but not with any value that might appear in G2:P2)

Works like a charm. Thank you, Barry! Definitely an improvement. I did make one modification: I put the =IF(SUM(\$G3:\$P3)=0,"",(formula) back in rather than IFERROR so zero values returned blank rather than Axis I, Axis II, and Axis III.

As I hoped would happen, I'm a bit baffled by some of what you used (1: index/match combination is brilliant, and 2: I'm still trying to figure out why you used column S for the COUNTIF), so now I'm having to detour from work while I research it.

Replies
0
Views
127
Replies
3
Views
221
Replies
2
Views
285
Replies
7
Views
236
Replies
16
Views
507

1,203,465
Messages
6,055,574
Members
444,799
Latest member
CraigCrowhurst

### 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.

### Which adblocker are you using?

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

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