Max function

[Luis_Maximo]

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.

[#2]

@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

[#3]

@Joe User 

=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

[#4 · Accepted solution]

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

[#5]

@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.

That said, your solution of using IFS instead is good.