Number Transformation

  • Thread starter Thread starter Saxman
  • Start date Start date
S

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

Else = 0 set below.

4 = 0
5 = 0
6 = 0
7 = 0
8 = 0
9 = 0
0 = 0

Might this be possible with ISERROR?
--
 
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
Click to expand...

Put this in B1...

=IF(A1=1,11,IF(A1=2,8,IF(A1=3,7,0)))

and copy it down.

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

I guess you mean "transforms to" rather than "equals".
4 = 0 ....
0 = 0

Might this be possible with ISERROR?
Click to expand...

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.
 
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...
 
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
 
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...
-----------------------------------------------------------------------
 
Earl said:
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.
Click to expand...

My data will remain static, so the solution is just fine. Thanks for replying.

--
 
Back
Top