Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

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
0
Michael Keith
Asked:
Michael Keith
1 Solution
 
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
 
Michael KeithAuthor Commented:
This is a great solution and look forward to using it in other spreadsheets.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now