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

#### Blvkcage

##### New Member
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.

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### johnnyL

##### Well-known Member
I have never used this function, but I think it would look something like:

VBA Code:
``=AGGREGATE(4,6,\$C\$3:\$C\$9)``

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

#### Michael M

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

#### Blvkcage

##### New Member
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:

VBA Code:
``=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

##### Well-known Member

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

#### Phuoc

##### Active Member
Try this:

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

#### Blvkcage

##### New Member
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

##### MrExcel MVP, Moderator
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
CD
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
8125,705,758125705758
949,902,855
Mark Max
Cell Formulas
RangeFormula
D3:D9D3=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,"")

Replies
17
Views
202
Replies
12
Views
346
Replies
7
Views
450
Replies
0
Views
240
Replies
0
Views
184

1,148,275
Messages
5,745,803
Members
423,976
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back