Creating Pay Bands in excel

[MWaller]

Hello

Please could someone advise how best to create the following pay bands in excel:
[TABLE="width: 412"]
<colgroup><col></colgroup><tbody>[TR]
[TD]£0-7,550[/TD]
[/TR]
[TR]
[TD]£7,551-9,925[/TD]
[/TR]
[TR]
[TD]£9,926-12,500[/TD]
[/TR]
[TR]
[TD]£12,500-17,500[/TD]
[/TR]
[TR]
[TD] £17,501-22,500[/TD]
[/TR]
[TR]
[TD]£22,501-30,000[/TD]
[/TR]
[TR]
[TD]£30,001-50,000[/TD]
[/TR]
[TR]
[TD]£50,001-100,000[/TD]
[/TR]
[TR]
[TD]£100,001-

My data is 4500 lines with pay amounts varying falling into all of the above categories

Thank you
Madeline[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Create a table eg:

[TABLE="width: 169"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Start Amount[/TD]
[TD]Band[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD]Band 1[/TD]
[/TR]
[TR]
[TD="align: right"]£7,550[/TD]
[TD]Band 2[/TD]
[/TR]
[TR]
[TD="align: right"]£9,925[/TD]
[TD]Band 3[/TD]
[/TR]
[TR]
[TD="align: right"]£12,500[/TD]
[TD]Band 4[/TD]
[/TR]
[TR]
[TD="align: right"]£17,500[/TD]
[TD]Band 5[/TD]
[/TR]
[TR]
[TD="align: right"]£22,500[/TD]
[TD]Band 6[/TD]
[/TR]
[TR]
[TD="align: right"]£30,000[/TD]
[TD]Band 7[/TD]
[/TR]
[TR]
[TD="align: right"]£50,000[/TD]
[TD]Band 8[/TD]
[/TR]
[TR]
[TD="align: right"]£100,000[/TD]
[TD]Band 9[/TD]
[/TR]
</tbody>[/TABLE]

Then you can use, eg:

=LOOKUP(D1-0.01,$A$2:$A$10,$B$2:$B$10)

where A1:B10 holds the table and D1 the lookup value.

 

[MWaller]

Hi thank you for this.
Will this work without calling the bands Band 1, 2 etc. the bands need to be called as above for example £0 - 7750.
How do I create a table?

 

[steve the fish]

Yes call them what you like. Just type them into the cells.

 

[MWaller]

Yes call them what you like. Just type them into the cells.

ok thank you. Can't get it to work - it's just returning #NAS

 

[steve the fish]

Make sure the numbers are numbers in the table. Click one of the cells with a £ sign. Look in formula bar. Do you see the £ sign? If so delete it from the cell to make it a true number.