Example:
I have a drop-down in A1 with 5 options (A, B,C,D,E)

I need to be able to have a formula that looks at A1, and if it's value is
"A" (from the drop-down) then place a "1" into cell B1.

I am trying to perform this without a formula residing in B1 because a
calculation is being performed on the value of B1. If the formula is there
and the value is displayed, the calculation fails because it sees the formula
instead of the value.

The formula I use to display the value is:

=IF(A1="A","1",IF(A1="B","2",IF(A1="C","3",IF(A1=" D","4",IF(A1="E","5")))))

But, I need the end result to be a number that can be a part of another
calculation.

Help!

Try this in B1

=LOOKUP(A1,{"A","B","C","D","E"},{1,2,3,4,5})

Hope that helps.


"John Morgan" <John wrote in message
...
Example:
I have a drop-down in A1 with 5 options (A, B,C,D,E)

I need to be able to have a formula that looks at A1, and if it's value is
"A" (from the drop-down) then place a "1" into cell B1.

I am trying to perform this without a formula residing in B1 because a
calculation is being performed on the value of B1. If the formula is
there
and the value is displayed, the calculation fails because it sees the
formula
instead of the value.

The formula I use to display the value is:

=IF(A1="A","1",IF(A1="B","2",IF(A1="C","3",IF(A1=" D","4",IF(A1="E","5")))))

But, I need the end result to be a number that can be a part of another
calculation.

Help!

The formula I use to display the value is:

=IF(A1="A","1",IF(A1="B","2",IF(A1="C","3",IF(A1=" D","4",IF(A1="E","5")))))

But, I need the end result to be a number that can be a part of another
calculation.

Try this formula...

=CODE(A1)-64

Rick

If you want the result to be a number, why have you surrounded the numbers
with quote marks to make them text strings?

Another option is =CODE(A1)-64
--
David Biddulph

"John Morgan" <John wrote in message
...
Example:
I have a drop-down in A1 with 5 options (A, B,C,D,E)

I need to be able to have a formula that looks at A1, and if it's value is
"A" (from the drop-down) then place a "1" into cell B1.

I am trying to perform this without a formula residing in B1 because a
calculation is being performed on the value of B1. If the formula is
there
and the value is displayed, the calculation fails because it sees the
formula
instead of the value.

The formula I use to display the value is:

=IF(A1="A","1",IF(A1="B","2",IF(A1="C","3",IF(A1=" D","4",IF(A1="E","5")))))

But, I need the end result to be a number that can be a part of another
calculation.

Help!

Hi Gaurav,
Thank you! That code worked GREAT! I needed to include some error handling
and I am receiving some unexpected results. I am stumped at the
inconsistancies in the values returned in my modified formula. I was
wondering if you could take a look and clue me in on what I am doing wrong...

Here is the new code that is not returning the expected results:

=IF(ISERROR(LOOKUP(F5,{"100k","76k-100k","51k-75k","26k-50k","<25k"},{1,2,3,4,5})),"-",(LOOKUP(F5,{"100k","76k-100k","51k-75k","26k-50k","<25k"},{1,2,3,4,5})))

Could it be that there is an attempt to perform calculations on the lookup
values?

THanks,
John

"Gaurav" wrote:

Try this in B1

=LOOKUP(A1,{"A","B","C","D","E"},{1,2,3,4,5})

Hope that helps.


"John Morgan" <John wrote in message
...
Example:
I have a drop-down in A1 with 5 options (A, B,C,D,E)

I need to be able to have a formula that looks at A1, and if it's value is
"A" (from the drop-down) then place a "1" into cell B1.

I am trying to perform this without a formula residing in B1 because a
calculation is being performed on the value of B1. If the formula is
there
and the value is displayed, the calculation fails because it sees the
formula
instead of the value.

The formula I use to display the value is:

=IF(A1="A","1",IF(A1="B","2",IF(A1="C","3",IF(A1=" D","4",IF(A1="E","5")))))

But, I need the end result to be a number that can be a part of another
calculation.

Help!

So, the question you asked originally is not the actual question you had.
Are you aware that the volunteers here customize their answers to what is
actually asked? For future reference... when you ask questions on
newsgroups, post your actual situation, not some simplified version of it...
you will get faster, more on the mark answers to your problem that way.

Secondly, do you also post under the name Office_Novice? I ask because
someone using that name asked a question (over in the m.p.e.programming
newsgroup) showing virtually the same range of values you showed in this
last post of yours (his ranges did not include the k's that yours have),
right down to the overlooking of the value 25k... none of your shown ranges
cover that value (as did none of Office_Novice's ranges either).

I offered this formula to Office_Novice and, except for the error handling,
it would apply to your question as well...

=MATCH(LEFT(A1),{"","7","5","2","<"},0)

Since you indicate you need error handling, this should do what you want...

=IF(ISNUMBER(SEARCH(","&A1&",",",100k,76k-100k,51k-75k,26k-50k,<25k,")),MATCH(LEFT(A1),{"","7","5","2","<"}, 0),"-")

Rick


"John Morgan" wrote in message
...
Hi Gaurav,
Thank you! That code worked GREAT! I needed to include some error
handling
and I am receiving some unexpected results. I am stumped at the
inconsistancies in the values returned in my modified formula. I was
wondering if you could take a look and clue me in on what I am doing
wrong...

Here is the new code that is not returning the expected results:

=IF(ISERROR(LOOKUP(F5,{"100k","76k-100k","51k-75k","26k-50k","<25k"},{1,2,3,4,5})),"-",(LOOKUP(F5,{"100k","76k-100k","51k-75k","26k-50k","<25k"},{1,2,3,4,5})))

Could it be that there is an attempt to perform calculations on the lookup
values?

THanks,
John

"Gaurav" wrote:

Try this in B1

=LOOKUP(A1,{"A","B","C","D","E"},{1,2,3,4,5})

Hope that helps.


"John Morgan" <John wrote in message
...
Example:
I have a drop-down in A1 with 5 options (A, B,C,D,E)

I need to be able to have a formula that looks at A1, and if it's value
is
"A" (from the drop-down) then place a "1" into cell B1.

I am trying to perform this without a formula residing in B1 because a
calculation is being performed on the value of B1. If the formula is
there
and the value is displayed, the calculation fails because it sees the
formula
instead of the value.

The formula I use to display the value is:

=IF(A1="A","1",IF(A1="B","2",IF(A1="C","3",IF(A1=" D","4",IF(A1="E","5")))))

But, I need the end result to be a number that can be a part of another
calculation.

Help!