Conditional formatting

[Stefan22]

Hi,

I'm comparing data from two columns using "data bars".The problem is that what i see does not reflect the reallity,f.e. comparing 6 and 4 resulting in the same bar width as with 9 and 1.To solve this i'm seting the values myself,the shortest bar to 0 and the longest to 10 and it works fine that way.But it's almost impossible to do it manually with over 100 records.
Maybe i'm overlooking some simple option which could resolve this?

And the second thing that bothers me concerned data bars as well.Is there any option that could change the bars' colour based on the cell value?In above example if cell value is 1-4 then bar would turn red,5-7 blue and so on...Is it possible in excel 2007?

Any help will be greatly appreciated.

 

[Stefan22]

as far as multi coloured data bars is concerned, yes, it's possible, see here.

Many tnx.

Regarding your formula =MAX($E$27:$F$39) it gave me an idea,just should be"sum",not "max" as you suggest.But now when i try to copy this formula down the column it says "Run time error 13 type mismatch"

 

[p45cal]

Many tnx.

Regarding your formula =MAX($E$27:$F$39) it gave me an idea,just should be"sum",not "max" as you suggest.But now when i try to copy this formula down the column it says "Run time error 13 type mismatch"

You shouldn't be copying a formula down any column. It should be in the conditional formatting dialogue box, just entered once, covering the whole range of cells needing it.

 

[Stefan22]

Many tnx.

Regarding your formula =MAX($E$27:$F$39) it gave me an idea,just should be"sum",not "max" as you suggest.But now when i try to copy this formula down the column it says "Run time error 13 type mismatch"

Forget this,the second formula works great.

Thank you so much mate.

 

[Stefan22]

You shouldn't be copying a formula down any column. It should be in the conditional formatting dialogue box, just entered once, covering the whole range of cells needing it.

I know,i know...its 2.30 am here.

Many thx.

 

[Stefan22]

Unfortunately my problem is not solved with that p45cal's formula.It doesn't sum up those cells properly and the bars' length are not realistic at all.

The main issue here is that excel doesn't allow relative references for data bars and to do it manually(summing) with over 300 cells is next to impossible.

Anyone with an idea how to work around this excel's limitation?