Re: Function/Formula Question by Rick
Rick
Thu May 08 14:09:35 CDT 2008
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" <JohnMorgan@discussions.microsoft.com> wrote in message
news:39A7A9B7-1EB9-45D5-B2FC-A6D92897D238@microsoft.com...
> 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 Morgan@discussions.microsoft.com> wrote in message
>> news:0DC7BC5A-E203-480A-8274-E32B2D22E030@microsoft.com...
>> > 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!
>>
>>
>>