another excel formula required

Percybridge

I want a certain cell to have a value as follows
If the value of B6 is
between the value in B1 and B2, B6 should have a value of £1000
between B2 and B3, B6 should have a value of £2000
over B3, its value should be £3,000

victor2

=IF(AND(B6>=B1,B6<=B2),1000,IF(AND(B6>=B3,B6<=B4),2000,IF(B6>=B5,3000,0)))

The = signs assume they are inclusive ranges.
However, if the ranges follow on from each other, a vlookup would be simpler....
=VLOOKUP(B6,D8:E10,2)

where D8, D9 and D10 contain the starting values of each "between" pair and E8, E9 and E10 contain 1000, 2000 and 3000 respectively.
You will get an N/A error if the value being looked up is below the first entry, but an additional entry starting at 0 should handle that.

Edit:
Forget the first formula as your ranges obviously do follow on. Vlookup is the answer.

bap98189

Try this.

=IF(AND(B5>=B1,B5<B2)=TRUE,1000,IF(AND(B5>=B2,B5<B3)=TRUE,2000,IF(B5>B3,3000,"Error")))

You didn't specify what you want to happen if B5 = B1, B2 or B3, but you can change the < or > to =< or >= in the formula if you want to alter this.

Percybridge

many thanks for taking the time to reply.

bod1467

How can B6 have a value to check against AND a formula?