title: Why don't #N/A and MAX (or MIN) play well with each other and what can I do abut it
body: <p>I have a list of 12 cars and need to calculate MPG.  I have the miles driven and fuel consumed, so <code>MPG = miles / gallons of fuel</code> is easy enough to program.</p>
  
  <p>Then in the fourth column I want a "label" for the best and worst MPG and all others should just be blank.  Here is my formula:</p>
  
  <pre><code>=IF(C1=MAX($C$1:$C$12),"MAX",IF(C1=MIN($C$1:$C$12),"MIN"," "))
  </code></pre>
  
  <p>Simple enough. A snippet of my data follows.</p>
  
  <pre><code>Miles   Gallons     
  Driven  Fuel    MPG 
  1,145   101.936 11.2    MIN
  1,277   91.226  14.0     
  4,561   376.270 12.1     
  3,655   236.647 15.4     
  2,598   204.620 12.7     
  2,948   222.591 13.2     
  2,384   153.914 15.5     
  1,808   127.940 14.1     
  3,034   168.387 18.0     
  3,763   234.053 16.1     
  3,237   196.615 16.5     
  1,530   72.812  21.0    MAX
  
  Next Month
  2,515   191.196 13.2     
  1,908   111.129 17.2     
  1,589   112.410 14.1     
  2,223   147.278 15.1     
  2,221   145.936 15.2     
  #N/A    142.165 #N/A    
  1,928   177.609 10.9    
  1,150   92.072  12.5    
  #N/A    67.651  #N/A    
  79      2.849   27.7    
  407     22.241  18.3    
  10,537  94.960  111.0   
  </code></pre>
  
  <p>Obviously car 12 has bad data, but that's not my concern right now.  What is giving me trouble is the <code>#N/A</code> values for cars 6 and 9.  My handy dandy little formula </p>
  
  <pre><code>=IF(C1=MAX($C$1:$C$12),"MAX",IF(C1=MIN($C$1:$C$12),"MIN"," "))
  </code></pre>
  
  <p>returns <code>#N/A</code> for each vehicle.  I have tried to use</p>
  
  <pre><code>If(NOT(ISNA(C1)),IF(C1=MAX($C$1:$C$12),"MAX",IF(C1=MIN($C$1:$C$12),"MIN"," ")),"#N/A") 
  </code></pre>
  
  <p>and</p>
  
  <pre><code>If(ISERROR(C1),"#N/A" ,IF(C1=MAX($C$1:$C$12),"MAX",IF(C1=MIN($C$1:$C$12),"MIN"," "))) 
  </code></pre>
  
  <p>with the same results.</p>
  
  <p>Can my original equation be reworked to compensate for <code>#N/A</code>?</p>