Band / Category assigning

[tropiqz]

Hi there,

I am wondering what the easiest way I can band values which are the closest to the actual values. Take the below example:

Here are my bands:

Chargeband	Exc VAT (pence per min)
1	4.5
2	4.5
3	4.5
4	13.5
5	13.5
6	13.5
7	13.5
8	36
9	36
10	36
11	36
12	67.5
13	67.5
14	90

<colgroup><col><col></colgroup><tbody>
</tbody>

And here are some of the values I want to produce a chargeband for:

0.7439
0.264
0.264
0.1501
0.7439
0.3344
0.8876
0.3344
0.2137
0.411 ​

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

e.g. value 0.7439 would fall into band 14.

I assume the best way would be through a VLOOKUP which will use < & > to make them fall into the appropriate band, is this the best way of doing it? Could anyone help with the best method?

Thanks in advance

 

[steve the fish]

You ought to explain this a bit better as it is a little confusing. How would you ever hit most of these bands the way you describe it? How would the formula ever produce band 2, 3, 5, 6 ,7 for example?

 

[tropiqz]

Sorry, it is confusing. Essentially I need to band them to the nearest one, disregarding the fact some of the bands have the same Exc VAT. I should have excluded the duplicated.

 

[steve the fish]

Ok lets try this and see if it produces what you want:

=MATCH(TRUE,INDEX($B$1:$B$14>D1*100,0),0)

 

[steve the fish]

Maybe safer to use the index too:

=INDEX($A$1:$A$14,MATCH(TRUE,INDEX($B$1:$B$14>D1*100,0),0))

 

[tropiqz]

Maybe safer to use the index too:

=INDEX($A$1:$A$14,MATCH(TRUE,INDEX($B$1:$B$14>D1*100,0),0))

That's worked perfectly Steve, you're a star. Many thanks