In Column A, I have a set of numbers in a format where:

K = Thousand (10^3)
M = Million (10^6)
B = Billion (10^9)
...
N = Nonillion (10^30)
d = decillion (10^33)

And I would like to be able to find the maximum parsed value's index from this column.

I had already written a custom function in the Apps Script, so that I can parse these values into scientific notation, when I need to perform math with them:

/**
 * Parses scientific notation from short notation.
 * 
 * @pararm {number} input The value to parse.
 * @return The parsed number in scientific notation.
 * @customfunction
 */
function parse(input) {
  return String(input).replace('K', 'E 3').replace('M', 'E 6').replace('B', 'E 9').replace('T', 'E 12').replace('q', 'E 15').replace('Q', 'E 18').replace('s', 'E 21').replace('S', 'E 24').replace('o', 'E 27').replace('N', 'E 30').replace('d', 'E 33');
}

Ex: =parse("10B") would yield 10E 9.

I then took this, and attempted to make a function that will get the maximum value's index:

/**
 * Discern the maximum value contained in a column, and return its index 
 * 
 * @param {string} column The column letter to check
 * @param {number} start The first index (row) to check
 * @param {number} end The last index (row) to check
 * @customfunction
 */
function customMax(column, start, end){

  var max = 0;
  var maxIndex = 0;

  for(var i = start; i<= end;i  ){

    var value = SpreadsheetApp.getActiveSheet().getRange(column   String(i)).getValue();
    var parsedVal = parseInt(parse(value));

    if(parsedVal > max){
      max = parsedVal;
      maxIndex = i;
    }
  }

  return maxIndex;
}

This code makes sense to me, and looks like it should work, however:

With data such as this, 1d is much larger than 300T, however running =customMax("A", "1", "2") yields 1.

The only consistency that I have found is that the Apps Script seems to be ignoring the T, d, or any other following character, and solely going off of the base of the number.

CodePudding user response：

Use Number instead of parseInt for scientific notation

As described in the example of parseInt(),

string of a scientific notation would be omitted and not interpreted

Reference:

• Number
• parseInt()
• Number() vs parseInt() (share by @SputnikDrunk2)