Conditional Formatting Query

timgayler

New Member
Joined
Feb 7, 2005
Messages
2
Dear all, I wonder if anyone can help
I'm setting up a grading spreadsheet. This will use a UK levelling system where the grades (in ascending order) are...
1c 1b 1a 2c 2b 2a --> and so on up to 7a

I have the spreadsheet using a lookup table to convert the inputted grades into numbers - and then am using formulae to average, and then another lookup table to give an average level achieved so far.

However, I'd really like it if I could then compare the average level (eg 3a) with the pupils target level (eg 2a), and then format the average level to show that it is higher than the target, lower etc.

Conditonal formatting seems to be the way to do it, however I cannot get this to work at present on the 2 grades. I can convert both to numerical scores and then use conditonal formatting - but I would prefer to hide these columns and just leave my users with the scores

Any help greatfully received
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
I have assumes that the grades (lookup) table is in order from A2-A10. (ie cell A2 is 1c, A3 is 1b, etc).
I have also assumed that the grade is in cell B2, and the target is in C2.

In conditional formatting, choose "formula is" and then this formula needs to be pasted into the box:

=MATCH(C2,A2:A10,0)<MATCH(B2,A2:A10,0)

Then attach a format.
 
Upvote 0
Hi Sweaters_vest_roc
Here's the html shot
KS3 Assessment Record 2004-05 (blank).xls
LMNOPQRSTUVWXYZAAABACADAEAFAGAHAI
26
27YearTargetNumberTopic1Topic2Topic3Topic4Topic5Topic6Topic1NumberTopic2NumberTopic3NumberTopic4NumberTopic5NumberTopic6NumberNumberWithSomethingInAllTotalAllTotalDividedByNumberWithSomethingRoundedValueOverallAverageGrade
28YearTarget
297a7a7a7b7b6a5b          6a
Sheet1


I'm fairly sure there's a number of ways to make this more elegant - I'm new at what I think of a 'more complex spreadsheets'

Tactps - I'll try your solution later

Thanks to you both - any more thoughts very welcome
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top