But I have a list of number where 6 equals 500, 7 equals 600, 8 equals 700,
and so one. (Probably about 20 of them) So I came up with this:

=If(b1=6,"500")

And it works. But the problem is that B1 will change, it could be 7 or 8.
And I want that number to correspond with the correct value. I've tried
separating it with a colon and I've put them in parentheses. And it's not
working.

Any ideas?
--
Bobbie Jo

You can nest the IF's like
=IF(B1=6,500,IF(B6=7,600)) etc
but you can only nest seven of them. The best way to do this I think is to
use a VLOOKUP table, have a look at VLOOKUP in help,
Regards,
Alan.
"Bobbie Jo" wrote in message
...
But I have a list of number where 6 equals 500, 7 equals 600, 8 equals
700,
and so one. (Probably about 20 of them) So I came up with this:

=If(b1=6,"500")

And it works. But the problem is that B1 will change, it could be 7 or 8.
And I want that number to correspond with the correct value. I've tried
separating it with a colon and I've put them in parentheses. And it's not
working.

Any ideas?
--
Bobbie Jo

Hi Bobbie Jo,

I'm sure someone will come up with a simpler way but one
that will work is this

=IF(B1=6,500,"")&IF(B1=7,600,"")&IF(B1=8,700,"") etc. etc.

HTH
Martin

If you have about 20 different values i would put them all in a list and
then use a vlookup

If the values you want returned are in E:F ie


E F
5 600
6 700
7 800

In cell A2 you would type =vlookup(B2,E:F,2, FALSE) and then in cell b2
you would type in the number ie 6. This would return 700 in cell A2

Change this around to suit your needs

Let me know how you go


--
Steel Monkey
------------------------------------------------------------------------
Steel Monkey's Profile: http://www.excelforum.com/member.php...o&userid=29051
View this thread: http://www.excelforum.com/showthread...hreadid=572053

Thanks everyone! I'm going to give each of your ideas a shot. Appreciate it!

--
Bobbie Jo


"Steel Monkey" wrote:


If you have about 20 different values i would put them all in a list and
then use a vlookup

If the values you want returned are in E:F ie


E F
5 600
6 700
7 800

In cell A2 you would type =vlookup(B2,E:F,2, FALSE) and then in cell b2
you would type in the number ie 6. This would return 700 in cell A2

Change this around to suit your needs

Let me know how you go


--
Steel Monkey
------------------------------------------------------------------------
Steel Monkey's Profile: http://www.excelforum.com/member.php...o&userid=29051
View this thread: http://www.excelforum.com/showthread...hreadid=572053

On Tue, 15 Aug 2006 18:40:02 -0700, Bobbie Jo
wrote:

But I have a list of number where 6 equals 500, 7 equals 600, 8 equals 700,
and so one. (Probably about 20 of them) So I came up with this:

=If(b1=6,"500")

And it works. But the problem is that B1 will change, it could be 7 or 8.
And I want that number to correspond with the correct value. I've tried
separating it with a colon and I've put them in parentheses. And it's not
working.

Any ideas?

How about using =100*B1-100 or =100*(B1-1)?
--

Cheers . . . JC