Basing calculations on cell colours - possible?

[billv84]

Hi all,

Hoping someone with a more powerful brain than me can help solve this problem!

I work in a school. We obviously record pupil progress in core subjects like maths and English in an MIS.
But for other subjects, something far simpler is acceptable.

We are trying to come up with an Excel template where we can have a vertical list of pupil names, then a horizontal list of topics within a subject running across the top.
For each topic, every pupil with be RAG-rated (i.e. red-limited progress, amber-getting there, green-got it).
To reduce teacher workload, we could apply red and amber by exception - i.e. the default is green.

What I'd like to know is if it's possible to have a 'total'-style column, running horizontally underneath the row of topic headers, which will show a percentage (or something similar) to indicate how much of the class have met expectations for that topic (i.e. been graded as green).

I've attached an image with a rough idea of what I'm trying to explain.

Thank you very much in advance for any help!

Bill

 

[James006]

Hi,
As another possibility, a simple UDF

Function mypct(rng As Range)
Dim c As Range, counter As Long
    For Each c In rng
        If c.Interior.Color = 255 Or c.Interior.Color = 49407 Then counter = counter + 1
    Next c
 mypct = Format((rng.Count - counter) / rng.Count, "00%")
End Function

In cell E6, just type =mypct(E7:E16)

 

[James006]

Forgot to mention, you can copy formula from cell E6 till cell J6

 

[billv84]

To me, this would be much easier to calculate and just as easy to use if you used your RAG rating in the cell and had conditional formatting apply the colour rather than manually applying colour.

In the sample below, in the conditional formatting I have made the font a slightly different colour to the background so that you can see it but in your actual sheet once you have it working, make the font in the CF exactly the same colour as the background 'Fill' colour and it will look just like your sample.
Start by entering a g (or G) in all the cells (select all cells, type g and confirm all with Ctrl+Enter).
Then if you want to mark a student as amber or red, just type an a or r in the relevant cell.
As you can see, the formula for the % calculation is then pretty simple.

billv84.xlsm
	D	E	F	G	H	I	J
5		Topic 1	Topic 2	Topic 3	Topic 4	Topic 5	Topic 6
6		60%	90%	100%	10%	0%	0%
7	Last	g	g	g	a	r	r
8	Last	g	g	g	a	r	r
9	Last	g	g	g	a	r	r
10	Last	r	g	g	a	r	r
11	Last	g	g	g	a	r	r
12	Last	g	g	g	a	r	r
13	Last	r	g	g	a	r	r
14	Last	g	g	g	a	r	r
15	Last	a	g	g	a	r	r
16	Last	a	r	g	g	r	r
Sheet1

Cell Formulas
Range	Formula
E6:J6	E6	=COUNTIF(E7:E16,"g")/COUNTA(E7:E16)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E7:J16	Expression	=E7="r"	text	NO
E7:J16	Expression	=E7="a"	text	NO
E7:J16	Expression	=E7="g"	text	NO

Hi Peter, sorry to be a pain, but I'm struggling to sort out the conditional formatting step here. Would you possibly mind giving me some idiot-proof instructions?! Many thanks!

 

[billv84]

Hi,
As another possibility, a simple UDF

Function mypct(rng As Range)
Dim c As Range, counter As Long
    For Each c In rng
        If c.Interior.Color = 255 Or c.Interior.Color = 49407 Then counter = counter + 1
    Next c
 mypct = Format((rng.Count - counter) / rng.Count, "00%")
End Function

In cell E6, just type =mypct(E7:E16)

Thanks James! I have never used this functionality in Excel, but will give it a go! Thanks again for your time

 

[James006]

You are welcome

Hope this solution is simple enough

 

[Peter_SSs]

I'm struggling to sort out the conditional formatting step here. Would you possibly mind giving me some idiot-proof instructions?!

For the layout shown with the first coloured cell being E7

1. Select from E7 to bottom right of area to be colored (J16 in the sample)
2. Home ribbon tab -> Conditional Formatting -> Clear Rules -> Clear Rules from Selected Cells
3. Home ribbon tab -> Conditional Formatting -> New rule ... -> Use a formula to determine which cells to format -> Format values where this formula is true:- =E7="g" -> Format... -> Fill tab -> Choose green -> Font tab -> Color -> Choose the same green -> OK -> OK
4. With the cells still selected, repeat step 3 but use the formula =E7="a" and set Fill & Font colour to amber
5. With the cells still selected, repeat step 3 but use the formula =E7="r" and set Fill & Font colour to red

The important thing is that the cell refence used in the formulas (E7 in this example) is whatever the active cell is in the range selected.

Now, typing "r" or "a" or "g" in one of the cells should set its colour and update the % calculation if the change was to or from green.

A sample workbook with the conditional formatting all set up can be accessed via this link: billv84_1.xlsm

 

[billv84]

For the layout shown with the first coloured cell being E7

1. Select from E7 to bottom right of area to be colored (J16 in the sample)
2. Home ribbon tab -> Conditional Formatting -> Clear Rules -> Clear Rules from Selected Cells
3. Home ribbon tab -> Conditional Formatting -> New rule ... -> Use a formula to determine which cells to format -> Format values where this formula is true:- =E7="g" -> Format... -> Fill tab -> Choose green -> Font tab -> Color -> Choose the same green -> OK -> OK
4. With the cells still selected, repeat step 3 but use the formula =E7="a" and set Fill & Font colour to amber
5. With the cells still selected, repeat step 3 but use the formula =E7="r" and set Fill & Font colour to red

The important thing is that the cell refence used in the formulas (E7 in this example) is whatever the active cell is in the range selected.

Now, typing "r" or "a" or "g" in one of the cells should set its colour and update the % calculation if the change was to or from green.

A sample workbook with the conditional formatting all set up can be accessed via this link: billv84_1.xlsm

Thank you so much Peter! Really appreciate your time (and patience!) Have a great day, wherever you are and whatever you're doing

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.