If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Is it possible?
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 |
#2
|
|||
|
|||
Is it possible?
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 |
#3
|
|||
|
|||
Is it possible?
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 |
#4
|
|||
|
|||
Is it possible?
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 |
#5
|
|||
|
|||
Is it possible?
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 |
#6
|
|||
|
|||
Is it possible?
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 |
Thread Tools | |
Display Modes | |
|
|