| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking up a value within a range of 3 letter or number codes...
Hello,
I'm trying to determine the best way to return a value or string using an if-then statement. The current formula I have looks like this: =IF(B3=ccode,"West","East") So basically, if whatever is in the cell B3 (let's say its "ABC") is also in the range named "ccode", then it returns the string "West". However, I know the sytax of this formula is incorrect because I receive a "#Name?" error. Any thoughts? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Looking up a value within a range of 3 letter or number codes...
Try this:
=IF(COUNTIF(ccode,B3),"West","East") Is that something you can work with? Post back if you have more questions. Regards, Ron Microsoft MVP - Excel "Jimmy" wrote in message ... Hello, I'm trying to determine the best way to return a value or string using an if-then statement. The current formula I have looks like this: =IF(B3=ccode,"West","East") So basically, if whatever is in the cell B3 (let's say its "ABC") is also in the range named "ccode", then it returns the string "West". However, I know the sytax of this formula is incorrect because I receive a "#Name?" error. Any thoughts? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Looking up a value within a range of 3 letter or number codes.
Ron,
Thanks for your help. It worked. I have another question though. When nested "COUNTIF" formula runs, it returns either a 1 or a 0. The 1 is returned if the cell contents are also in the range and a 0 if it is not in the range. How does the if - then formula know that a 1 is a true outcome and a 0 is a false outcome? Jimmy "Ron Coderre" wrote: Try this: =IF(COUNTIF(ccode,B3),"West","East") Is that something you can work with? Post back if you have more questions. Regards, Ron Microsoft MVP - Excel "Jimmy" wrote in message ... Hello, I'm trying to determine the best way to return a value or string using an if-then statement. The current formula I have looks like this: =IF(B3=ccode,"West","East") So basically, if whatever is in the cell B3 (let's say its "ABC") is also in the range named "ccode", then it returns the string "West". However, I know the sytax of this formula is incorrect because I receive a "#Name?" error. Any thoughts? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Looking up a value within a range of 3 letter or number codes.
Hi, Jimmy
The first part of the IF function works like this: It interprets zero (0) as FALSE and all other numbers (positive or negative) as TRUE. Examples: =IF(0,"Is true","Is false").....returns: Is false =IF(-6,"Is true","Is false").....returns: Is false =IF(32,"Is true","Is false").....returns: Is true =IF(0.0023,"Is true","Is false").....returns: Is true Does that help? Post back if you have more questions. Regards, Ron Microsoft MVP - Excel "Jimmy" wrote in message ... Ron, Thanks for your help. It worked. I have another question though. When nested "COUNTIF" formula runs, it returns either a 1 or a 0. The 1 is returned if the cell contents are also in the range and a 0 if it is not in the range. How does the if - then formula know that a 1 is a true outcome and a 0 is a false outcome? Jimmy "Ron Coderre" wrote: Try this: =IF(COUNTIF(ccode,B3),"West","East") Is that something you can work with? Post back if you have more questions. Regards, Ron Microsoft MVP - Excel "Jimmy" wrote in message ... Hello, I'm trying to determine the best way to return a value or string using an if-then statement. The current formula I have looks like this: =IF(B3=ccode,"West","East") So basically, if whatever is in the cell B3 (let's say its "ABC") is also in the range named "ccode", then it returns the string "West". However, I know the sytax of this formula is incorrect because I receive a "#Name?" error. Any thoughts? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Looking up a value within a range of 3 letter or number codes.
I'm a little confused. You say that all other numbers (positive or negative)
will display as true, yet you have below: =IF(-6,"Is true","Is false").....returns: Is false Why would this function return as false? Jimmy "Ron Coderre" wrote: Hi, Jimmy The first part of the IF function works like this: It interprets zero (0) as FALSE and all other numbers (positive or negative) as TRUE. Examples: =IF(0,"Is true","Is false").....returns: Is false =IF(-6,"Is true","Is false").....returns: Is false =IF(32,"Is true","Is false").....returns: Is true =IF(0.0023,"Is true","Is false").....returns: Is true Does that help? Post back if you have more questions. Regards, Ron Microsoft MVP - Excel "Jimmy" wrote in message ... Ron, Thanks for your help. It worked. I have another question though. When nested "COUNTIF" formula runs, it returns either a 1 or a 0. The 1 is returned if the cell contents are also in the range and a 0 if it is not in the range. How does the if - then formula know that a 1 is a true outcome and a 0 is a false outcome? Jimmy "Ron Coderre" wrote: Try this: =IF(COUNTIF(ccode,B3),"West","East") Is that something you can work with? Post back if you have more questions. Regards, Ron Microsoft MVP - Excel "Jimmy" wrote in message ... Hello, I'm trying to determine the best way to return a value or string using an if-then statement. The current formula I have looks like this: =IF(B3=ccode,"West","East") So basically, if whatever is in the cell B3 (let's say its "ABC") is also in the range named "ccode", then it returns the string "West". However, I know the sytax of this formula is incorrect because I receive a "#Name?" error. Any thoughts? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Looking up a value within a range of 3 letter or number codes.
For me, the formula =IF(-6,"Is true","Is false") returns Is true, so Ron's
description was correct, but his example wasn't. -- David Biddulph "Jimmy" wrote in message ... I'm a little confused. You say that all other numbers (positive or negative) will display as true, yet you have below: =IF(-6,"Is true","Is false").....returns: Is false Why would this function return as false? Jimmy "Ron Coderre" wrote: Hi, Jimmy The first part of the IF function works like this: It interprets zero (0) as FALSE and all other numbers (positive or negative) as TRUE. Examples: =IF(0,"Is true","Is false").....returns: Is false =IF(-6,"Is true","Is false").....returns: Is false =IF(32,"Is true","Is false").....returns: Is true =IF(0.0023,"Is true","Is false").....returns: Is true Does that help? Post back if you have more questions. Regards, Ron Microsoft MVP - Excel "Jimmy" wrote in message ... Ron, Thanks for your help. It worked. I have another question though. When nested "COUNTIF" formula runs, it returns either a 1 or a 0. The 1 is returned if the cell contents are also in the range and a 0 if it is not in the range. How does the if - then formula know that a 1 is a true outcome and a 0 is a false outcome? Jimmy "Ron Coderre" wrote: Try this: =IF(COUNTIF(ccode,B3),"West","East") Is that something you can work with? Post back if you have more questions. Regards, Ron Microsoft MVP - Excel "Jimmy" wrote in message ... Hello, I'm trying to determine the best way to return a value or string using an if-then statement. The current formula I have looks like this: =IF(B3=ccode,"West","East") So basically, if whatever is in the cell B3 (let's say its "ABC") is also in the range named "ccode", then it returns the string "West". However, I know the sytax of this formula is incorrect because I receive a "#Name?" error. Any thoughts? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Looking up a value within a range of 3 letter or number codes.
Sorry....typo!
It should be: =IF(-6,"Is true","Is false").....returns: Is true Thanks for catching that and letting me know. Regards, Ron Microsoft MVP - Excel "Jimmy" wrote in message ... I'm a little confused. You say that all other numbers (positive or negative) will display as true, yet you have below: =IF(-6,"Is true","Is false").....returns: Is false Why would this function return as false? Jimmy "Ron Coderre" wrote: Hi, Jimmy The first part of the IF function works like this: It interprets zero (0) as FALSE and all other numbers (positive or negative) as TRUE. Examples: =IF(0,"Is true","Is false").....returns: Is false =IF(-6,"Is true","Is false").....returns: Is false =IF(32,"Is true","Is false").....returns: Is true =IF(0.0023,"Is true","Is false").....returns: Is true Does that help? Post back if you have more questions. Regards, Ron Microsoft MVP - Excel "Jimmy" wrote in message ... Ron, Thanks for your help. It worked. I have another question though. When nested "COUNTIF" formula runs, it returns either a 1 or a 0. The 1 is returned if the cell contents are also in the range and a 0 if it is not in the range. How does the if - then formula know that a 1 is a true outcome and a 0 is a false outcome? Jimmy "Ron Coderre" wrote: Try this: =IF(COUNTIF(ccode,B3),"West","East") Is that something you can work with? Post back if you have more questions. Regards, Ron Microsoft MVP - Excel "Jimmy" wrote in message ... Hello, I'm trying to determine the best way to return a value or string using an if-then statement. The current formula I have looks like this: =IF(B3=ccode,"West","East") So basically, if whatever is in the cell B3 (let's say its "ABC") is also in the range named "ccode", then it returns the string "West". However, I know the sytax of this formula is incorrect because I receive a "#Name?" error. Any thoughts? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding a number to a letter of the alphabet to get a letter | Excel Worksheet Functions | |||
| Vlookup using letter and numeric codes | Excel Discussion (Misc queries) | |||
| count a number range and a letter in a cell | Excel Worksheet Functions | |||
| change headers from letter to number/number to letter | Excel Worksheet Functions | |||
| column header changed from letter to number, how return to letter | Excel Discussion (Misc queries) | |||
Hybrid Mode
