Can I make multiple comparisons ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,

I am having multiple IF conditions in excel2003, Can I have Switch or
between functions in excel...
 
Like i am having following table -

Region TCH ErLang TRX
BLR 2 0.3 1
BLR 10 5.1 2
BLR 17 10.7 3
BLR 25 17.6 4
BLR 32 23.8 5
BLR 40 31.0 6
BLR 47 37.5 7
BLR 55 45.0 8
BLR 62 51.6 9
BLR 70 59.2 10
BLR 77 65.9 11
BLR 85 73.5 12



Now if Erlang is between 0 and 0.3 then I need TRX=1, then if ErLang is >0.3
and <5.1 then TRX=2. I hope now i make some understanding...
 
Set up a table like this somewhere on your sheet:

0.0 1
0.3 2
5.1 3
10.7 4
17.6 5
23.8 6
31.0 7
37.5 8
45.0 9
51.6 10
59.2 11
65.9 12

Let's say this occupies cells M1:N12. Then with your calculated
Erlangs in A2 (say), you can use this formula to return the number of
lines:

=VLOOKUP(A2,M$1:N$12,2)

Adjust the cell references to suit.

Hope this helps.

Pete
 
Hi,

Thanks for the solution but I have already tried this... problem is vlookup
is not giving reults as i need coz it wont give reults with "Between"
comparisons...
 
If you had a value of 2.5, say, then the formula I gave you will
return 2, or a value of 18.7 will return 5 - is this not what you
want?

Maybe you are thinking that VLOOKUP is looking for an exact match, but
I have not set the 4th parameter in the function so it does not
operate that way in my formula.

Pete
 
Back
Top