The filter() function looks through a source array, and filters it according to condition
=FILTER(A:A,ISNUMBER(A:A))
.
My source array is a rather long formula, so I end up repeating the same formula twice in the ISNUMBER()
condition:
=FILTER(ARRAYFORMULA(1+(SORT(TRANSPOSE(FILTER($N9:9,ISNUMBER($N9:9),$N$3:$3="Skills [s]")),1,FALSE))*StackPenalty), ISNUMBER(ARRAYFORMULA(1+(SORT(TRANSPOSE(FILTER($N9:9,ISNUMBER($N9:9),$N$3:$3="Skills [s]")),1,FALSE))*StackPenalty)))
Is there any way I can write this shorter?
Any way to reference the source array by some name?
Index?
Any way to use ISNUMBER() condition without passing the whole source array?
To recap: I need a way to reference the first argument of Filter()
function in the second argument to that function, without repeating the whole argument explicitly.
Link to Spreadsheet. Sheet "Fitting Tool", cell L18 (change N9:9 in formula to N18:8), but this will be repeated throughout the L:L column.
Best Answer
I've been working on your file, even before the bounty period, and it as been a struggle. I wanted to give you the automation te
ARRAYFORMULA
can give in combination with a bit of Google Apps Script. In doing so, I wanted to understand the calculations a bit and thus I started with optimizing the first formula for column E:=ARRAYFORMULA(IF(A4:A<>"";ARRAYFORMULA(A4:A*D4:D);""))
For column H, I created to following formula:
=ARRAYFORMULA(IFERROR(VLOOKUP(G4:G30;Suits!A:AC;{MATCH(H2, suits!A1:AC1 ,0)}*SIGN(ROW(G4:G30));0),""))
For column I, I created the following script:
For column J, I created te following script:
Here, for the sake of time, I set the first calculation to be 1.
After that, time was running short. The Mult column is/was difficult to comprehense. We can work on it afterwards if you want?
See te example file I copied in te beginning: Fitting Tool