Interactive Excel Graph

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.
Graph-of-Spread-Data.xlsx
LVL 1
Michael KeithAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

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.

=COUNTA(4:4)

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

$B$5:INDEX($5:$5,$R$3)

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(B12:INDEX(12:12,$R$3),$B$5:INDEX($5:$5,$R$3),$R$5,$B$6:INDEX($6:$6,$R$3),$R$6,$B$7:INDEX($7:$7,$R$3),$R$7,$B$8:INDEX($8:$8,$R$3),$R$8,$B$9:INDEX($9:$9,$R$3),$R$9,$B$10:INDEX($10:$10,$R$3),$R$10)

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:

=IF(R5="*","all",R5)&CHAR(10)&IF(R6=">0","all",R6)&CHAR(10)&IF(R7=">0","all",R7)&CHAR(10)&IF(R8="*","all",R8)&CHAR(10)&IF(R9="*","all",R9)&CHAR(10)&IF(R10="*","all",R10)

Let me know if it helps.

cheers, teylyn

Graph-of-Spread-Data.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Michael KeithAuthor Commented:
This is a great solution and look forward to using it in other spreadsheets.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.