Google Sheets – Array Formula to Show Leading Zero

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

This seems to be easiest solved with a script. If you have a list of philosopher names (column A) and their abbreviations (column B):

Name        | Abbreviation
--------------------------
Socrates    | soc
Plato       | pla
...

in a sheet named Philosophers, you should be able to use the following script function:

function getPhilosophersByAbbreviations(abbreviationsStr) {
  var philosophers = SpreadsheetApp.getActive().getSheetByName("Philosophers").getDataRange().getValues();
  var result = new Array();
  var abbreviations = abbreviationsStr.split(",");
  for (var a = 0; a < abbreviations.length; a++) {
    var abbreviation = abbreviations[a];
    for (var p = 0; p < philosophers.length; p++) {
      if (philosophers[p][1] == abbreviation) {
        result.push(philosophers[p][0]);
      }
    }
  }
  return result;
}

In your data sheet, you enter the formula in column B:

soc     | =getPhilosophersByAbbreviations(A1)
pla     | =getPhilosophersByAbbreviations(A2)
des     | =getPhilosophersByAbbreviations(A3)
heg     | =getPhilosophersByAbbreviations(A4)
heg,des | =getPhilosophersByAbbreviations(A5)

... and the full names will be displayed in columns B and so on.

Feel free to play with the example spreadsheet I set up.