• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Scatter chart: How to prevent overlapping of labels that have same value???

S

Smitty15

New Member
I have built a scatter chart to show as a risk heat map, where Y-axis is Impact and X-axis is Likelihood. Couple challenges I have and could really use some help. Some info on the risks.


-Each has an individual risk ID #. No duplicates exist.

-Impact rating is either 1,3,5,7,or 9

-Likelihood rating is either 1,3,5,7,or 9


1) How do you prevent risks that have the same impact and likelihood rating from overlapping on the scatter chart?


2) Is there a way to conditionally format the label market based on a value from my data? If so, how would I do this?
 
Not sure if 1 is possible, it seems the nature of the problem would cause overlaps. For 2, your best bet would be to control how your chart data is created. Using formulas, you could create a "basic series" and a "special series". If condition is not met, basic series has value, special series has NA(). If it is met, do the reverse. Then, format the special series in the chart how you would like. Since NA() is ignored by scatter charts, this will give the appearance to the user of a singe series with some point(s) highlighted.
 
Thank you for the reply. I was able to figure out question #2. For question #1 however, I have built the following formula, which separates the labels somewhat, but there are still several duplicates. I have tried create unique ratings for each risk, while keeping the rating within the range for the scatter chart. Doing this, I used PERCENTILE function to break up the Risk ID #'s into quadrants, then tried to further separate the ratings by adding another unique value by SUM(Risk ID +/- percentile plus (1/Risk ID #), but it looks like this didn't have much of an effect.


AE15 = TRUE/FALSE from form control check box

AD18:AD104 = Risk ID # (separate number for each risk)

B18:B104 = 1-87 (number of risks)

Data!W2:W88 = Impact rating of either 1,3,5,7,9

Data!X2:X22 = Likelihood rating of either 1,3,5,7,9


=IF($AE$15,SUM(

IF($AD18<=PERCENTILE($AD$18:$AD$104,0.2),SUM(Data!X2-0.8,(1/$B18)),

IF(AND($AD18>=PERCENTILE($AD$18:$AD$104,0.21),$AD18<=PERCENTILE($AD$18:$AD$104,0.4)),SUM(Data!X2-0.4,(1/$B18)),

IF(AND($AD18>=PERCENTILE($AD$18:$AD$104,0.41),$AD18<=PERCENTILE($AD$18:$AD$104,0.6)),SUM(Data!X2,(1/$B18)),

IF(AND($AD18>=PERCENTILE($AD$18:$AD$104,0.61),$AD18<=PERCENTILE($AD$18:$AD$104,0.8)),SUM(Data!X2+0.4,(1/$B18)),SUM(Data!X2+0.8,(1/$B18))))))),

NA())
 
You must log in or register to reply here.
Back
Top