Hi,
I'm doing some work with test data and I'd like to have excel indicate via conditional formatting how a student is doing based on their age and test score when compared to a normal range.
The conditional formatting is applied to the scores entered in the Data sheet.
Desired formatting:
Each student is given an age code based on their date of birth and date of test administration.
The age code is calculated by a difference in years between birth and test administration with a concatenation of a character based on month difference. The month code is as follows:
(0 months = A, 1 month = B, 2 months = C, ... 11 months = M)
This age code allows me to use VLOOKUP on one column instead of having to match two columns.
e.g.
Below is the excel formula used to create age codes.
C2 contains student DOB
L1 contains test admin date
I use VLOOKUP to acquire the expected low and high values from a different worksheet (called Raw) from a table called raw_range.
Formula to obtain lower bound:
Formula to obtain upper bound:
Given this, I'd like to employ a conditional format that has the above properties without having to place more values in cells.
I've tried this code in the conditional format with formula, however excel gives an error saying the formula has an error:
In this formula I'm testing to see if the value in L2 (the entered data) is less than the lower bound
I've attached an example workbook.
I'm doing some work with test data and I'd like to have excel indicate via conditional formatting how a student is doing based on their age and test score when compared to a normal range.
The conditional formatting is applied to the scores entered in the Data sheet.
Desired formatting:
- If a student's test score is above the expected range - fill cell light green
- If a student's test score is below the expected range - fill cell light red
- If a student's test score is within the expected range (inclusive of upper & lower bound) - fill cell light yellow
- If a student has no test score (= "") - fill cell grey
Each student is given an age code based on their date of birth and date of test administration.
The age code is calculated by a difference in years between birth and test administration with a concatenation of a character based on month difference. The month code is as follows:
(0 months = A, 1 month = B, 2 months = C, ... 11 months = M)
This age code allows me to use VLOOKUP on one column instead of having to match two columns.
e.g.
Code:
Date of Test = 01/02/2014
Student Date of Birth = 01/03/2000
Age Code = INT(difference in years) & CHAR(difference in months)
Hence, this students age code is: 14BC2 contains student DOB
L1 contains test admin date
Code:
=(YEAR((DATEVALUE(TEXT(L$1,"yyyy-mm-dd"))-DATEVALUE(TEXT($C2,"yyyy-mm-dd")))) -1900) & CHAR((MONTH((DATEVALUE(TEXT(L$1,"yyyy-mm-dd"))-DATEVALUE(TEXT($C2,"yyyy-mm-dd")))) -1) + 65)I use VLOOKUP to acquire the expected low and high values from a different worksheet (called Raw) from a table called raw_range.
Formula to obtain lower bound:
Code:
=VLOOKUP((YEAR((DATEVALUE(TEXT(L$1,"yyyy-mm-dd"))-DATEVALUE(TEXT($C2,"yyyy-mm-dd")))) -1900) & CHAR((MONTH((DATEVALUE(TEXT(L$1,"yyyy-mm-dd"))-DATEVALUE(TEXT($C2,"yyyy-mm-dd")))) -1) + 65), raw_range[#All], 2, FALSE)
Code:
=VLOOKUP((YEAR((DATEVALUE(TEXT(L$1,"yyyy-mm-dd"))-DATEVALUE(TEXT($C2,"yyyy-mm-dd")))) -1900) & CHAR((MONTH((DATEVALUE(TEXT(L$1,"yyyy-mm-dd"))-DATEVALUE(TEXT($C2,"yyyy-mm-dd")))) -1) + 65), raw_range[#All], 3, FALSE)Given this, I'd like to employ a conditional format that has the above properties without having to place more values in cells.
I've tried this code in the conditional format with formula, however excel gives an error saying the formula has an error:
Code:
=IF(L2 < (VLOOKUP((YEAR((DATEVALUE(TEXT(L$1,"yyyy-mm-dd"))-DATEVALUE(TEXT($C2,"yyyy-mm-dd")))) -1900) & CHAR((MONTH((DATEVALUE(TEXT(L$1,"yyyy-mm-dd"))-DATEVALUE(TEXT($C2,"yyyy-mm-dd")))) -1) + 65), raw_range[#All], 2, FALSE)), "true", "false")I've attached an example workbook.
