Using the IF formula

[jcparham]

I am trying to use the if statement to the following pruposes:

if A1 is greater than 13000 but less than 14000 then £25 is entered
into the cell. however:
if A1 is greater than 14000 but less than 15000 then £35 is entered
into the cell. however:
if A1 is greater than 15000 but less than 16000 then £50 is entered
into the cell. and so on

Has anyone anu ideas how to acheive this??

Thanks

John

 

[Richard Buttrey]

I am trying to use the if statement to the following pruposes:

if A1 is greater than 13000 but less than 14000 then £25 is entered
into the cell. however:
if A1 is greater than 14000 but less than 15000 then £35 is entered
into the cell. however:
if A1 is greater than 15000 but less than 16000 then £50 is entered
into the cell. and so on

Has anyone anu ideas how to acheive this??

Thanks

John

What do you mean by 'and so on'?
How should the result change by band? It's not obvious from the above
whether there is a direct relationship since the first jump is £10,
the second £15.

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Pete_UK]

When you say "and so on", how many more conditions are you likely to
want to set up? And what happens if A1 is less than or equal to 13,000,
or when A1 = 14,000, or 15,000, or 16,000?

I think the easiest way to do this is to set up a little table
somewhere along the lines of:

0 0
13000 25
14000 35
15000 50

and so on, listing your criteria and the amounts linked to them. Let's
imagine this occupies cells L1:M4. In B1 you could have a formula:

=VLOOKUP(A1,L$1:M$4,2)

which will give you what you want. I've made certain assumptions - eg
the range is from 13000 to 13999 to give £25, 14000 to 14999 to give
£35 etc.

Hope this helps.

Pete

 

[John Turnbull]

=IF(A1>16000,0,IF(A1>15000,50,IF(A1>14000,35,IF(A1>13000,25,0))))