Is it possible?

B

Bobbie Jo

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?
 
A

Alan

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.
 
M

MartinW

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
 
S

Steel Monkey

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
 
J

JC

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?
Click to expand...

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