MikeCollins999
New Member
Hi All,
Really sorry to repeat this post - I posted a while ago and got an answer fro Hui but the suggested solution did not work as expected and whilst I posted back, the thread seems to have got lost in the long list of "calls for help"...
So, I’m trying to help a friend automate the process of placing primary school children in reading bands. The school works out the child’s reading age (rightly or wrongly) by taking their age from the start of the year and counting the months so a student who is 4 years and 3 months old would have a reading age of 4:3. A student who is 4 years and 11 months old would have a reading age of 4:11.
Once the child’s expected reading age is determined, their actually reading age is assessed. Again this is entered in the format Y:M.
I now need to look up the Band (colour) that the child falls into based on the actual reading age, where the bands look like this:
I can split these values so they represent the upper-limit or split them so the lower limit is in one column and the upper is in the next column. Essentially, the structure of the band table is flexible – all I want to do is return the colour – respecting the fact that 4:12 is greater than 4:7.
I have attached an annotated sample - on the first worksheet is an outline of the problem, on the second worksheet is Hui's proposed solution showing why the formula doesn't work and a suggestion of how the problem might be resolved (although I do not know how to implement it
)
Any help or pointers would be ace.
Many thanks,
Mike
Original Post and sample file: http://forum.chandoo.org/threads/doing-lookups-with-complex-data-or-two-values.31243/
Really sorry to repeat this post - I posted a while ago and got an answer fro Hui but the suggested solution did not work as expected and whilst I posted back, the thread seems to have got lost in the long list of "calls for help"...
So, I’m trying to help a friend automate the process of placing primary school children in reading bands. The school works out the child’s reading age (rightly or wrongly) by taking their age from the start of the year and counting the months so a student who is 4 years and 3 months old would have a reading age of 4:3. A student who is 4 years and 11 months old would have a reading age of 4:11.
Once the child’s expected reading age is determined, their actually reading age is assessed. Again this is entered in the format Y:M.
I now need to look up the Band (colour) that the child falls into based on the actual reading age, where the bands look like this:
Code:
[$D] [$E]
< 4 LILIC
4:0 – 4:3 PINK
4:4 – 4:6 RED
4:7 – 4:.12 YELLOW
5:0 – 5:5 BLUEI can split these values so they represent the upper-limit or split them so the lower limit is in one column and the upper is in the next column. Essentially, the structure of the band table is flexible – all I want to do is return the colour – respecting the fact that 4:12 is greater than 4:7.
I have attached an annotated sample - on the first worksheet is an outline of the problem, on the second worksheet is Hui's proposed solution showing why the formula doesn't work and a suggestion of how the problem might be resolved (although I do not know how to implement it
)Any help or pointers would be ace.
Many thanks,
Mike
Original Post and sample file: http://forum.chandoo.org/threads/doing-lookups-with-complex-data-or-two-values.31243/
Last edited by a moderator:
