Highlight max positive and negative on chart.

The general approach is to add two more series to your chart, tied to an alternate axis. The method depends on your data structure - can you share a sanitized example workbook with your current chart?

What kind of strategies have you tried and rejected? I worry about repeating ideas that you already don't want to use.

My first thought, because of its simplicity, would be to simply add two additional data series to the clustered bar chart. One data series based on a column with a formula like =IF(OR(MAX(values)=thisvalue,MIN(values)=thisvalue),maxvalue,0) and the other data series based on a column with a formula like =IF(OR(MAX(values)=thisvalue,MIN(values)=thisvalue),minvalue,0).

I would start with that, see what you like and don't like about that, and then we can move forward from there.

Here's the workbook.

One data series based on a column with a formula like =IF(OR(MAX(values)=thisvalue,MIN(values)=thisvalue),maxvalue,0) and the other data series based on a column with a formula like =IF(OR(MAX(values)=thisvalue,MIN(values)=thisvalue),minvalue,0).

I've tried similar method, but for some reason I can't get the max value to display at the correct price, 4280 in this case. It always ends up all the way at the bottom.

You've done something with your uploaded sample file so that Excel will not allow me to even select or investigate what is in the chart.

If I trust LO Calc, I can open the file in LO Calc and examine the chart. First I note that the chart is using D3:D36 for the category axis labels for all data series. It appears that you have three data series for that category axis. The main data series using B3:B36 for the values, a second data series using only G3:G4 for the values (which, because there are only two cells in the range, will be charted against the first two categories on the category axis, which is why they are showing up at the bottom of the chart), and a third data series using only H3:H4 for the values (again, plotted against the first two categories).

If it would let me, I would make the following edits to your file (after checking my second thought below):

1) I don't know what column H is trying to do, so clear H and remove the Series 2 from the chart.
2) Edit Series 3 in the chart so that it points to G3:G36, like the main data series.
3) Enter a formula like I recommended in G3 =IF(OR(MAX($B$3:$B$36)=B3,MIN($B$3:$B$36)=B3),B3,0). Copy/paste/fill down to G36.

That should put gray, second data series, max/min bars next to the blue, first data series, max/min bars.

That's probably not the final solution. Now that we have the max/min bars in the appropriate position along the category axis, what would you like to do next?

Another thing I notice, you appear to be trying to set up data for a bar + XY scatter combination chart approach. If that is correct, then the edits I would make to your file are:

1) Remove series 2 from the chart.
2) Select series 3 and change the chart type to XY scatter (line without marker). If your copy of Excel doesn't automatically move this series to the secondary axis system and show both horizontal and vertical secondary axes, add both secondary axes to the chart.
3) By trial and error, find a combination of secondary vertical axis min/max and value/formula in H3:H4 that will position the XY scatter series at the correct vertical position.
4) Format the secondary horizontal axis to have the desired limits, if the default Auto setting is inappropriate.

Either approach can work. Try them and see what direction you want to go.

I uploaded the workbook again, hopefully this one is fine. I will try your suggestion when I get home.
In H I get min and max of B, and G shows me what "strike" the max of B is at, in this case 4280.

Thank you.

That's perfect, exactly what I was looking for.

Thank you MrShorty.

One more thing. Is there a way to extend the same series to both sides of the y axis? So that one series displays on positive and negative.
The only work around I've found is to plot another series with opposite values. But that doesn't look nice as the lines are not aligned, not to mention a ton of series.

I drew a black line on the chart at 4300 to show what I would like to achieve.

That sounds about right for a clustered bar chart like you are using here.

As a bar chart, my first thought would be use a stacked bar chart with a "sparse" data layout in the spreadsheet to make a "clustered and stacked" bar chart. See tutorial here https://peltiertech.com/charts-for-excel/cluster-stack/ . Note that most of the work is arranging the data in the spreadsheet, so a lot depends on how readily you can rearrange the data (or a copy of the data).

My second thought would be to return the idea of a bar + scatter combo chart. The spreadsheet work will be easier, but building the chart will be the difficult part. Here's a tutorial for making a generic bar + scatter combination chart that should be readily adaptable to the scenario where you want to add a horizontal line to bar chart: https://peltiertech.com/bar-line-xy-...hart-in-excel/

Look over those two tutorials, decide which approach you want to try, then let us know where you get stuck following the tutorials.