Ask Your Question

Single chart with two lines with different categories

asked 2017-10-22 22:34:58 +0100

mbf82 gravatar image

updated 2017-10-23 09:33:31 +0100

I have some data like this:

X1 | Y1
1  | 100
2  | 120
3  | 130
5  | 160

X2 | Y2
1  |  80
3  | 110
4  | 140
5  | 155

Basically the values (Y) are associated with a corresponding X value. The X values are not continuous and have some “holes”.

I need to put both on a single line chart, but, if I use the X1 values as categories, then the Y1 plot is correct, but the Y2 isn't as the second value of Y2 (110) would end shown for X value 2, but its correct X value is 3.

I cannot change the format of the table, nor join the tables, unless I can do that automatically in a hidden sheet. The data needs to stay as it is.

To give more details on where I get the data from (in response to an answer I got), the data sets actually contain three different columns. A date, a number of days from that date (X), and the value (Y).
The X column is calculated with =DAYS(ValueFromTheDateColumn, DATE(InitialYear, InitialMonth, InitialDay)).
The initial date is different from the two data sets, so I cannot merge the two data sets into one as it would not make sense.

edit retag flag offensive close merge delete


What kind of chart?

Lupp gravatar imageLupp ( 2017-10-23 00:44:58 +0100 )edit

Lupp: a line chart, with points and lines style.

mbf82 gravatar imagembf82 ( 2017-10-23 09:33:10 +0100 )edit

(Sorry if I sound dogmatic.)
A 'Line'-chart not being an 'X-Y (Scatter)' chart is a bad thing in itself. Just consider what the line should stand for. Anybody with at least a basic technical education should suppose the line to tell something like " there is a development in time" or "...regarding an abscissa-variable scaled in one of the usual ways". 'Labels' aren't. Use 'Column' charts in cases where there is no scaled "development" and where slopes don't mean anything therefore.

Lupp gravatar imageLupp ( 2017-10-23 11:14:04 +0100 )edit

The Y value is a weight, so there's a development over time.

mbf82 gravatar imagembf82 ( 2017-10-23 21:54:04 +0100 )edit

Quoting @mbf82: "The Y value is a weight, so there's a development over time."
If so you should use X-Y charting anyway. The only thing needed is that what you called 'Label' also is numeric: the dates presumably.
All the other problems will simply vanish.
See coming amendment to my answer.

Lupp gravatar imageLupp ( 2017-10-23 22:53:53 +0100 )edit

3 Answers

Sort by » oldest newest most voted

answered 2017-10-23 00:52:18 +0100

enka88 gravatar image

updated 2017-10-23 00:53:31 +0100

You should join thoose table like this to get corrected result

X | Y1 | Y2

1 | 100 | 80

2 | 120 |

3 | 130 | 110

4 | | 140

5 | 160 | 155

edit flag offensive delete link more


I cannot as (1) I cannot change the format; (2) it would not make sense due to what the data represents. I edited my original post to give some more details.

mbf82 gravatar imagembf82 ( 2017-10-23 09:32:04 +0100 )edit

answered 2017-10-23 22:06:33 +0100

mbf82 gravatar image

I found a solution which is not perfect but good enough.

I added a new sheet for calculations only. The first cell (A1) is 1, A2 is =A1+1, A3 is =A2+1, etc.
The first cell in the second column (B1) is =VLOOKUP(A1,MainSheet.B:C,2,0), and so on. That is, it does a look up in Y1 of a value corresponding to the first column in the calculations sheet. The third colum contains another VLOOKUP for the values from Y2. I end up with a lot of “#N/A”, but that's OK.

edit flag offensive delete link more

answered 2017-10-23 11:22:42 +0100

Lupp gravatar image

updated 2017-10-23 22:57:00 +0100

Editing with regard to the recent comment by the OQ to his question:

Everything simple now. Just use the appopriate tool. 'Line' cahrts are of little use anyway. Use X-Y charting.
See this attached demo.

Orioginal answer:

Quoting @mbf82: "I cannot change the format of the table, nor join the tables, ... The data needs to stay as it is."

No way then. (I would tend to add "fortunately".)
Anybody post here, please, if knowing better.

Another sceptical comment: If you cannot change the rotten tire, you shouldn't drive the car.

Quoting @mbf82: "...unless I can do that {join tables} automatically in a hidden sheet. The data needs to stay as it is."
This is feasible, but not exactly simple.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-10-22 22:34:58 +0100

Seen: 426 times

Last updated: Oct 23 '17