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

Blvkcage

New Member
Joined
Jan 14, 2020
Messages
6
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Hi All,
How to show MAX in row that contain #N/A value ?

Max.png


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
Joined
Nov 7, 2011
Messages
1,854
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
Joined
Jan 14, 2020
Messages
6
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
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
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Phuoc

Active Member
Joined
Apr 29, 2016
Messages
458
Office Version
  1. 2016
Try this:

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,853
Office Version
  1. 365
Platform
  1. Windows
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,"")
 

Forum statistics

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

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top