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:
[TABLE="width: 258"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Chargeband[/TD]
[TD]Exc VAT (pence per min)[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4.5[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4.5[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]4.5[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]13.5[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]13.5[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]13.5[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]13.5[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]67.5[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]67.5[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]90
[/TD]
[/TR]
</tbody>[/TABLE]

And here are some of the values I want to produce a chargeband for:
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]0.7439[/TD]
[/TR]
[TR]
[TD="align: right"]0.264[/TD]
[/TR]
[TR]
[TD="align: right"]0.264[/TD]
[/TR]
[TR]
[TD="align: right"]0.1501[/TD]
[/TR]
[TR]
[TD="align: right"]0.7439[/TD]
[/TR]
[TR]
[TD="align: right"]0.3344[/TD]
[/TR]
[TR]
[TD="align: right"]0.8876[/TD]
[/TR]
[TR]
[TD="align: right"]0.3344[/TD]
[/TR]
[TR]
[TD="align: right"]0.2137[/TD]
[/TR]
[TR]
[TD="align: right"]0.411

​

[/TD]
[/TR]
</tbody>[/TABLE]
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