Excel - use If Function with 'between' range? 2

[dsmith910]

Hi

I need to set up a function which says:

If Cell A1 = between 30 and 26 then 5 else 0

I have a total of 4 of these ranges and intend putting the answers in 4 different cells, then adding the cells to give a total. Figure there's probably a better way of doing this too - perhaps by doing a vlookup? but I'd still need to know how to express the 'between' bit of the expression, plus I can't figure out which bit goes where on the vlookup screen.

Help!

Doreen

 

[xlbo]

for this:
If Cell A1 = between 30 and 26 then 5 else 0
you would use
=IF(AND(A1>26,A1<30),5,0)

Being as you have multiple ranges (which hopefully don't overlap), you can make use of how excel evaluates IF statements ie If it finds a &quot;TRUE&quot; answer, the rest of the equation is ignored so:

for these ranges:
26-30 = 5
20-25 = 4
16-20 = 3
10-15 = 2
the following formula will work
=IF(A1>30,&quot;Outta Range&quot;,if(A1>26,5,if(A1>20,4,if(A1>16,3,if(A1>10,2,0)))))

HTH
Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[likelylad]

Is this what you are looking for?????
IF(AND(A1>=26,A1<=30),5,0)

 

[dsmith910]

Hi Geoff

Your solution works just great - can't believe how fast you managed to answer.
Multitudinous thanks.

Doreen

Hi Likelylad

Many thanks for the help - but this only seemed to work for me if I only have two parameters, once I added in more it didn't work properly.

Doreen

 

[schat]

hi doreen
assuming your 4 ranges are thus:
26-30 = 5
20-25 = 4
15-19 = 3
10-14 = 2
here's a vlookup solution as an alternative to what xlbo suggested:
=VLOOKUP(A2,{0,0;10,2;15,3;20,4;26,5},2)
where A2 is the cell to be evaluated.
hth
schat

 

[dsmith910]

Hi schat
Have just tried this solution too on the basis that sometimes one things works better than another - it also works great. Many thanks for the help its really appreciated. Maybe one of these days I'll have learned enough to help others too.

Thanks again

Doreen