Strudel_Girl
New Member
- Joined
- Aug 31, 2018
- Messages
- 1
I would be so grateful for your help. I have been searching for days and am at a loss as to how to even find the answer.
I need to determine whether a group of Primary School children are Below, At or Above Level based upon their score and where that lies in relation to their age. I can either have it return a result such as Below, At or Above or I could use conditional formatting to present where the children sit. I have calculated their age as at the test date (and used MROUND to round to nearest 0.5) but what I need is a way for Excel to find the age in a table of results and then assess their score based on that age.
The following table is an example of the criteria (my ages go from 5-11yrs old):
<tbody>
</tbody>
In my lay thinking, I am looking for a way to say (referencing below table), if their age at the time of the test is 6.5yrs (A2) and their score is between 3 (B2) and 7 (C2), they are either Normal (or conditionally formatted Yellow) or if they are 7 years old (A3) at the time of the test and receive 3 (D3) they are Critical (or conditionally formatted Red).
<tbody>
</tbody>
I'm not sure if it would be a 2 level VLOOKUP or what?? I have researched that though and it doesn't seem to fit, nor does Index & Match. Would be super grateful for any advice, a suggestion of a video to watch or even just what terminology I should search for.
Thanks in advance for your help!
I need to determine whether a group of Primary School children are Below, At or Above Level based upon their score and where that lies in relation to their age. I can either have it return a result such as Below, At or Above or I could use conditional formatting to present where the children sit. I have calculated their age as at the test date (and used MROUND to round to nearest 0.5) but what I need is a way for Excel to find the age in a table of results and then assess their score based on that age.
The following table is an example of the criteria (my ages go from 5-11yrs old):
Age | Normal Range | Critical |
6.5 | 3 to 7 | 2 |
7 | 5 to 11 | 3 |
7.5 | 7 to 15 | 5 |
<tbody>
</tbody>
In my lay thinking, I am looking for a way to say (referencing below table), if their age at the time of the test is 6.5yrs (A2) and their score is between 3 (B2) and 7 (C2), they are either Normal (or conditionally formatted Yellow) or if they are 7 years old (A3) at the time of the test and receive 3 (D3) they are Critical (or conditionally formatted Red).
Age | Norm. Low | Norm. High | Critical |
6.5 | 3 | 7 | 2 |
7 | 5 | 11 | 3 |
7.5 | 7 | 15 | 5 |
<tbody>
</tbody>
I'm not sure if it would be a 2 level VLOOKUP or what?? I have researched that though and it doesn't seem to fit, nor does Index & Match. Would be super grateful for any advice, a suggestion of a video to watch or even just what terminology I should search for.
Thanks in advance for your help!