3 variables with y-axis (years)

Excel charts can show a lot of different things. What you want may be possible, but I did not understand what you want the yellow lines to do, so I can't help. Can you explain in more detail what you are trying to achieve?

I'm still not sure I understand. What I see in your current charts:

1) You have a stacked area chart that is using columns B and C together as a multi-level category x axis with a single set of Y values to plot against those X values. If I understand, what you want is to have column B be the horizontal category axis labels and columns C and D to be two separate sets of Y values plotted against the X categories. One of the columns should plot on the primary Y axis and the other should plot on the secondary Y axis. Does that sound right? If that is what you want, I would expect to pull up the select data dialog, change the category axis values for the chart to be column B, add another data series to be column C. Then, exit the select data dialog and select one of the data series and format the series so it is plotted on the secondary axis.

2) You also have an XY scatter chart that is showing column B on the X axis and column C on the primary Y axis and column D on the secondary Y axis. This is what I understand you want, so I am not sure what should be different on this chart.

If I understand, yes, you can set up the chart's axes to have this kind of scaling. Most of the question is how much do you want it automated.

First, I will observe that an axis where 2% corresponds to 50 (100% full scale would correspond to 2500) and 5% corresponds to 100 (100% full scale corresponds to 2000) is not as simple as one would assume at first glance. Most of the time, the expectation is that the relationship between the primary and secondary axes is relatively simple (a simple proportion for example secondary=k*primary). The example you give appears to be more complicated, but that is mostly about understanding the relationship between the two axes, which is not important in understanding the programming options.

Easiest is to simply do it manually. Select an axis -> Format axis -> change the min, max, major unit, minor unit values to get the desired axis range and tick mark spacing.

If you need a higher level of automation, then you can follow this tutorial here to link these chart axes formatting values to spreadsheet cells: https://peltiertech.com/chart-udf-control-axis-scale/ From there, it's just a matter of figuring out what formulas to enter into the cells.

An alternative approach, that may be particularly pertinent for something with uneven spacing of the tick marks (like your example numbers suggest) would be to "fake" the secondary axis with a dummy series like described here: https://peltiertech.com/secondary-ax...tional-scales/

At this point, it is mostly about deciding how you want to interact with the chart/spreadsheet and how much automation you want and exactly what the desired relationship should be between the two axes. Look over the tutorials and see what you want to do.