Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting
Can I use conditional formatting to compare text values in Excel?
I want to compare student grade performance over time and want to show if a student's grade has increased or decreased e.g. A - B would be a decrease, B - A would be an increase. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting
Can you give more information on the layout of your sheet? Because the
formula could be different depending on the layout. Let me explain: Is your sheet with one row per student and different columns giving different periods? Or is your sheet with one row per student per period? Meaning you could have several rows with the same student but for different periods? If this is the case then are your data sorted as per period or not? "staffrmj" wrote: Can I use conditional formatting to compare text values in Excel? I want to compare student grade performance over time and want to show if a student's grade has increased or decreased e.g. A - B would be a decrease, B - A would be an increase. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting
On Jun 8, 12:34*pm, DKS wrote:
Can you give more information on the layout of your sheet? *Because the formula could be different depending on the layout. Let me explain: Is your sheet with one row per student and different columns giving different periods? Or is your sheet with one row per student per period? *Meaning you could have several rows with the same student but for different periods? *If this is the case then are your data sorted as per period or not? "staffrmj" wrote: Can I use conditional formatting to compare text values in Excel? I want to compare student grade performance over time and want to show if a student's grade has increased or decreased e.g. A - B would be a decrease, B - A would be an increase. Thanks- Hide quoted text - - Show quoted text - The sheet has one row per student and records performance in the different terms in different columns. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting
Let's say first student is in A2 with grades in B2:K2
Select C2:K2 Use Format | Conditional Formatting For improving grade use Formula Is: C2<B2 and make green For lowering grade use Formula IS: C2B2 and make red Note the logic in Excel is the opposite of what is in your head because for Excel "A" is less than "B" in the sense that it has a lower ASCII value. Works with single grades (will not detect A+ differs from A) - that would need a more complex formula best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "staffrmj" wrote in message ... Can I use conditional formatting to compare text values in Excel? I want to compare student grade performance over time and want to show if a student's grade has increased or decreased e.g. A - B would be a decrease, B - A would be an increase. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting
On Jun 8, 1:19*pm, "Bernard Liengme"
wrote: Let's say first student is in A2 with grades in B2:K2 Select C2:K2 Use Format | Conditional Formatting For improving grade use Formula Is: C2<B2 and make green For lowering grade use Formula IS: C2B2 and make red Note the logic in Excel is the opposite of what is in your head because for Excel "A" is less than "B" in the sense that it has a lower ASCII value. Works with single grades (will not detect A+ differs from A) - that would need a more complex formula best wishes -- Bernard Liengme Microsoft Excel MVPhttp://people.stfx.ca/bliengme "staffrmj" wrote in message ... Can I use conditional formatting to compare text values in Excel? I want to compare student grade performance over time and want to show if a student's grade has increased or decreased e.g. A - B would be a decrease, B - A would be an increase. Thanks- Hide quoted text - - Show quoted text - I do need to differentiate between A and A+ - any ideas gratefully received!! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting
Hi,
One way would be to create a grade table in an out-of-the-way place on your sheet. The table would assign a number to each grade. eg: AA.........AB (Row refs) A+..........1 A............2 A-..........3 B+..........4 B.............5 B-...........6 C+..........7 C............8 etc. Lets say your table is in AA1:AB20 Then use VLOOKUP in your conditional formatting. Copying from satffrmj: Let's say first student is in A2 with grades in B2:K2 Select C2:K2 Use Format | Conditional Formatting For improving grade use Formula Is: =VLOOKUP(C2,$AA$1:$AB$20,2,0)VLOOKUP(B2,$AA$1:$AB $20,2,0) Make format green For lowering grade use Formula Is: =VLOOKUP(C2,$AA$1:$AB$20,2,0)<VLOOKUP(B2,$AA$1:$AB $20,2,0) Make format red Regards - Dave. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting
Oops - mixed up the "<" and "" thingies.
For improving grade use Formula Is: =VLOOKUP(C2,$AA$1:$AB$20,2,0)<VLOOKUP(B2,$AA$1:$AB $20,2,0) Make format green For lowering grade use Formula Is: =VLOOKUP(C2,$AA$1:$AB$20,2,0)VLOOKUP(B2,$AA$1:$AB $20,2,0) Make format red Dave. "Dave" wrote: Hi, One way would be to create a grade table in an out-of-the-way place on your sheet. The table would assign a number to each grade. eg: AA.........AB (Row refs) A+..........1 A............2 A-..........3 B+..........4 B.............5 B-...........6 C+..........7 C............8 etc. Lets say your table is in AA1:AB20 Then use VLOOKUP in your conditional formatting. Copying from satffrmj: Let's say first student is in A2 with grades in B2:K2 Select C2:K2 Use Format | Conditional Formatting For improving grade use Formula Is: =VLOOKUP(C2,$AA$1:$AB$20,2,0)VLOOKUP(B2,$AA$1:$AB $20,2,0) Make format green For lowering grade use Formula Is: =VLOOKUP(C2,$AA$1:$AB$20,2,0)<VLOOKUP(B2,$AA$1:$AB $20,2,0) Make format red Regards - Dave. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting
Hi,
Here is what you need: Create a range in the spreadsheet that contains all the letter grades in order: A+ A A- B+ B B- C+ C C- D+ D D- F+ F F- You may not need A+ and F-. Suppose this is in the range L1:L15. Assume your grades start in B2 and extend to the right say to J2. Highlight the range C2:J2 (skipping B2) and choose Format, Conditional Formatting, Formula is (from the first drop down). Enter the formula: =MATCH(C2,$L$1:$L$15,0)<MATCH(B2,$L$1:$L$15,0) set a color and click Add. Choose Formula is and enter the second formula: =MATCH(C2,$L$1:$L$15,0)MATCH(B2,$L$1:$L$15,0) set a color. You can make the formulas shorter by defining a range name, call it F for the range L1:L15, then your formulas become: =MATCH(C2,F,0)<MATCH(B2,F,0) and =MATCH(C2,F,0)MATCH(B2,F,0) If you want to keep the range L1:L15 reference on a different sheet than the conditionally formatted cell you will need to use the range name approach mentioned above. (When L1:L15 is selected click in the Name Box and type the name you want to use.) If you don't want a range in the spreadsheet to be used at all than choose the Insert, Name, Define command and enter a name in the first box then in the Refers to box enter: ={"A+";"A";"A-";"B+";"B";"B-";"C+";"C";"C-";"D+";"D";"D-";"F+";"F";"F-"} Cheers, Shane Devenshire Microsoft Excel MVP Join http://setiathome.berkeley.edu/ and download a free screensaver and help search for life beyond earth. "staffrmj" wrote in message ... Can I use conditional formatting to compare text values in Excel? I want to compare student grade performance over time and want to show if a student's grade has increased or decreased e.g. A - B would be a decrease, B - A would be an increase. Thanks |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting
On Jun 8, 7:30*pm, "Shane Devenshire"
wrote: Hi, Here is what you need: Create a range in the spreadsheet that contains all the letter grades in order: A+ A A- B+ B B- C+ C C- D+ D D- F+ F F- You may not need A+ and F-. *Suppose this is in the range L1:L15. Assume your grades start in B2 and extend to the right *say to J2. Highlight the range C2:J2 (skipping B2) and choose Format, Conditional Formatting, Formula is (from the first drop down). *Enter the formula: =MATCH(C2,$L$1:$L$15,0)<MATCH(B2,$L$1:$L$15,0) set a color and click Add. *Choose Formula is and enter the second formula: =MATCH(C2,$L$1:$L$15,0)MATCH(B2,$L$1:$L$15,0) set a color. You can make the formulas shorter by defining a range name, call it F for the range L1:L15, then your formulas become: =MATCH(C2,F,0)<MATCH(B2,F,0) and =MATCH(C2,F,0)MATCH(B2,F,0) If you want to keep the range L1:L15 reference on a different sheet than the conditionally formatted cell you will need to use the range name approach mentioned above. *(When L1:L15 is selected click in the Name Box and type the name you want to use.) If you don't want a range in the spreadsheet to be used at all than choose the Insert, Name, Define command and enter a name in the first box then in the Refers to box enter: ={"A+";"A";"A-";"B+";"B";"B-";"C+";"C";"C-";"D+";"D";"D-";"F+";"F";"F-"} Cheers, Shane Devenshire Microsoft Excel MVP Joinhttp://setiathome.berkeley.edu/and download a free screensaver and help search for life beyond earth. "staffrmj" wrote in message ... Can I use conditional formatting to compare text values in Excel? I want to compare student grade performance over time and want to show if a student's grade has increased or decreased e.g. A - B would be a decrease, B - A would be an increase. Thanks- Hide quoted text - - Show quoted text - Thanks a lot - it worked a treat! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |