Stepped chart with multiple series

[maillme]

Hi there,

I used a tutorial to create a stepped chart (Bank of England interest rates) - But i'd like to add another series (infact another two). The data unit is the same (% / interest rate) but the dates they change are different (but the range the same. E.g. 2007 to 2017). Can anyone advise how to do this?

When I try to simply add another series, it creates something weird (see below). I suepect it's to do with the changes being on different dates.

Incorrect (with multiple series):

Screen Shot 2017-10-14 at 16.20.55.png

Correct (only one series):

Screen Shot 2017-10-14 at 16.20.14.png


And pasted below the data:

Series 1:

Rate
11-Jan-07 5.00%
10-May-07 5.25%
05-Jul-07 5.50%
06-Dec-07 5.75%
07-Feb-08 5.50%
10-Apr-08 5.25%
08-Oct-08 5.00%
06-Nov-08 4.50%
04-Dec-08 3.00%
08-Jan-09 2.00%
05-Feb-09 1.50%
05-Mar-09 1.00%
04-Aug-16 0.50%
14-Oct-17 0.25%
01-Jan-07 5.00%
11-Jan-07 5.25%
10-May-07 5.50%
05-Jul-07 5.75%
06-Dec-07 5.50%
07-Feb-08 5.25%
10-Apr-08 5.00%
08-Oct-08 4.50%
06-Nov-08 3.00%
04-Dec-08 2.00%
08-Jan-09 1.50%
05-Feb-09 1.00%
05-Mar-09 0.50%
04-Aug-16 0.25%
14-Oct-17 0.25%

Series two:

15-Oct-08 4.25%
12-Nov-08 3.75%
10-Dec-08 3.25%
21-Jan-09 2.50%
11-Mar-09 2.00%
08-Apr-09 1.50%
13-May-09 1.25%
13-Apr-11 1.00%
13-Jul-11 1.25%
09-Nov-11 1.50%
14-Dec-11 1.25%
11-Jul-12 1.00%
08-May-13 0.75%
13-Nov-13 0.50%
11-Jun-14 0.25%
09-Dec-15 0.15%
16-Mar-16 0.05%
14-Oct-17 0.00%
01-Jan-07 4.25%
15-Oct-08 3.75%
12-Nov-08 3.25%
10-Dec-08 2.50%
21-Jan-09 2.00%
11-Mar-09 1.50%
08-Apr-09 1.25%
13-May-09 1.00%
13-Apr-11 1.25%
13-Jul-11 1.50%
09-Nov-11 1.25%
14-Dec-11 1.00%
11-Jul-12 0.75%
08-May-13 0.50%
13-Nov-13 0.25%
11-Jun-14 0.15%
09-Dec-15 0.05%
16-Mar-16 0.00%
14-Oct-17 0.00%

Any help is greatly appreciated. File also attached.

[AliGW]

Will you please attach a sample Excel workbook?

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[maillme]

Yes, I was having difficulty doing that.....

But have now attached it - thanks for the advice.

Neil

[Kaper]

Two basic ways to do it:
1) use XY (scatterplot) would be the quickest but then you could have some problems with "nice" date axis showin Jan 1st each year.
so I used method 2):
a) collect all dates for all institutions (column I in my sample file)
b) remove duplicates and sort ascending (column J)
c) duplicate the dates (in first set - yellow in column K - remove first date)
d) for duplicated dates (starting row 46 in example) use standard Vlookup to find data in interest rates tables, so in L46 (and similar in M46 and N46) and copy down:

Please Login or Register  to view this content.

e) for first set (yellow in example) L2 (and similar M2 and N2) and copy down formula is a bit more complicated:

Please Login or Register  to view this content.

The inner VLOOKUP finds last earlier date present in interest table and then this date is used in outer standard vlookup.

I attach sample file to study (of course steps a,b,c can be done in the same column. I did it in separete, to show step by step the way to go) and screenshot (just for reference)

PS. For FED I used lower values for last four periods

[maillme]

Czesc Kaper,

Thank you very much.

I almost understand what you have done. I'm going to keep at it.

So, essentially, you are finding out what each interest rate was at, at the time of any of the changes - so that if one changes (say, ECB) you add a value of the others - the current value)... etc etc?

thanks again!
Neil

[Kaper]

Or yet in other words:
1) List all days when at least one rate changes
2) For each of listed days and for each of rates write two values - "night before that day" and "night after that day"
If there was a change in given institution, these 2 figures will be different, if not (change only in other institutions) these 2 figures will be the same.

[maillme]

thanks again