How to add a secondary X axis to an Excel scatter plot?

Question

Please have a check at the following Excel chart.

I have a problem in plotting (x-y scatter plot) series using 2 horizontal axis, one being a date ("date no") and the other one a calculated person age at that date. Series 1 is on the primary vertical axis while Series 2 is on the secondary vertical axis.

The problem I have is plotting the 2nd horizontal axis ("age") corresponding to the "date no".

I had a similar question in the past but found the solution too complex to implement, There must be a more straightforward solution. Tks.

Answer 1

Indeed, a dummy series would be the way to do that.

Answer 2

I basically did what I described in my first reply, except that I used this for series 2:

=SERIES(Sheet1!$A$5,Sheet1!$B$3:$I$3,Sheet1!$B$5:$I$5,2)

Answer 3

Wonderful !! This is exactly what I need. Now I need to understand how it can be done for my new and existing similar plots.

Thanks a LOT!

Answer 4

Thank you so much! Problem solved!

Answer 5

Just an additional point. I did not check yet but assume the same could be applied if I only have one series (say series 1). Am I right?