Creating Pay Bands in excel

[MWaller]

Hello

Please could someone advise how best to create the following pay bands in excel:

£0-7,550
£7,551-9,925
£9,926-12,500
£12,500-17,500
£17,501-22,500
£22,501-30,000
£30,001-50,000
£50,001-100,000
£100,001- My data is 4500 lines with pay amounts varying falling into all of the above categories Thank you Madeline

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

 

[steve the fish]

Create a table eg:

Start Amount	Band
0	Band 1
£7,550	Band 2
£9,925	Band 3
£12,500	Band 4
£17,500	Band 5
£22,500	Band 6
£30,000	Band 7
£50,000	Band 8
£100,000	Band 9

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

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.