Forum Discussion
Risk matrix auto formatting
Hi,
I am working on a risk matrix where you enter severity and probability of the risk on each axis and where they corss each others lines there is the risk. We have classified them in A-E where A is the highest risk and severity is S0-S5, Probability is P0 to P5. where they meeet in the matrix I would like the matrix to get the right colour in a scale from dark green, via light green, yellow and orange up red for the worst case. How can I builds this into an excel formula where also each risk value A-E is displayed in teh coloured cell?
3 Replies
- so first of all colors can not be applied using cell functions/formulas. This can be done using Conditional Formatting (or using VBA/macros). That said it would be easier to apply conditional formatting if you had numbers and then you could apply a single color scale rule. Using the A-E you could apply 5 different rules (1 for each letter/color).
mtarler thank you
not being with my Computer for the moment, can you show me a sample?
OtherthenOlov0417 In the attached I gave example of gradient coloring and individual coloring. There are 6 rules. 1 rule for column I and 5 rules for column J
the coloring options for the gradient (col I) are more limited as it is interpreted based on 3 colors (high, mid, low) and you don't have control of other aspects (not that couldn't 'layer' additional formatting rules on top)
the coloring options for col J are completely flexible as you can specifically define each of the 5 rules to format the colors, text, borders, etc, how you want.
