Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number Transformation
By entering a numerical value in a cell A1, I would like to see another value
displayed in cell B1. For instance, a 1 in A1 would display 11 in B1. 1 = 11 2 = 8 3 = 7 Else = 0 set below. 4 = 0 5 = 0 6 = 0 7 = 0 8 = 0 9 = 0 0 = 0 Might this be possible with ISERROR? -- |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number Transformation
By entering a numerical value in a cell A1, I would like to see another
value displayed in cell B1. For instance, a 1 in A1 would display 11 in B1. 1 = 11 2 = 8 3 = 7 Else = 0 set below. 4 = 0 5 = 0 6 = 0 7 = 0 8 = 0 9 = 0 0 = 0 Put this in B1... =IF(A1=1,11,IF(A1=2,8,IF(A1=3,7,0))) and copy it down. Rick |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number Transformation
Sun, 17 Jun 2007 13:39:31 -0500 from Saxman
: By entering a numerical value in a cell A1, I would like to see another value displayed in cell B1. For instance, a 1 in A1 would display 11 in B1. 1 = 11 2 = 8 3 = 7 I guess you mean "transforms to" rather than "equals". 4 = 0 .... 0 = 0 Might this be possible with ISERROR? It might, but I think it's easier with LOOKUP. :-) Put this in B1: =LOOKUP(A1,{0,1,2,3,4},{0,11,8,7,0}) The first array is the "look-for" values, and the second is the "transform to" values. If A1 is greater than 4, LOOKUP uses the "transform-to" value that corresponds with the "look-for" value of 4, which is the 0 you specified. Limitations: If A1 is negative you'll get #N/A in B1. If A1 is not a whole number you'll get something in B1 that corresponds to the next lower number. For example, 2.2 in A1 puts 8 in B1. -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number Transformation
Saxman,
=CHOOSE(A2,11,8,7,0,0,0,0,0,0) This won't handle 0 or above 9 or blank. So: =IF(AND(A3=1,A3<=9),CHOOSE(A3,11,8,7,0,0,0,0,0,0) ,0) -- Earl Kiosterud www.smokeylake.com Note: Top-posting has been the norm here. Some folks prefer bottom-posting. But if you bottom-post to a reply that's already top-posted, the thread gets messy. When in Rome... ----------------------------------------------------------------------- "Saxman" wrote in message ... By entering a numerical value in a cell A1, I would like to see another value displayed in cell B1. For instance, a 1 in A1 would display 11 in B1. 1 = 11 2 = 8 3 = 7 Else = 0 set below. 4 = 0 5 = 0 6 = 0 7 = 0 8 = 0 9 = 0 0 = 0 Might this be possible with ISERROR? -- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number Transformation
Hello,
Put into C1:D4 1 11 2 8 3 7 4 =A1 Then enter into B1 =VLOOKUP(A1,C1:D4,2,0) Regards, Bernd |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number Transformation
Earl Kiosterud wrote:
=IF(AND(A3=1,A3<=9),CHOOSE(A3,11,8,7,0,0,0,0,0,0) ,0) This works fine by pasting into B1,B2,B3 and amending the function. Thanks. -- |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number Transformation
On 17 Jun., 21:14, Bernd P wrote:
Hello, Put into C1:D4 1 11 2 8 3 7 =A1 Errormessage Then enter into B1 =VLOOKUP(A1,C1:D4,2,0) Regards, Bernd |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number Transformation
Saxman,
Glad to hear it. Thanks for the feedback. The VLOOKUP solutions that have been offered would certainly be the way to go if you had a lot of values, and if you do get more, you should probably switch to that solution. The IF/AND/CHOOSE solution I offered would start to get messy and difficult to update when things change, whereas the table with VLOOKUP is a clean way of handling it. -- Earl Kiosterud www.smokeylake.com Note: Top-posting has been the norm here. Some folks prefer bottom-posting. But if you bottom-post to a reply that's already top-posted, the thread gets messy. When in Rome... ----------------------------------------------------------------------- "Saxman" wrote in message ... Earl Kiosterud wrote: =IF(AND(A3=1,A3<=9),CHOOSE(A3,11,8,7,0,0,0,0,0,0) ,0) This works fine by pasting into B1,B2,B3 and amending the function. Thanks. -- |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number Transformation
Earl Kiosterud wrote:
Saxman, Glad to hear it. Thanks for the feedback. The VLOOKUP solutions that have been offered would certainly be the way to go if you had a lot of values, and if you do get more, you should probably switch to that solution. The IF/AND/CHOOSE solution I offered would start to get messy and difficult to update when things change, whereas the table with VLOOKUP is a clean way of handling it. My data will remain static, so the solution is just fine. Thanks for replying. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting Test Scores to somewhat of a Linear Transformation | Excel Worksheet Functions | |||
Count number of times a specific number is displayed in a cell ran | Excel Worksheet Functions | |||
Transformation d'images avec une extension .jpg ou .jpeg en images avec extension .bmp ou .ico | Excel Discussion (Misc queries) | |||
excel format cells/Number/Category: Number problem | Excel Discussion (Misc queries) | |||
Rounding a number to a multiple quantity that adds to a fixed total number | Excel Worksheet Functions |