I have a list of 12 cars and need to calculate MPG. I have the miles driven and fuel consumed, so MPG = miles / gallons of fuel is easy enough to program.
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:
=IF(C1=MAX($C$1:$C$12),"MAX",IF(C1=MIN($C$1:$C$12),"MIN"," "))
Simple enough. A snippet of my data follows.
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
Obviously car 12 has bad data, but that's not my concern right now. What is giving me trouble is the #N/A values for cars 6 and 9. My handy dandy little formula
=IF(C1=MAX($C$1:$C$12),"MAX",IF(C1=MIN($C$1:$C$12),"MIN"," "))
returns #N/A for each vehicle. I have tried to use
If(NOT(ISNA(C1)),IF(C1=MAX($C$1:$C$12),"MAX",IF(C1=MIN($C$1:$C$12),"MIN"," ")),"#N/A")
and
If(ISERROR(C1),"#N/A" ,IF(C1=MAX($C$1:$C$12),"MAX",IF(C1=MIN($C$1:$C$12),"MIN"," ")))
with the same results.
Can my original equation be reworked to compensate for #N/A?
For Excel 2010 or later:
=IFERROR(LOOKUP(2,1/(C1=AGGREGATE({14,15},6,$C$1:$C$12,1)),{"MAX","MIN"}),"")