Conditional Formatting Query

[chandelirious]

Hi,

I'm putting a spreadsheet together that will compare National results with local scores.

I think Conditional Formatting is what I need for this, but I'm not sure exactly what formula to use.

The attached image displays what I'm working with, and the colour scheme that I need to implement.

So for example, KPI 1 is 70.

Location 1's KPI 1 score is 76, so as that is actually above the National result, I would want that cell to be coloured green.

The scoring system would go like this:

Anything from 3 points below and above National, the cell would be coloured green.

Between 4 and 10 points below National, the cell would be coloured yellow.

Between 11 and 19 points below National, the cell would be coloured red.

Anything that is 20 points or more below National, the cell would be coloured purple... I know.

I know how to use conditional formatting to change the colour based on a set value, but I can't figure out how to do it based on the value in a cell.

I just used RANDBETWEEN to fill the cells - I just want to get this working in draft before I begin work on the main spreadsheet, which has 93 locations!

Thanks very much for your help.

 

[6StringJazzer]

The model to follow is the option "Use a formula to determine which cells to format." Select the cells for all locations (from D12 to K whatever-the-last-row-is) and use formulas like this:

Green
=D12>=D$9-3

Yellow
=D12>=D$9-10

and so forth.

If you provide an actual table of data instead of a picture I could provide a sample file but I am not going to type in all your data from scratch.

 

[etaf]

does this work for you

Book2
	A	B	C	D	E	F	G
1
2			Anything from 3 points below and above National, the cell would be coloured green.
3
4			Between 4 and 10 points below National, the cell would be coloured yellow.
5
6			Between 11 and 19 points below National, the cell would be coloured red.
7
8			Anything that is 20 points or more below National, the cell would be coloured purple... I know.
9
10	National			70	56	98	86
11
12	Loc1			86	1	70	9
13	Loc2			68	63	92	30
14	Loc3			96	37	71	13
15	Loc4			52	73	29	1
16	Loc5			59	48	49	93
17	Loc6			66	68	52	85
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D12:G17	Expression	=D12>=D$10-3	text	YES
D12:G17	Expression	=D12>=D$10-10	text	YES
D12:G17	Expression	=D12>=D$10-19	text	YES
D12:G17	Expression	=D12<=D$10-20	text	YES

Book2
	A	B	C	D	E	F	G	H	I
1
2			Anything from 3 points below and above National, the cell would be coloured green.
3
4			Between 4 and 10 points below National, the cell would be coloured yellow.
5
6			Between 11 and 19 points below National, the cell would be coloured red.
7
8			Anything that is 20 points or more below National, the cell would be coloured purple... I know.
9
10	National			70	56	98	86
11
12	Loc1			70	93	9	79
13	Loc2			99	67	21	43
14	Loc3			84	8	15	7
15	Loc4			61	19	54	48
16	Loc5			13	12	47	67
17	Loc6			68	34	4	45
18
19
20
21
22
Sheet1

Cell Formulas
Range	Formula
D12:G17	D12	=RANDBETWEEN(1,100)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D12:G17	Expression	=D12>=D$10-3	text	YES
D12:G17	Expression	=D12>=D$10-10	text	YES
D12:G17	Expression	=D12>=D$10-19	text	YES
D12:G17	Expression	=D12<=D$10-20	text	YES

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[chandelirious]

does this work for you

Book2
	A	B	C	D	E	F	G
1
2			Anything from 3 points below and above National, the cell would be coloured green.
3
4			Between 4 and 10 points below National, the cell would be coloured yellow.
5
6			Between 11 and 19 points below National, the cell would be coloured red.
7
8			Anything that is 20 points or more below National, the cell would be coloured purple... I know.
9
10	National			70	56	98	86
11
12	Loc1			86	1	70	9
13	Loc2			68	63	92	30
14	Loc3			96	37	71	13
15	Loc4			52	73	29	1
16	Loc5			59	48	49	93
17	Loc6			66	68	52	85
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D12:G17	Expression	=D12>=D$10-3	text	YES
D12:G17	Expression	=D12>=D$10-10	text	YES
D12:G17	Expression	=D12>=D$10-19	text	YES
D12:G17	Expression	=D12<=D$10-20	text	YES

Book2
	A	B	C	D	E	F	G	H	I
1
2			Anything from 3 points below and above National, the cell would be coloured green.
3
4			Between 4 and 10 points below National, the cell would be coloured yellow.
5
6			Between 11 and 19 points below National, the cell would be coloured red.
7
8			Anything that is 20 points or more below National, the cell would be coloured purple... I know.
9
10	National			70	56	98	86
11
12	Loc1			70	93	9	79
13	Loc2			99	67	21	43
14	Loc3			84	8	15	7
15	Loc4			61	19	54	48
16	Loc5			13	12	47	67
17	Loc6			68	34	4	45
18
19
20
21
22
Sheet1

Cell Formulas
Range	Formula
D12:G17	D12	=RANDBETWEEN(1,100)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D12:G17	Expression	=D12>=D$10-3	text	YES
D12:G17	Expression	=D12>=D$10-10	text	YES
D12:G17	Expression	=D12>=D$10-19	text	YES
D12:G17	Expression	=D12<=D$10-20	text	YES

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Thank you for this, I appreciate it.

I did download XL2BB, however, restrictions on my work laptop prevented me from installing it. I didn't really consider using a shared drive, I may do that in the future if I can't get around XL2BB.

Thanks again.

 

[etaf]

ok,
did those conditional formatting formulas solve the issue
Note they are placed in the order shown and use stop of true

 

[chandelirious]

ok,
did those conditional formatting formulas solve the issue
Note they are placed in the order shown and use stop of true

Yes, I've tried them today - they work exactly how I need them to. Thank you!

 

[etaf]

you are welcome