 SOLVED

# Max function

I have a set of data in functions(the data is in function not manually added) and I want to determine the largest number of this data set but the function does not recognize the data as numbers but rather as formulas soon the result gives zero.

4 Replies

# Re: Max function

@Luis_Maximo  wrote:  ``the [MAX] function does not recognize the data as numbers but rather as formulas soon the result gives zero``

I think you misinterpret the problem.

There is no problem with MAX referring to a cell that contains a numeric formula.   For example:

A1: =IF(TRUE, 1, 10)

B1: =IF(FALSE, 2, 20)

C1: =MAX(A1,B1) returns 20.

In your case, I suspect that the problem is:  the data is text, not numeric.

Looks can be deceiving, and the format of the cell does not matter.  Use formulas of the form =ISTEXT(A1) to confirm that A1 is text.

If you are unsure about how to fix the problem -- that is, to be sure that the data is numeric -- please provide concrete examples that demonstrate the problem.  Ideally, attach an Excel file.

As a wild guess, a common mistake is to write my example above as

=IF(FALSE, "2", "20")

The double-quotes around the numbers cause the result to be strings, not numeric values.

In that case, the fix is to remove the double-quotes around values that are intended to be numeric

# Re: Max function

``=IF(P3="NI";1;)&IF(P3="PI";2;)&IF(P3="I";3;)&IF(P3="PE";4;)&IF(P3="E";5;)``

I used this formula to change the letters (NI,PI,I,PE,E) to numbers to use the MAX formula to return from the dataset which is the highest value but its return 0

best response confirmed by Luis_Maximo (New Contributor)
Solution

# Re: Max function

I was able to solve using the IFS formula I think were too many formulas for the formula to recognize

# Re: Max function

@Luis_Maximo  wrote:  ``I think were too many formulas``

Another misinterpretation.  The "&" operator returns a string.  So

`=IF(P3="NI";1;)&IF(P3="PI";2;)&IF(P3="I";3;)&IF(P3="PE";4;)&IF(P3="E";5;)`

returns the string "1" or "2" etc or the null string if none of the conditions are true.

I suggested that you use ISTEXT to the type of the data that MAX sees.

It would behoove you to follow directions in the future.