Variable Graphs

  • Thread starter Thread starter JON
  • Start date Start date
J

JON

I don't know if it is possible but I would love an answer.

Let say that I have 100 data points ranging from 1 to
100. I want to create a chart that plots all 100 points
in decending order but highlights on the graph the
selection that the user of the model picks.

For example:

A user selects a given location and with that location is
an associated number (Average Salary). When the user
then plots the graph to see where his selection is in
conjunction to the other locations, his selection is
highlighted in a different color from all the other
corresponding salaries in graphic (column chart)form.

Can it be done
 
It is always surprising what is possible with XL charting. :)

Suppose your names are in A1:A100 and the values are in B1:B100. Also,
suppose the name of interest is in H1.

In C1 enter the formula =INDEX($A$1:$A$100,MATCH(D1,$B$1:$B$100,0))
In D1 enter the formula =LARGE($B$1:$B$100,ROW())
In E1 enter the formula =IF(EXACT(C1,$H$1),NA(),D1)
In F1 enter the formula =IF(ISNA(E1),D1,NA())

Copy C1:F1 down to rows 2:100.

Plot C1:C100, E1:F100 in a column chart, with C as the X data and E and
F as two separate series. Double-click the charted series. From the
Patterns tab set the Border to None. From the Option tab, set the
Overlap to 100 and the Gap Width to 0.

The data corresponding to the selection in H1 will stand out.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Microsoft MVP -- Excel
 
Back
Top