The table has a column containing numbers. But there are one or two #VALUE! Values ​​in this column. Because of this, the MAX function on this column yields #VALUE !. Can you please tell me how to get the maximum value from the correct numeric values ​​in this column?

    3 answers 3

    To bypass errors, cover-ups, etc. there is a function UNIT (), in the English version of AGGREGATE ().

    In the described case, the reference version of the sheet function is probably sufficient. for example

    =АГРЕГАТ(4;6;A1:A10)

    See ex. WorksheetFunction.Aggregate Method (Excel) or AGGREGATE function .

    • Your decision came up. Only I have an English version and the separator between the function arguments should be, not;. My Formula: = AGGREGATE (4.6, D $ 2: D $ 281) - Andrey Epifantsev

    Try using the array formula: For example, like this:

     {=МАКС(ЕСЛИОШИБКА($A$1:$A$5;""))}

    PS Put CTRL + SHIFT + ENTER

      Option:

       =МАКС(ЕСЛИ(ЕОШ(A2:A99);;A2:A8))

      Array formula, found by three keys Ctrl + Shift + Enter

      If the values ​​are in the ascending range, you can search for the last numeric value with a simple formula:

       =ПРОСМОТР(9E+307;A2:A99)

      In general, it is correct: not to allow errors to appear on the sheet — to bypass it in a formula that displays values ​​in this range. If data is pulled from the outside (without formulas), select the range and use the FIND / REPLACE tool to remove errors.