Need help with a complex graph in Excel (with filter)

[sjoerd1978]

Dear All,


I am trying to build a graph in Excel dat displays the movements in interest rates. De table contains the following details:

Bank Product Interest rate Effective date
A 1 7% June 12, 2011
A 3 8% February 21, 2012
B 2 4% April 8, 2011
B 3 9% January 14, 2012

I would like this graph to display the interest rates per product (a line) and upon effective date that the line jumps to the new interest rate level). This graphs must have the possabilitites to be filtered per bank as well as per product.

My Excel knowledge isn't sufficient to pull somethign like this off. Is there someone who can help me with this?

P.s. Excel 2007.

Regards,


Sjoerd

[teylyn]

Hello,

please explain the data. There is only one data point per product, assuming that product 3 is not the same product, since it is offered by different banks. Can you post a better data sample in an Excel file (so we don't have to recreate it)?

cheers,

[teylyn]

See the attached file.

In order to show a line, you need at least two data points, start and end. To create a step effect, you need to have a data point for the end of the lower rate on the same date as the start of the higher rate. Then use a XY scatter chart with lines to chart. Filter the data table to hide series in the chart.

cheers,

[sjoerd1978]

Teylyn,

You helped me a lot. Please find attached the final graph. Each answer always results in new questions. If you now add data (say Bank A has changed his interest rates for Personal Loans again), you manually need to adjust the data range in the graph as well. How can you make sure Excel automatically add this to the respective category?

Kind regards,


Stewart

[teylyn]

Hello Stewart,

look at the attached file. I've completely re-arranged the data. Every product is now listed in its own column. All the dates are in the leftmost column. Each data series uses ALL the dates as the X value and ALL the cells in its respective column as the Y value. Using a XY chart, only the data points that have values are plotted against the date, so if there are gaps or blanks, it does not matter. The rows don't even need to be in chronological sort order.

You can now add new dates at the bottom of the table and enter the interest in the column for the product. Then click the chart and drag the coloured outlines that sit around the data table to include the new data.

Refining this concept, you could use dynamic range names that automatically grow and shrink with the data, so you never have to re-define the data source again.

If you're comfortable to adjust the chart source manually, enjoy. If you would like to explore the low-maintenance approach with dynamic range names, please pipe up.

cheers,

[sjoerd1978]

Teylen,

Once again thank you very much for your response. Can't find the attachment....

Regards,

Stewart

[teylyn]

Dang possums. I'm sure I attached it, but the dogs must have eaten it, or the forum crashed.

I'll try again.

This time it worked, I think.