IF Statement Question

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

Guest

Hello,

I have the following statement that does what I want

=IF(G10=3,-3,IF(G10=4,-2,IF(G10=5,-1,IF(G10=9,1,IF(G10=10,2,IF(G10=11,3,0))))))

If the random number is between 3 and 11, a value is generated, my problem is if the number is either a 2 or a 12, I want to get another number

For example

Random Number List 1 List 2
2 --- 8
7 0 n/a
12 --- 4

So if a number is between 3-11, read corresponding value from list 1, 2 or 12 read from list 2.

Can I do this in one big if statements.

Thanks in advance

Doug
 
=IF(G10=2,8,IF(OR(G10=3,G10=4,G10=5),G10-6,IF(OR(G10=9,G10=10,G10=11,G10=12),G10
-8,0)))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Doug V said:
Hello,

I have the following statement that does what I want

=IF(G10=3,-3,IF(G10=4,-2,IF(G10=5,-1,IF(G10=9,1,IF(G10=10,2,IF(G10=11,3,0))))))

If the random number is between 3 and 11, a value is generated, my problem is
Click to expand...
if the number is either a 2 or a 12, I want to get another number
 
Did you try the vlookup function?
create two columns, one with 1 to say 20
and one with the corresponding numbers.
give the area a name

if you call the two columns for instance myrange
the formula would be =VLOOKUP(E2,myrange,2)
E2 is your random number...
2 is indicating to look in the second column.

Regards
Bill K
Doug V said:
Hello,

I have the following statement that does what I want

=IF(G10=3,-3,IF(G10=4,-2,IF(G10=5,-1,IF(G10=9,1,IF(G10=10,2,IF(G10=11,3,0)))
)))

If the random number is between 3 and 11, a value is generated, my problem
Click to expand...
is if the number is either a 2 or a 12, I want to get another number
 
Back
Top