 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008Function/Formula Question
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. > Example: > I have a drop-down in A1 with 5 options (A, B,C,D,E) [quoted text clipped - 17 lines] > > 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 > Try this in B1 > [quoted text clipped - 23 lines] > > > > 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 > Hi Gaurav, > Thank you! That code worked GREAT! I needed to include some error [quoted text clipped - 42 lines] >> > >> > 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 > Example: > I have a drop-down in A1 with 5 options (A, B,C,D,E) [quoted text clipped - 17 lines] > > Help! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.