Help : Scatter Charts - Position of data labels
- Thread starter anishms
- Start date
Thanks a lot vletm
My Likelihood Score and Impact Score in the dashboard is an average of the sub process in each function and that could be a fractional number.
Lets say in Finance, there are 11 sub process with likelihood and impact score for each sub process and
here in the dash board the likelihood and impact score will be the average of these 11 sub process,
In case if the average is coming 3.25 likelihood and 3 impact, that will show as High but the label is coming in yellow background.
Thanks for your response vletm and sorry for the delayed reply from my side
I referred your file
I was unable to upload the complete file previously due to large file size and I have attached it now after compressing for your reference and advice
I also wonder why the file size is 3.17 MB in .xlsb format. may be due to the power query connections.
I referred your file
I was unable to upload the complete file previously due to large file size and I have attached it now after compressing for your reference and advice
I also wonder why the file size is 3.17 MB in .xlsb format. may be due to the power query connections.
please find attached zip file
I have copied your codes in the attached file.
Could you please check and help
anishms
I skip those power query as well as other 'new DA...s'.
I could open Your #10-file.
You still skipped Your comments ..
You asked to help ... where did You write ... what?
My comments:
'Impact Score'-scale should be 0-5 as well as that other.
You haven't written needed scales.
I made some modifications with calculations, because values aren't whole numbers.
The chart will refresh after as I've written, it could do other way too.
I skip those power query as well as other 'new DA...s'.
I could open Your #10-file.
You still skipped Your comments ..
You asked to help ... where did You write ... what?
My comments:
'Impact Score'-scale should be 0-5 as well as that other.
You haven't written needed scales.
I made some modifications with calculations, because values aren't whole numbers.
The chart will refresh after as I've written, it could do other way too.
Thanks vletm
The help required is to plot the data labels in respective colors of risk rating without over lapping.
The scale is 1 to 5 for both Likelihood Score and Impact Score. And it won't be a whole number here because it is an average calculated from different sheets.
This is how it is coming now for me
The help required is to plot the data labels in respective colors of risk rating without over lapping.
The scale is 1 to 5 for both Likelihood Score and Impact Score. And it won't be a whole number here because it is an average calculated from different sheets.
This is how it is coming now for me
anishms
1) plot the data labels in respective colors - solved
2) without over lapping - solved as possible ... large chart helps it and if use #-numbers
3) if scale is from 1 to 5 then You should do something ... check below
- Your background picture starts from zero --- I marked Your possible used range (left and bottom ranges should be delete)
- I modify #18 value to 1/1 ... now left-bottom corner ... 'low' can be seen with two 'lines'!
- if case 5/5 - then that #-number will be top-right corner ... okay
BUT as You know --- if there is no 'white' range around 'possible used range' then there will be more tight with near outside values!
... and many times those #-values would be in very small area.
4) Your or any basic 'normal' versio ... if there are eg five times values 3/4
... then those would show as overlapped 5 --- mine with one label eg 1, 2, 3, 4, 5
1) plot the data labels in respective colors - solved
2) without over lapping - solved as possible ... large chart helps it and if use #-numbers
3) if scale is from 1 to 5 then You should do something ... check below
- Your background picture starts from zero --- I marked Your possible used range (left and bottom ranges should be delete)
- I modify #18 value to 1/1 ... now left-bottom corner ... 'low' can be seen with two 'lines'!
- if case 5/5 - then that #-number will be top-right corner ... okay
BUT as You know --- if there is no 'white' range around 'possible used range' then there will be more tight with near outside values!
... and many times those #-values would be in very small area.
4) Your or any basic 'normal' versio ... if there are eg five times values 3/4
... then those would show as overlapped 5 --- mine with one label eg 1, 2, 3, 4, 5
Hi vletm
Below is the heatmap
I have rounddown the X and Y values in your code to have the matching risk rating in column G to be in line with the chart colors
I hope it is fine now
Thanks for your support
Below is the heatmap
I have rounddown the X and Y values in your code to have the matching risk rating in column G to be in line with the chart colors
chk_ef = Application.WorksheetFunction.RoundDown(chk_e, 0) * Application.WorksheetFunction.RoundDown(chk_f, 0)I hope it is fine now
Thanks for your support
Last edited by a moderator:
Hi Vletm,
Happy New Year!
I have made some changes in the codes in the risk rating in column G due to change in the calculation
Request your help in the following
Could you please check and suggest is it fine have have so many If statements
It looks like data labels are over lapping due to my likelihood score and impact scores are very close after the actual risk assessment
Can you help to map the bg color as data label font color (Green, Orange and Red)
Exclude numbers if either likelihood score or impact score is blank. (blanks are due to functions are pending for risk assessment)
Happy New Year!
I have made some changes in the codes in the risk rating in column G due to change in the calculation
Request your help in the following
Could you please check and suggest is it fine have have so many If statements
It looks like data labels are over lapping due to my likelihood score and impact scores are very close after the actual risk assessment
Can you help to map the bg color as data label font color (Green, Orange and Red)
Exclude numbers if either likelihood score or impact score is blank. (blanks are due to functions are pending for risk assessment)
Why did You something like that?
I made some changes in the code due to the change in risk rating calculation. Earlier it was based on the composite score (chk_ef = chk_e * chk_f). But if I take composite score, 9 is coming in both Moderate and High as highlighted below. Hence, I need it to be based on the X and Y axis position in the heat map.
Didn't my code work?
Your code is working perfectly. I can do only minor changes as I'm completely a newbie to VBA.
I thought of making the changes wherever I can and ask your help otherwise you will fire me for the change request.
Request your help in the following
1) Below is the change required in calculation
Do nothing if chk_e = "" Or chk_f = 0
Low
chk_e <= 1.5 And chk_f <= 2.5
chk_e <= 2.5 And chk_f <= 1.5
Moderate
chk_e <= 1.5 And chk_f <= 5.5
chk_e <= 2.5 And chk_f <= 4.5
chk_e <= 3.5 And chk_f <= 3.5
chk_e <= 4.5 And chk_f <= 2.5
chk_e <= 5.5 And chk_f <= 1.5
Else High
2) It looks like data labels are over lapping due to my likelihood score and impact scores are very close after the actual risk assessment.
3) Can you help to map the bg color as data label font color (Green, Orange and Red).
4) Exclude numbers if either likelihood score or impact score is blank. (blanks are due to functions are pending for risk assessment).
I made some changes in the code due to the change in risk rating calculation. Earlier it was based on the composite score (chk_ef = chk_e * chk_f). But if I take composite score, 9 is coming in both Moderate and High as highlighted below. Hence, I need it to be based on the X and Y axis position in the heat map.
Didn't my code work?
Your code is working perfectly. I can do only minor changes as I'm completely a newbie to VBA.
I thought of making the changes wherever I can and ask your help otherwise you will fire me for the change request.
Request your help in the following
1) Below is the change required in calculation
Do nothing if chk_e = "" Or chk_f = 0
Low
chk_e <= 1.5 And chk_f <= 2.5
chk_e <= 2.5 And chk_f <= 1.5
Moderate
chk_e <= 1.5 And chk_f <= 5.5
chk_e <= 2.5 And chk_f <= 4.5
chk_e <= 3.5 And chk_f <= 3.5
chk_e <= 4.5 And chk_f <= 2.5
chk_e <= 5.5 And chk_f <= 1.5
Else High
2) It looks like data labels are over lapping due to my likelihood score and impact scores are very close after the actual risk assessment.
3) Can you help to map the bg color as data label font color (Green, Orange and Red).
4) Exclude numbers if either likelihood score or impact score is blank. (blanks are due to functions are pending for risk assessment).
anishms
Why did You something like that?
Is Your heatmap now ... correct?
You've changed it already once!
Did You changed it again?
Didn't my code work?
You wrote Your code is working perfectly. ... but seems that it didn't ... which one?
Of course You can change that code as You want
... but should it work?
Here, some of Your ~"9" values with my files heatmap as You have wanted ... before 'some changes'
I won't comment based ...
... some changes
... if I cannot know - what do would like to have for results now?
I need clear facts to continue.
Why did You something like that?
Is Your heatmap now ... correct?
You've changed it already once!
Did You changed it again?
Didn't my code work?
You wrote Your code is working perfectly. ... but seems that it didn't ... which one?
Of course You can change that code as You want
... but should it work?
Here, some of Your ~"9" values with my files heatmap as You have wanted ... before 'some changes'
I won't comment based ...
... some changes
... if I cannot know - what do would like to have for results now?
I need clear facts to continue.
The heatmap is final now. it is given in the sheet "Heat Map BG V2
Now it should be based on the X and Y axis position in the heat map accordingly the heatmap background i have changed
Can you help to map the bg color as data label font color (Green, Orange and Red).
Offer only wanted.
Now it should be based on the X and Y axis position in the heat map accordingly the heatmap background i have changed
Can you help to map the bg color as data label font color (Green, Orange and Red).
Offer only wanted.
Last edited by a moderator: