Google-sheets – Formula or script for highest number starts with alpha

google sheetsgoogle-apps-scriptworksheet-function

I am looking for a formula or script that can show me the highest number within a range of values that have a leading alpha character. For example:

D101
D102
D103
D104
J101
J102
J103

I would like a cell that shows the highest D# in column is: D104 & highest J# in col is J103.

I tried using RIGHT, in combination with MAX but was unsuccessful. This is the formula I tried which evaluates to 0:

=MAXA(B:B,RIGHT(B:B,3))

Best Answer

Please try:

=ArrayFormula(max(value(right(filter(A:A,Left(A:A)="D"),3))))

and adjust D to the letter of your choice (or parameterise it).

MAX
VALUE
RIGHT
FILTER
LEFT

Expect 0 from attempts at maths operations on string functions since these always return Text.

Related Solutions

Google Sheets – How to Get Average of Last Four Values in a Row

Try using OFFSET to get the range and AVERAGE function to average, i.e. like this for row 2:

=iferror(average(offset(C2,0,max(0,count(C2:Z2)-4),1,4)),0)

If there are fewer than 4 values, that will average them all, otherwise just the last 4—if there are no values you get 0. You can change that to a blank by replacing 0 at the end of the formula with "".

This formula works in both Excel and Google Spreadsheets.

Google-sheets – Make dynamic array blank based on adjacent values in Google Spreadsheets

Direct answer

The following formula return the expected results for the three cases exposed in the question. It should be added to cell A2 and cells in A3:A13 should be empty.

=ArrayFormula(
  IF(
    NOT(
      (ROW(B2:B13)-1=1)
      *
      (B2:B13="$")
    )
    *
    (
      (B2:B13=">")
      +
      (B2:B13="$")
      +
      (ROW(B2:B13)-1>=Vlookup("$",{B2:B12,ROW(B2:B12)},2,0))
    )
    *
    NOT((B1:B12=">")
    *(B2:B13="$")),
    ".",
    " "
  )
)

Explanation

In this case the use of ArrayFormula function together with references to B2:B13 will return an array of 12 rows and 1 columns with the expected values.

The comparison of a range or array with a scalar value returns an array of the same size of the range or array. All the tests should return an array of the same size.

AND and OR functions could not work with arrays, but Google Sheets interpret TRUE as 1 and FALSE as 0, so, instead of using AND function use * and instead of OR use +.

As + and * take precedence over comparison operators each test should be enclosed in parentheses.