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

Step 1: after specifiying that serie 2 is plotted on the secondary (y-)axis, click Add Chart Element > Axes >Secondary Horizontal on the Design tab of the Ribbon (under Chart Tools). This option doesn't become available until a series has been plotted on the secondary (y-)axis.

Step 2: select each series in turn and look at the formula bar. Each series has a formula of the form

=SERIES(name, x-values, y-values, order)

Manually edit x-values to point to the correct range. The formula for serie 1 becomes:

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

and that for serie 2:

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

(The sheet name might be different, of course)

By the way, the numbers in your date no row correspond exactly to the dates in the date row, so it's not clear to me why you want to distinguish between them.

Answer 4

Thank you! It does not work unfortunately.

Series 1 has now completely changed and the Y values are no longer the same.

I would need to find a mean for Excel to add the "age" row as and additional horizontal axis. For example, in my original plot, I would need Excel putting a 73.4 tick mark on the additional horizontal axis in correspondence of 41600 (or 22.11.2013).  73.4 is, in years, the age of an individual, born on 20/07/1940 (14812), at the date of 22.11.2013, i.e. (41600-14812)/365. And similarly for the following dates.

Maybe too much for Excel?

Answer 5

Bumping this up ...

Do I have a chance with Excel to solve this issue?