Can I format a number to a percentage of a previous cell?

[everlast1910]

Hi all,

First time post so sorry if this is in the wrong area.

I have been trying to mess about with something and can't seem to get my head around things.

What I ideally want is:

When we talk about showing progress from a start number so if the number is 2.2 in cell B2. In cell C2 if the number inputted is 10-15% greater I would like to be able to conditionally format that to go to light green. If the number in C2 is 16-25% progress to format to dark green and 25%+ to purple. And then the same formula for a number in putted in D2 so the number is a percentage better than the one inputted in B2 but also be able to play around with the percentages.

I would then easily somehow like it to then do the same for B3 to C3/D3 - B4 to C4/D4 etc

Is this something that is do able?

Thanks for any help.

All the best

Andy

[JohnTopley]

Try these CF rules

select "Use formula ........", enter formula below, FORMAT==>Fill==> select colour==> OK

=AND(C2>=B2*1.1,C2<=B2*1.15) light green

=AND(C2>=B2*1.16,C2<=B2*1.25) dark green

=c2>b2*1.25 purple

[everlast1910]

Brilliant

I assume I will then have to manually input B3-C4-D4 B5 etc?

Thanks again.

[everlast1910]

Sorry just seen an error I made.

It to turn yellow if it is equal to the B2 number

I would want the light green to be 1-15%

Sorry to be a pain.

[JohnTopley]

just add another CF

=C2=B2

Fill as Yellow

or is it

=AND(C2=B2,C2<=B2*1.15) light green

i.e. C2=B2 or <= B2*1.15

Confused by your reply as to which you want.

[everlast1910]

The =C2=B2 was exactly what I wanted thank you!

I just assume now I will need to manually input them for all the cells no way to copy the conditions?

[JohnTopley]

In CF, set "Applies to:" to the range you require

[everlast1910]

Very sorry but is there also a way at the bottom to show how many are green light green yellow red etc so it automatically tally's them up?

[everlast1910]

Thank you so much last question and I am done!

Is there a way to turn of the formatting if there is nothing in the cell because at the moment as the cells are blank they are formatting to yellow.

No worries if not!

[JohnTopley]

Not without VBA: you could add formulae which "mirror" the CF formulae

=SUMPRODUCT((C2:C10>=B2:B10)*(C2:C10<=B2:B10*1.15))

counts those in C >= B and <= B*1.15