Interactive Excel Graph

Posted on 2011-10-28
Last Modified: 2012-05-12
I am looking to have an interactive graph that graphs the data attached.   In light blue above each column of data to be graphed are 7 rows of data that describe each issue.  Below that in column format is the yield to MMD for that issue.   At the far right is the average yield to MMD by each year, which is just represented by yr 1, yr 2, yr 3, etc.   So yr 1 has an avg, yr 2 has an avg and so on.   In another column is an adjusted average spread which I would like to adjust based on selecting critera that would show up in the 7 rows above each column.    But I want to be able to have multiple criteria.   For example one of the data that shows up above the column is BQ or NON BQ.   I would like to be able to select say BQ and only have the Adjusted Average Column sum up and average the columns that have a BQ.    The same goes for each of the describtions in Rows 5-10 above each column.   But I would like to base this potentially on 1 to 5 critieria.  So for instance I may want to sum and average only the BQ issues in 2006 with an A1 underlying rating, or maybe sum and average BQ issues in 2006, with and A1 rating and a par amount above $20,000,000.    I will be adding columns over time so will need it to be expandable.   The ultimate goal is to be able to graph the adjusted average spread column so that as criteria are selected the graph changes.   I am just not quite sure how to approach this and hoping for some suggestions.   Thanks for any help you can provde.
Question by:Apex623
    LVL 50

    Accepted Solution


    something like the attached maybe?

    See the chart on Sheet1. Enter criteria for the chart in the blue cells in column P.

    The Graph Data sheet references these cells in column R and uses them in the formula in R12 and below.

    To enable future addition of columns, R3 calculates how many columns in row 4 contain text. You should keep row 4 empty for the columns to the right of the last data column.


    The Adjusted avg spread is then calculated by using an Index function to pinpoint the last cell with data, for example


    This returns a cell range starting in B5 and ending in the column with the last value, as determined by R3.

    The full formula to calculate the adjusted spread based on the criteria is this:


    Sumifs() can work with wildcards and several comparison operators. Text can be compared with the wildcard sign * to return all values. Numbers can be compared with >0 to return all values.

    The comparison values are in R5:R10, fed by the manual entry on Sheet1, column P. There, you'll find some instructions on what to enter. If the criteria are entered with an invalid format, the result of the Sumifs() will be 0.  The model can be made more robust with data validation, but maybe this gets you on the way.

    A textbox on the chart displays the current criteria for the chart, so the chart can easily be copied and pasted and still retain the information about the parameters that feed it. The textbox for the parameter values is fed by a formula in V5 on the Graph Data sheet:


    Let me know if it helps.

    cheers, teylyn

    LVL 1

    Author Closing Comment

    This is a great solution and look forward to using it in other spreadsheets.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now