Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

Edit: Link to Spreadsheet
[removed]

Edit 2: I just now saw some people trying to look into this, but i had changed the original spreadsheet since then. Sorry i just noticed your comments. I've restored the spreadsheet to the previous revision and copied it to the link below. This link will not be changing. Even though i've changed my spreadsheet logic for this already, i am still interested in the answer (and the bounty is still there).

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

Copy of Spreadsheet (non-changing)
https://docs.google.com/spreadsheet/ccc?key=0Aq6pdGj-zwyedEt0NFdhRDYzY25HVHl5S0o2UlEwMFE&usp=sharing Sheet "Fitting Tool", cell L18 (change N9:9 in formula to N18:8), but this will be repeated throughout the L:L column

share|improve this question
    
Can you share a doc with us? –  Jacob Jan Tuinstra Feb 18 '13 at 6:57
    
Added link to OP –  Slav Feb 18 '13 at 11:22
    
Sorry, i only saw the comments from some people looking into this right now. I've reverted the spreadsheet and saved it under a different link (read OP). Thank you –  Slav Feb 25 '13 at 19:56
    
Is this the original sheet? –  Jacob Jan Tuinstra Feb 25 '13 at 20:59
    
Sheet Weapons doesn't exist..... –  Jacob Jan Tuinstra Feb 25 '13 at 21:11

2 Answers 2

up vote 0 down vote accepted

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:

function skills(array1, array2, array3) {
  var array = new Array();

  for(i=0, len2=array2.length; i<len2; i++) {
    var prod = 1;
    for(j=0, len1=array1.length; j<len1; j++) {
      if("% " + array2[i][0] == array1[j][0]) {        
        prod *= array1[j][2] + 1;        
      }      
    }
    var value;
    if(array3[i][0] == "") {
      value = "";
    } else {
      value = prod * array3[i][0];
    }
    array.push([value]);
  } 
  return array;
}

For column J, I created te following script:

function mods(array1, ModAttrb, ModHeader, ModStats, array5, array6) {
  var array = new Array(), aSecond = new Array(), aThird = new Array();

  // second calculation  
  for(i=0, len1=array1.length; i<len1; i++) {    
    var secondSum=0;    
    for(j=0, len2=ModAttrb.length; j<len2; j++) {      
      if(array1[i][0] == ModAttrb[j][0]) {        
        for(l=1, len4=ModStats[0].length; l<len4; l++) {
          if(ModStats[j][l] == 0 || ModStats[j][l] == "") {
            secondSum += 0;
          } else {
           secondSum += ModStats[j][l];
          }
        }
      }
    } 
    aSecond.push(secondSum);
  } 

  // third calculation
  for(i=0, len1=array1.length; i<len1; i++) {
    var thirdSum=0;
    for(m=0, len5=array5.length; m<len5; m++) {
      if(array1[i][0] == array5[m][0]) {        
        thirdSum += parseInt(array5[m][3]);        
      }      
    }
    if(thirdSum == 0) {
      thirdSum = 0;
    } 
    aThird.push(thirdSum);
  }  

  for (var i=0; i < aSecond.length; i++) {
    var result = 0;
    result += (array6[i] * 1 + parseInt(aSecond[i]) + parseInt(aThird[i]));
    array.push([result])
  }  
  return array;
}

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

share|improve this answer
    
Thanks, you've done a lot of work, i will have to look into everything that you've done to understand it –  Slav Mar 2 '13 at 19:46

Plz all these two functions in your script editor and run and than =SHEETNAME() in any cell will return you the result

function SHEETNAME() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveRange().getSheet();
  return s.getName();
}

function GETSHEETKEY() {
  //Browser.msgBox('Spreadsheet key: ' + SpreadsheetApp.getActiveSpreadsheet().getId());
  var s = SpreadsheetApp.getActiveSpreadsheet().getId()
  return s;
}
share|improve this answer
    
Does this really answer the question? –  Jacob Jan Tuinstra Nov 13 '13 at 22:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.