Less than and Greater than

[neilbexton]

Hi

Im having trouble with a formula. Please help.

In cell H5 I need a formula that will work out the following.

If H4 = 0-1000 it must read £10
If H4 = 1001-1800 it must read £5
If H4 = 1801+ it must read £0

Thanks

[Special-K]

=lookup(h4,{0,1001,1801},{10,5,0})

[TMS]

Please Login or Register  to view this content.

Regards, TMS

[neilbexton]

Legend

Thanks

[neilbexton]

Ok, That seemed a little easy for you. Lol

Perhaps I could simplify the whole thing im trying to do.

Cell B1 is either going to be under 1000, between 1001 and 1800, or over 1801

If B1 is under 1000 and
If H4 = 0-1000 it must read £17
If H4 = 1001-1800 it must read £10
If H4 = 1801+ it must read £0

If B1 is Between 1001 and 1800 and
If H4 = 0-1000 it must read £10
If H4 = 1001-1800 it must read £5
If H4 = 1801+ it must read £0

If B1 is over 1801 and
If H4 = 0-1000 it must read £0
If H4 = 1001-1800 it must read £0
If H4 = 1801+ it must read £0

Hope that's clear.

Thanks

[Special-K]

Try

=IF(B1<1000,IF(AND(H4<=1000,H4>=0),17,IF(AND(H4<=1800,H4>=1001),10)),IF(B1<=1800,IF(AND(H4>=1001,H4<=1800),5),0))

[neilbexton]

That returns a FALSE answer.

[boopathiraja]

tRY

Please Login or Register  to view this content.

[Special-K]

That returns a FALSE answer.

Try this (I updated the formula, you may have entered my earlier one which wasnt working. This one provides a value rather than FALSE)

=IF(B1<1000,IF(AND(H4<=1000,H4>=0),17,IF(AND(H4<=1800,H4>=1001),10)),IF(B1<=1800,IF(AND(H4>=1001,H4<=1800),5),0))

[TMS]

Maybe:

Please Login or Register  to view this content.

Regards, TMS

[neilbexton]

This works on all except if the B1 (B2 as you have put) is 999 or less. How would I correct it?

[boopathiraja]

Try

Please Login or Register  to view this content.

[TMS]

And the other solutions?