Colour coding for a range of number values in excel.

[Chris_Cook]

Hi there.

I am using conditional formatting to try and colour my percent ranges automatically. I am using the "format only cells that contain" option and inputting my percent ranges (for example 1 - 20, 21 - 40 etc...). 

It works with every number, apart from if it ends in a zero! So anything that lands on 10, or 50 stays white. Can anyone help or suggest a better way to set this up?

Many thanks,

Chris.

[#2]

@Chris_Cook 

As a quick fix,

i would try 1-9.999 instead of 1-10 times, instead of 1-20 times 1-19.9999, etc. should actually work.

Format numbers as percentages

I would be happy to know if I could help with this information.

Nikolino

I know I don't know anything (Socrates)

[#3]

@NikolinoDE 

I've just tried it and it still leaves the whole numbers white.

It's just bizarre why it doesn't include the last number at the end of the range???

[#4]

If I may recommend sending the file (without sensitive data), this is the only way to determine with certainty where the error may be. Everything else is more time consuming than necessary.

Thx,
Nikolino

[#5]

@NikolinoDE 

Sure. Here it is. If you can fix it you would be a life saver! I've written on the sheet which column I would like to colour code.

Thank you.

[#6]

@Chris_Cook 

Please have a look at an example in the inserted file.

There is a mistake in thinking about your file. If it is red up to 20%, why does the next level start at 21%? One percent is missing.

For example, if it is 20.001%, it may not have any color.

Take a look at the example and if you have any questions please feel free to answer.

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

[#7]

Hi there.

Thank you for this. I would like to include 20 in the first band, so I'm guessing I should edit the rules to start at 21.001?

Is the correct?

Many thanks.

[#8]

I would like to include 20 in the first band, so I'm guessing I should edit the rules to start at 21.001?

then 1) 0%-20,9999% / 2) 21%-39,9999% / 3) 40% - 59,9999% / 4) 60% - 79,9999% / 5) 80% - 100%

That would be what you want it to be.

[#9]

Yes! :)
Amazing. Thank you so much.
I'm not mathematically coherent anyway, let alone putting it into excel.

[#10]

your welcome

[#11]

Ok...so now a new problem.

I had set the numbers to round up to the nearest whole number, so now the colours are in accurate if it is say 61, or 81 because the number might actually be 60.6 but it is rounding it up.

Is there anyway of the conditional formatting to read the actual number in the cell, (rounded up version) rather than the raw unrounded number, as I think it is reading the decimal points.

I hope that makes sense?

Chris.