Chart anf Function Problem

[fpengineer]

Here is my situation I have a risk assessment matrix and I am trying to
build a bar chart from the results. The matrix is set up in such a way
that there are a number of situations and each situation has three
levels each level has a numeric value associated with it. The higher
the number the greater the risk, for example:

Situation 1: Level 1 = 15, Level 2 = 45, Level 3 = 27
Situation 2: Level 1 = 32, Level 2 = 13, Level 3 = 9

I have conditionally formatted the cells that contain the 3 values for
each situation to change color based on the value, the highest number
is red, the middle number is yellow and the smallest number is green.

I want my chart to do the same thing automatically; as the numbers
change and the bars adjust I want them to change color. I have found a
tutorial that has helped me a bit:

http://peltiertech.com/Excel/Charts/ConditionalChart1.html

I have used and IF function to get the highest and lowest numbers to go
to the right cells but cannot figure out how to get the middle number
to be placed in the proper cell.

Hope this all makes sense. Can anyone help?

 

[AH]

Hi,

You could try to use functions SMALL or LARGE to reorder the levels. Let's
say that your example array is in range A13:

Situation 1: Level 1 = 15, Level 2 = 45, Level 3 = 27
Situation 2: Level 1 = 32, Level 2 = 13, Level 3 = 9

So that the values are in range B23

15, 45, 27
32, 13, 9

Now use the SMALL function.

E2: =SMALL(B22;1)
F2: =SMALL(B22;2)
G2: =SMALL(B22;3)
E3: =SMALL(B33;1)
F3: =SMALL(B33;2)
G3: =SMALL(B33;3)

Now the values in range B23 are in order from smallest to biggest per
line. Make the bar chart out of that area.

Hope this helps,

Asser

 

[fpengineer]

Asser,

Thank you for the reply. I tried to use the smal function but th
problem i ran into was that the bumbers shift cells based on thei
value and do not stay associated with the order that thy apear in th
matrix. So what i get is the 3rd bar is always the highest but th
third bar does not necessarilly refer to Level 3. this is why i lik
the example in the link i pasted.

let me paste the function i am using for high and low and see if tha
helps explain what i have so far

Low Number Cell (C2): =IF(AND(B2<B3,B2<B4),B2,NA())
Middle Number Cell (D2): ?
High Number Cell (E2): =IF(AND(B2>B3,B2>B4),B2,NA())

This just represents one row as you can see I am missing the formul
for the middle number i tried this:

=IF(AND(B3>B2,B3>B4,B3<B2,B3<B4),B3,NA()) but htis is only returnin
the #N/A in the cell.

maybe this will explaine my problem a little better

Mik

 

[Jazzer]

Hi Mike,

Now I understand your problem more clearly (I hope). You can stil
use the SMALL function, but use it a little diferently, like this:

C2: =IF(SMALL($B$2:$B$4,1)=B2,B2,NA())
D2: =IF(SMALL($B$2:$B$4,2)=B2,B2,NA())
E2: =IF(SMALL($B$2:$B$4,3)=B2,B2,NA())

C3: =IF(SMALL($B$2:$B$4,1)=B3,B3,NA())
D3: =IF(SMALL($B$2:$B$4,2)=B3,B3,NA())
E3: =IF(SMALL($B$2:$B$4,3)=B3,B3,NA())

C4: =IF(SMALL($B$2:$B$4,1)=B4,B4,NA())
D4: =IF(SMALL($B$2:$B$4,2)=B4,B4,NA())
E4: =IF(SMALL($B$2:$B$4,3)=B4,B4,NA())

Now make a stacked column chart out of the area C2:E4 and orientat
series in Columns.

After that, just change the coloring of the series in the way yo
want them.

- Asse

 

[fpengineer]

Thanks Asser that did the trick. it is funny that you came up with thi
i was actually thinking the same thing after you first sugestion bu
just couldn't put my finger on how to do it. Thanks again.

Mik