How to show MAX in row that contain #N/A value

[Blvkcage]

Hi All,
How to show MAX in row that contain #N/A value ?

this is the formula =IF(C3=MAX($C$3:$C$9),C3," ") but the problem is if there are #N/A i can't show MAX.
Any help is appreciated.

 

[johnnyL]

I have never used this function, but I think it would look something like:

=AGGREGATE(4,6,$C$3:$C$9)

The '4' equates to Max
The '6' equates to Ignore errors

 

[Michael M]

It would also be a good idea to modify your formulas to avaiod the #N/A error
What formula is in Col "C"

 

[Blvkcage]

It would also be a good idea to modify your formulas to avaiod the #N/A error
What formula is in Col "C"

if i avoid #N/A Error it will be effect my custom chart, so i need #N/A if data not show.

I have never used this function, but I think it would look something like:

=AGGREGATE(4,6,$C$3:$C$9)

The '4' equates to Max
The '6' equates to Ignore errors

thanks for your help , but i need it to show in same row that contain max value

 

[Michael M]

CAn't you replace the #N/A with a zero ??

 

[Phuoc]

Try this:

=IF(ISERROR(C3),C3,IF(C3=AGGREGATE(4,6,$C$3:$C$9),C3," "))

 

[Blvkcage]

Try this:

=IF(ISERROR(C3),C3,IF(C3=AGGREGATE(4,6,$C$3:$C$9),C3," "))

Try this:

=IF(ISERROR(C3),C3,IF(C3=AGGREGATE(4,6,$C$3:$C$9),C3,"

Thanks alot Phuoc its work...

 

[Peter_SSs]

Thanks alot Phuoc its work...

I don't think Phuoc's formula needs the IF(ISERROR( part?
This seems to produce the same result

21 08 31.xlsm
	C	D
3	#N/A	#N/A
4	#N/A	#N/A
5	#N/A	#N/A
6	#N/A	#N/A
7	#N/A	#N/A
8	125,705,758	125705758
9	49,902,855
Mark Max

Cell Formulas
Range	Formula
D3:D9	D3	=IF(C3=AGGREGATE(4,6,C$3:C$9),C3," ")

The other question is: Do you really want a space character in cell D9 in this case? Space characters, tend to make you think, incorrectly, that the cell is blank.
Perhaps this would be better?

=IF(C3=AGGREGATE(4,6,C$3:C$9),C3,"")