Function/Formula Question

[John Morgan]

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!

 

[Gaurav]

Try this in B1

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

Hope that helps.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[David Biddulph]

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

 

[John Morgan]

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

 

[Rick Rothstein \(MVP - VB\)]

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