Putting Cells into Bands then giving a figure from which band that cell is in

[rexy69]

ok so this is slightly more complicated than the title, ill attach a dummy workbook and try to explain.

all you will really need from this is the "To Target column" the "Band Column" and "Score Column" and the banding section on the right hand side

ok so the bands go from 0, 1 , 2 and 3 depending on what the "To Target column" figure is

---


ok so the first problem (probably the easier one)

as you can see the first line is 100 "to target" which means that it is band 3 as band 3 is 95+, how do i make it so excel will check to see what the figure is in "to target cell" and change the band column to the right banding number?

you may be able to guess what im about to ask next.

---

depending on the banding there is a column next to the banding parameters e.g 0-79.9 - the column next to is 0, therefore putting a 0 in the score column
or e.g 95+ the column next to it is 81 therefore putting 81 in the score column.

so they are the two things i need help with.
im hoping this is possible, if i haven't explained it well enough let me know and i will try again

Many Thanks

p.s i have just realised the left table is not inline with the right table, the rows matter as the scores change

Thanks again

[kvsrinivasamurthy]

Pl see attached file.