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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
tim.

kind of hard to understand what you mean here. any chance you could show us your sheet via htmlmaker?

ben.
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
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.
 

timgayler

New Member
Joined
Feb 7, 2005
Messages
2
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
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
HTML is not working at the moment. Can you give us a graphic?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,083
Messages
5,599,643
Members
414,326
Latest member
kfg1287

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
Top