Chart Minimum and Maximum.

[ysherriff]

I am trying to highlight only minimum and maximum on a 12 months line chart. the formula for maximum: =IF($D2=MAX($D$2:$D$13),$D2,NA()) works perfectly
but my issue lies is that the minimum formula is picking up zero value. I want it to ignore the zero value. I have modified the formula but it is not working.

=IF($D2>0,NA(),MIN($D$2:$D$13,$D2,NA()))

Thanks for your help. Attached is the small excel spreadsheet with example

 

[Somendra Misra]

Hi,

Try below formula for column C value equal or above 0 in E2 and copy down:

=IF($C2=MIN(IF($C$2:$C$13>0,$C$2:$C$13)),$C2,NA())

Confirm with Ctrl+Shift+Enter.

If there are chances of -ve values in column C and you want to left them, than try below array formula.

=IF($C2>0,IF($C2=MIN(IF($C$2:$C$13>0,$C$2:$C$13)),$C2,NA()),NA())

Regards,

 

[jeffreyweir]

Another way: When you want to calculate the minimum non-zero item in an array, you can use an array-entered combination of SMALL and IF, like this:
=SMALL(IF(C2:C13>0,C2:C13),1)

See the attached file.

This link may also be of interest if you want to know how to do this type of thing on an XY chart: http://dailydoseofexcel.com/archives/2014/08/02/dynamic-vertical-line-on-a-scatterplot/

 

[ysherriff]

Thank you bothfor your help!!! much appreciated....