IF formulas #5

I'm wanting to continue this function all the way to "Z" and "9".  This is 
used to quickly cypher names for phone pad input.  It takes 4 formulas to 
achieve this since Excel only allows 7 IF functions.

=IF(B10="A","2",IF(B10="B","2",IF(B10="C","2",IF(B10="D","3",IF(B10="E","3",IF(B10="F","3",IF(B10="G","4",IF(B10="H","4"))))))))

I've completed all 4 formulas but I'm wanting to display the result under 
the letter without going 4 deep and/or having blanks (using conditional 
format).

NAME:	z
TELEPHONE NUMBERS:	
FALSE
FALSE
FALSE
9

I need either solution:  IF function or a way to display results by suming 
the 4 formulas.  I hope this makes sense.  :-((

0
Golden (2)
11/23/2005 4:43:05 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
412 Views

Similar Articles

[PageSpeed] 1

Use VLOOKUP with a table.

Jerry

"Paul Golden" wrote:

> I'm wanting to continue this function all the way to "Z" and "9".  This is 
> used to quickly cypher names for phone pad input.  It takes 4 formulas to 
> achieve this since Excel only allows 7 IF functions.
> 
> =IF(B10="A","2",IF(B10="B","2",IF(B10="C","2",IF(B10="D","3",IF(B10="E","3",IF(B10="F","3",IF(B10="G","4",IF(B10="H","4"))))))))
> 
> I've completed all 4 formulas but I'm wanting to display the result under 
> the letter without going 4 deep and/or having blanks (using conditional 
> format).
> 
> NAME:	z
> TELEPHONE NUMBERS:	
> FALSE
> FALSE
> FALSE
> 9
> 
> I need either solution:  IF function or a way to display results by suming 
> the 4 formulas.  I hope this makes sense.  :-((
> 
0
11/23/2005 4:47:02 PM
No IFs are required.  Try:

=CHOOSE(CODE(B10)-64,2,2,2,3,3,3,4,4,4,5,5,5,6,6,6,7,7,7,7,8,8,8,9,9,9,9)
-- 
Gary's Student


"Paul Golden" wrote:

> I'm wanting to continue this function all the way to "Z" and "9".  This is 
> used to quickly cypher names for phone pad input.  It takes 4 formulas to 
> achieve this since Excel only allows 7 IF functions.
> 
> =IF(B10="A","2",IF(B10="B","2",IF(B10="C","2",IF(B10="D","3",IF(B10="E","3",IF(B10="F","3",IF(B10="G","4",IF(B10="H","4"))))))))
> 
> I've completed all 4 formulas but I'm wanting to display the result under 
> the letter without going 4 deep and/or having blanks (using conditional 
> format).
> 
> NAME:	z
> TELEPHONE NUMBERS:	
> FALSE
> FALSE
> FALSE
> 9
> 
> I need either solution:  IF function or a way to display results by suming 
> the 4 formulas.  I hope this makes sense.  :-((
> 
0
GarysStudent (1572)
11/23/2005 5:01:06 PM
Reply:

Similar Artilces:

Formula to find average of field for all rows that contain another field #2
Say I have a worksheet with the following information: Name Position Salary John Producer $10,000 Jeremy Producer $98,000 Jaime Producer $50,000 Darren Artist $67,000 Chris Artist $75,000 Clint Artist $30,000 Adam Artist $57,000 In Cell C2 (salary for John) I want to create a formula that looks for all rows that contain the same position as in B2 (John's position which is producer) and then calculates the average of all the salaries in column C of those rows that have the position producer. Then I want to c...

HotFixes
How do I find out what Exchange hotfixes have been applied to an Exchange 5.5 (SP4) server (running on Win2k)? Is there a utility I can run to detect them? Or are they listed in the registry (in a common location hopefully)? thanks Clay Hilton yes if you check in the add/remove programs ,you should see all the hotfixes listed there .They will have a Q number appended . >-----Original Message----- >How do I find out what Exchange hotfixes have been applied to an Exchange >5.5 (SP4) server (running on Win2k)? Is there a utility I can run to detect >them? Or are they lis...

Team Folders migration from 5.5 to 2003
Are there any known issues with migrating Exchange/Outlook Team Folders from Exchange 5.5 to Exchange 2003 using PFMigrate? We have many Team Folders on our older Exchange 5.5 server that we MUST migrate over to Exchange 2003 with full functionality Thanks, FastEddie These are Team Folders created with the Team Folders Wizard? I don't know whether PFMigrate will migrate the folder home page setting and the custom form that a couple of the folders use. You may need to handle those manually. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for ...

Removing Last 5.5 Server that has an IMC
I am following the steps in the article: http://support.microsoft.com/kb/883407/ before removing my last 5.5 server and moving the smtp services over to my 2003 server. The scenario that applies is single\last 5.5 server (it is a single AND last :) Step 3 - verify the smtp connector is seen in your exchange server 5.5 configuration - is the step that keeps failing. I have repeated step 2 now a couple of times and waited for about 3 hours now and the connection still does not show up. I also see no obvious (or any) errors in the event log on my 5.5 server. if I can successfully send a...

migration shut down the information store 5.5
Hi all I have two 5.5 and two 2003 sp1 exchange server all in the same admin group and site. on one of the 5.5 server, i have all the mailboxes moved over to one of my 2003 servers. I would like to turn off the information store on the 5.5 server that has all the production mailboxes moved over to the 2003 server, this way if anyone complains that they cant get into email, then i know that the mailbox needs to get moved. My questions are, by tunrning off the information store service on the 5.5 server, will this negatively effect the ADC replication? I have removed the IMC off of this...

RNDIS, WLK 1.5, crash
Moin, I test a RNDIS device which is connected via USB to the host. During power management tests the RNDIS driver part usb8023x.sys holds the IRP too long and the driver verifier complains about it with DRIVER_POWER_STATE_FAILURE (9f) A driver is causing an inconsistent power state. The tests fail on XP x86 SP 3 and Vista x86 SP2. Does MS provide a fixed driver ? Best regards, Stefan P.S.: The first analysis with WInDbg shows ******************************************************************************* * ...

Menu Item in GP 7.5
Hi!!! What is the code to add a menu in GP 7.5? If you have a look at the v7.5 integration guide manual or the v7.50 samples they show how you can add your menu entries into the SY_Palette_MSTR and/or SY_Toolbar_MSTR tables. David Musgrave [MSFT] Senior Development Consultant Escalation Engineer MBS Support - Asia Pacific Microsoft Business Solutions http://www.microsoft.com/BusinessSolutions mailto:dmusgrav@online.microsoft.com Any views contained within are my personal views and not necessarily Microsoft Business Solutions policy. This posting is provided "AS IS" with no...

Simple formula for newbie!
Hi Simple question, as just starting with formulas!! But.. I need a formula whereby if cell D1 >= 10, then multiply E1*D1. Else multiply F1*D1. Thanks! TC Try... =IF(D1>=10,E1*D1,F1*D1) Hope this helps! In article <#GK8G$#oFHA.3316@tk2msftngp13.phx.gbl>, "TC" <trevc80@hotmail.com> wrote: > Hi > > Simple question, as just starting with formulas!! But.. > > I need a formula whereby if cell D1 >= 10, then multiply E1*D1. Else > multiply F1*D1. > > Thanks! > TC TC You'd almost got it! Try - =IF(D1>=10,E1*D1...

need help with formula #10
I'd appreciate help with a formula I need a formula to calculate the following 4.5% of any amount up to and equal to $41,100.00 PLUS 6.00% on an amount above $41,100. ie $20,000 would be (20,000 x .045) $900.00 ie $50,000 would be [(41,100 x .05) + (8,900 x .060)] $2,383.50 Thanks Pete -- pgruenin ----------------------------------------------------------------------- pgruening's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2601 View this thread: http://www.excelforum.com/showthread.php?threadid=47876 I used =IF(A1>41100,(A1-41100)*6%+(41100*4.5%...

need to hide formula ...
I have a problem with a Formula that gives me a #VALUE! but would like this to be hidden. Earlier I had posted for help on #DIV/0! and I the response was great it worked and the problem was fixed. But I realized that later this cause a problem in my cell next to it. So this is how the whole thing is actually, on colom C cell-A I have this formula: =ROUND(D5*20,0)/20 on colom D cell-A I have this formula: =$A$5/SUM($B$5:$B$103)*B5 Previously I received a solution for colom D cell-A and it went like: =IF(SUM($B$5:$B$103)*B5=0,"",$A$5/SUM($B$5:$B$103)*B5) Worked wonderfully. But...

I want to trace the precedents in a formula when they are on mult.
Multiple spreadsheets in a workbook. When I click on formula only shows precedent on 1st sheet I want to see on rest of sheets Paul wrote: > Multiple spreadsheets in a workbook. When I click on formula only shows > precedent on 1st sheet I want to see on rest of sheets Unfortunately it doesn't. That's something I would like to see as well. -- Registered Linux User no 240308 Just waiting for Broadband to complete the conversion!(3 weeks and counting!) gordonATgbpcomputingDOTcoDOTuk to email me remove the obvious! Depending on what version of excel (xl2002 in front of me),...

Formula Help #40
I want to use excel to subtract two times and then divide a specified cell by the sum and return the value. Example: I get to point A @ 7:00am depart @ 9:00am and deliver 864 boxes. How many boxes per hour did I deliver? When I sum 9:00am - 7:00am I get 2:00 then if I divide 864 by that I get 0:00 if I leave it formatted as time. And if I change the format to general I get 10368?? I should get 432 9am-7am gives 0.00 in time. if you format as number it will give you 0.08333333... that means 0.083333333.... day. so 864/.0833333......./24 will give your per hour i.e. 432 A1 ------7:00am A...

Password Issue #5
I have Money Plus. I have not been able to login because I get the error message that the file Money is trying to open is password protected. Before this issue started to happen, I my Live ID / Password were the same for both Money and Hotmail. I have tried all of my old passwords, non of them have worked. I event updated my Live ID password, but that did not work. Now what? Thoughts? In microsoft.public.money, bond007jms wrote: >I have Money Plus. I have not been able to login because I get the error >message that the file Money is trying to open is password protected. Before &...

How do I do formulas?
Hi, How do I do formulas? I want to change numbers to forlumas. How do I do this? Can it do done? Thank You. Bryan If you mean you want the sheet to display the formulas instead of th reults you can do it by pressing the Ctrl key & the ' key or Tools Menu > Options > View Tab > Tick Formula -- mudrake ----------------------------------------------------------------------- mudraker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=247 View this thread: http://www.excelforum.com/showthread.php?threadid=27107 Also, make sure your Formula b...

Gross Margin Formulas
Does anybody have a formula for gross margins? I also believe there should be a link to basic accounting formulas for first timers assuming cost is in A1 and price is in B1, =(B1-A1)/B1 format cell as percent -- Regards, Peo Sjoblom "newman" <anonymous@discussions.microsoft.com> wrote in message news:7bd201c402da$08323250$a401280a@phx.gbl... > Does anybody have a formula for gross margins? > > I also believe there should be a link to basic accounting > formulas for first timers > > ...

How can I convert a formula to a value in excel?
I used to use lotus years ago and I know that it was /range value to change a formula to a value. Can you change a formula to a value in excel? Morgansmummy wrote: > I used to use lotus years ago and I know that it was /range value to change a > formula to a value. Can you change a formula to a value in excel? Copy, paste special-values. -- Registered Linux User no 240308 Fedora Core 4, Pan, Thunderbird and Firefox gordonATgbpcomputingDOTcoDOTuk to email me remove the obvious! ...

Excel sum formula #2
Hi everyone. This is my first time here. I actually have a little bi of difficulty for a work that I have to do... It goes like this: O the workbook, I have differents pages. In theses differents pages, have a number on the first second and 3rd page wich is 300300 in th first colum. I'd like to get the sum of the 3 display next to th number 300300. There is an exemple: (First page) (second page) 102120 -5689 156460 -5879 012110 -3956 254680 -4568 300300 -4564 300300 456846 So what i...

Inserting rows with Data, Formula's and Validation
Hi all, I posted this twice earlier today, but it hasn't appeared so if it triple post please forgive! I have a spreadsheet that starting at row 18 contains the following information A18=M$2 B18= Datavalidation indirect ($M$2) C18=IF(B18="No More Options",VLOOKUP($B18,INDIRECT(CONCATENATE($M$2,"_info")),1,FALSE),IF(B19<2,VLOOKUP($B18,INDIRECT(CONCATENATE($M$2,"_info")),1,FALSE),CONCATENATE(B19," x ",VLOOKUP($B18,INDIRECT(CONCATENATE($M$2,"_info")),1,FALSE)))) E18=IF(ISERROR(VLOOKUP($B18,INDIRECT(CONCATENATE($M$2,"_info"))...

How to use autofilter in excel with formula reference changing
Hi, I have a table A B C 1 forro 120 2 ment 80 40 3 forro 50 30 4 ment 40 10 5 ment 30 10 6 forro 20 10 7 forro 10 10 8 forro 5 5 Starting from C2, the column C has a formula (=B1-B2) result = 40; (=B2-B3) result = 30; etc... When I apply autofilter the formula in cell C keeps the original information (=B1-B2), and I would like to have a formula to change and shows de result as below (=B1-B3) result = 70; (=B3-B6) result = 30; etc... A B C 1 forro 120 3 forro 50 70 6 forro 20 30 7 forro 10 10 8 forro 5 5 The objective is to have a formula considering jus...

"550 5.7.1 Unable to relay" TO external domains
Hi together, I have a big problem to send to any recipient which has an external domain (outside of Exhange organization). I deployed Exchange 2003 + SP2 based on Windows 2003 AD. - Installation of Exchnage is a default installation. - Default recipient policy exists. - There is no restriction to send mails. - Only SMTP VS1 is used to send mails. The users on Exchange server cann send mails internally without any problem. BUT if they send any mail to an user who has an external domain address, they get the following error: "550 5.7.1 Unable to relay for username@externaldomain.com&q...

Formula To Split Name
I have a list of names in colum B, that I would like to split into column C and D. The names in column B have a first name, middle intial and a last name. I would like the last name to go into column c and the first name and middle initial to go into column D. Column B Column C Column D Mary A Jones Jones Mary A Hi, Put this in column C =RIGHT(B1,LEN(B1)-FIND("*",SUBSTITUTE(B1," ","*",LEN(B1)-LEN(SUBSTITUTE(B1," ",""))))) Then this in column D ...

Exchange 5.5 & W2K SP4
I'm getting ready to install W2K Service Pack 4 on my Exchange Server. I'd like to know if anyone has had any issues doing so. Server Configuration: Windows 2000 SP2, Member Server, No other services (strictly an Exchange Server). Exchange 5.5 SP4 Thanks, Terry N0_Spam_terryb@esi.net The only thing that I have noticed is that after appling Windows SP4 is that the Exchange Optimizer will generate an error and you will not be able to move the files automatically. However the work around is to stop all the services before launching Exchange Optimizer. -travis >-----Orig...

Outlook 2007 #5
Does anybody use AOL Accounts??? Are you having problems only SENDING mail from an AOL setup? Seems smtp part not sending, but the imap receives... Sent mail just sits in the outbox.. Does your ISP support connecting to an ISP off their network? My ISP blocks port 25 access off their network. "joey022461" <joey022461@discussions.microsoft.com> wrote in message news:A6F5B1BF-F336-4AEE-980A-9BF2411ADDC3@microsoft.com... > Does anybody use AOL Accounts??? > > Are you having problems only SENDING mail from an AOL setup? > > Seems smtp part not sending, but t...

Is it possible to shorten this LOOKUP Formula?
I have 3 groups of Drivers, all of them work a for day work week. Group 1 works Monday-Thursday, 2 works Tuesday-Friday, and 3 works Friday, Saturday, Sunday, Monday. Group 1 with Friday off has assignment numbers of 900-949, 2 with Monday off has 950-999, 3 does 900-949 on Friday and 950-999 on Monday. (The weekend numbers are totally different and not included in this process). On my spread sheet I use these numbers in column A to time track the drivers as they pass various locations. Column H is where I place the names of the drivers. I have H set to change names for the different d...

increment reminder level SP 5
In the Collection Mgmt Query, we found out “increment reminder level” did not function after we installed ...Did someone also face the same problem? AC ...