I am using a formula in Excel 2010 to display the individual with the most wins, as below:
IF($P$3="Most wins",SUM(LARGE(Wins,{1})),"FALSE")
where wins represents an array of an indefinite number of individuals.
However I would like to be able to autofill this, or similar, such that the next row displays the following:
IF($P$3="Most wins",SUM(LARGE(Wins,{2})),"FALSE")
Unfortunately I can't get the value inside the curly brackets to increment, which is causing no end off issues when the formula is going to be dragged down beyond 1,000 rows.
Is this possible, or am I using the wrong method?
=IF($P$3="Most wins",SUM(LARGE(Wins,ROW(A1))),"FALSE")
Place that in the top of your column and copy it down. As you do, the cell reference for A1 will change from A1 to A2 to A3, etc. The ROW function returns the number of the row of the cell reference.
Alternatively as pointed out in Darren's comment, you could also use ROW()-2 if you data is starting in the second row. This has the advantage if you ver deleted the 1 row or column, you will not screw up your cell references.