I want the markers to take their place. Can I achieve this without coding or manually changing the highest and lowest value?
I'm not sure I understand the requirement here. As we noted earlier, the problem is that the H and L values belong on the secondary axis, but we cannot actually move them to the secondary axis because our use of the up/down bars and high/low lines will not allow us to have more than 4 data series on the secondary axis. The only way I can see to position the H and L values correctly is to change the values in column G and H. This change doesn't necessarily need to be done manually. Both axes are basic, linear axes, so a simple linear transformation can compute the value for the primary axis that corresponds with a given value on the secondary axis. See if this fits within your requirement of "without coding or manually changing the highest and lowest value:"
1) I need a table in the spreadsheet with the max and min of each axis.
-- primary -- secondary
max -- 1E7 -- 160
min -- 0 -- 100
in T1:V3
2) I will use the TREND() function to perform the transformation. In G2, I edit the formula to be =IF(C2=MAX($B:$E),TREND($U$2:$U$3,$V$2:$V$3,MAX($B:$E),NA()). A similar edit in H2. Copy/paste/fill down.
That should position the H L markers in the correct positions. As data are changed/added/edited, it should continue to work just fine as long as Excel's "auto" algorithm for the axis limits continues to choose the same values for the axis limits of both axes. If Excel ever chooses different axis limits, it will be easy enough to update those values in the spreadsheet, or you can set the axis limits to "fixed" at values of your choosing, and change them manually when change is needed. The only thing that is "off" is that a reader who goes to column G in the spreadsheet expecting to see 152.85 in G28 (or 122.45 in L7) will instead see a value around 9E6 (and 3.7E6). The solution to this problem might be to have helper columns perform the transformation and leave the values/formulas in columns G and H unchanged.
I don't know if that falls under the restriction of "manually changing the highest and lowest values," but, as I said, I don't see a way to do this without changing those values somehow. This approach should automate those changes. The only manual changes will be when the axis limits change. If you have the foresight now to say that 100 to 160 are good choices or to choose better values for the axis limits, you can further minimize how often those manual edits will need to be made.
Again, I don't see a way to do this without changing the values in G and H. Is that an acceptable approach to those changes?
Bookmarks