How to use more than 7 IF functions in a formula?

I tried to post a question earlier but it never appeared on the messag
list.  Not sure if it went through. So here it is again. 

I am trying to set up a formula where when I type in a weight i
another column it will automatically puts in an assigned value for tha
weight.  The problem is that the weights range from 110 through 260.  
have found that I cannot put in more than 7 IF functions in a formula.
This is my first time trying to do something like this and I am havin
problems.  I know there has to be a way to do this. 

For example I tried this formula and it worked except I con only put i
7 IF statements:
If(b4=110,.988,ifb4=111,.983,if(br=112,.980 and so on to 7 IF"s

Can anyone explain in detail and laymens terms how I can solve thi
problem.  I am definitely a newbie and need help

--
Message posted from http://www.ExcelForum.com

0
4/7/2004 6:51:42 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
322 Views

Similar Articles

[PageSpeed] 14

I have never done it, but I think the answer is to break your formula up to
use in different cells. E.g., in A1 you have the formula =If(B1=true, "yes",
"no") and in B1 you have a nested if that refers to a nested if in C1 and so
on.

HTH, Greg

"jack_sxh >" <<jack_sxh.14cw44@excelforum-nospam.com> wrote in message
news:jack_sxh.14cw44@excelforum-nospam.com...
> I tried to post a question earlier but it never appeared on the message
> list.  Not sure if it went through. So here it is again.
>
> I am trying to set up a formula where when I type in a weight in
> another column it will automatically puts in an assigned value for that
> weight.  The problem is that the weights range from 110 through 260.  I
> have found that I cannot put in more than 7 IF functions in a formula.
> This is my first time trying to do something like this and I am having
> problems.  I know there has to be a way to do this.
>
> For example I tried this formula and it worked except I con only put in
> 7 IF statements:
> If(b4=110,.988,ifb4=111,.983,if(br=112,.980 and so on to 7 IF"s
>
> Can anyone explain in detail and laymens terms how I can solve this
> problem.  I am definitely a newbie and need help.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
gregkoppel (32)
4/7/2004 7:09:29 PM
it did and you have two replies!!

"jack_sxh >" <<jack_sxh.14cw44@excelforum-nospam.com> wrote in message
news:jack_sxh.14cw44@excelforum-nospam.com...
> I tried to post a question earlier but it never appeared on the message
> list.  Not sure if it went through. So here it is again.
>
> I am trying to set up a formula where when I type in a weight in
> another column it will automatically puts in an assigned value for that
> weight.  The problem is that the weights range from 110 through 260.  I
> have found that I cannot put in more than 7 IF functions in a formula.
> This is my first time trying to do something like this and I am having
> problems.  I know there has to be a way to do this.
>
> For example I tried this formula and it worked except I con only put in
> 7 IF statements:
> If(b4=110,.988,ifb4=111,.983,if(br=112,.980 and so on to 7 IF"s
>
> Can anyone explain in detail and laymens terms how I can solve this
> problem.  I am definitely a newbie and need help.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
news9767 (27)
4/7/2004 7:11:39 PM
You sound like you need a lookup table.  I could try to explain here
but it would be easier to search for VLookup or Lookup or Hlookup i
Excel Help.

Basically what a lookup does is let you specify a compare and a retur
range to essentially do a bunch of IFs.  Here's a brief example.


Code
-------------------
  ColA   ColB
  110    .988
  111    .983
  112    .980
  113    .977
  114    .97
-------------------

Your function would be:

=VLOOKUP(C1,A1:B5,2)

Assuming your entered weight is in cell C1.



--
Message posted from http://www.ExcelForum.com

0
4/7/2004 7:18:11 PM
Reply:

Similar Artilces:

Special Pasting a work book with many sheets and formulas
I have a workbook with many sheets that all have formulas and links to other data. I want to save the workbook as another name with all the worksheets keeping the values only (no links or formulas). Is there a quick way to do this for everysheet without having to special paste every sheet in the workbook. So can I save everysheets data values at workbook level. See this page for a code example http://www.rondebruin.nl/values.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "lex63" <lex63@discussions.microsoft.com> wrote in message news:ED708...

Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No Data",IF(V7="Yes",&qu...

*major* functional issue with MOSS/WSS?
We are looking to move a big site over to using MOSS in a big as an alternative to shared network drives (ugh). One of the biggest issues is sending docs as attachments via email for others to edit. We'd like to get everyone to use the 'send a link' functionality from thew Sharepoint DL BUT when the user receives the email and clinks on the embedded link the doc opens read only. They then cant edit and save their changes. Seems a big an counter intuitive functional flaw. Are we missing something here? Is there any easy (for the end user) way to send those links to a doc ...

How do I set up a daily average of unit sales formula
More info required. -- HTH RP (remove nothere from the email address if mailing direct) "jim m" <jim m@discussions.microsoft.com> wrote in message news:7E6D4510-97C1-42D4-A402-5590201C6065@microsoft.com... > ...

Can SUMPRODUCT be used for entire column?
This formula results in a numeric result: =SUMPRODUCT((A1:A20)*(B1:B20="b")) but this formula results in a #NUM! result: =SUMPRODUCT((A:A)*(B:B="b")) Which means I need to specify the length of the columns, which may grow over time. Any way to do this for the entire column, without having to specify the length of the column? XL07 removed the limitation on array formulas (which SUMPRODUCT is, even though it doesn't require CTRL-SHIFT-ENTER) and entire columns. For pre-XL07, one can use =SUMPRODUCT(A1:A65535, --(B1:B65536="b")) to get all but on...

Using scanner in Word97
I want to scan a picture into word97 using my HP 4370 ScanJet. Could not find option of "From Scanner" under "Insert" --> "Picture". Apparently I must need some sort of Word97 Add-On. What and where is the add-on? Is it on the Office97 CD? Same applys to Excel97. Also, does microsoft sponsor a Word97 / Office97 discussion group? If so, would appreciate a link. "PSRumbagh" <PSRumbagh@discussions.microsoft.com> said this in news item news:39100DC1-A7EE-4679-881D-526BAA386620@microsoft.com... > I want to scan a picture ...

Using later version of microsoft access
Hi, I've got access 2000 on my computer. When I go to open a database someone sent me I get an error message : this database is in an unrecognized format. The database may have been created with a later version of microsoft access. Is there any way I can open and use this file (short of upgrading to later version of access)? If you do not have Access 2002 or 2003, ask the person to save it in Access 2000 format for you. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at m...

unable to read file #7
Does anyone know how to solve this error? This is the second time I have had the same error on the same file. Last time I was able to recover from a saved copy and updated fine. This time whenI opened the copy and updated it, then saved it, I got the same error when I tried to open it again, Now I don't have a good copy. I have downloaded all avaliable updates and tried running "Excel.exe /regserver>ok" This did not help. I also ran defran on the drive. "Randell" wrote > Does anyone know how to solve this error? This is the > second time I have had th...

Office installation and Windows 7
I got a new computer today with Windows7. It is a 64bit system where I had 32 before. I tried to install my Office Professional 2007 and it would not install. I got a message that Microsoft would check the problem and get back to me, then another message that the program was shutting down. I am in school and really need my Office. Is there a way around this so that I can get it installed? -- Linda Doesnt make any difference, 64 / 32 bit. Does your new PC have a Trial Version of Office & Activation Assistant that you failed to uninstall first, then rebooted? "Linda...

Problem with Update function of CRecordSet class
Hi All When I use from Update function of CRecordSet class, it apply to table after a few time(for example 1 second) when I need to see it at time. Thanks ...

Using Company Wide Mail Templates.
Hi, I do not know if i am at the right spot here, or if it is evne possible, but i got the following question. My boss would like me to make sure that every outgoing mail has the same looks. It starts by adding a signature that is the same for everyone, except with ofcourse personalized information. This was easily done by giving everyone a signature. The next question is however, to put the head of our website, also above our mail. This means that every user that sends a mail, the mail will have a nice header, underneath that header, the mail is typed, and then its ended with the si...

Dynamic Range Selection Using VBA
What I'm trying to accomplish is to be able to run a procedure that selects a range based on a number provided in another cell. For example; if the number 10 is in cell A1, then cells A20:A30 would be selected when I run the macro. If the number 6 is provided, then cells A20:A26 would be selected. Not sure where to start, so any help is appreciated. this may do what you want range("A20").Resize(range("A1").Value+1).select -- Gary Keramidas Excel 2003 "TEK" <TEK@discussions.microsoft.com> wrote in message news:DA9FFF99-FC28-...

Accommodating for empty cells in this formula?
I have a formula in cell H21, for example, reads like this: =IF($G21<>"",($H20-$G21),"") is there a way to adjust the formula so that an empty cell in G21 doesn't give the #VALUE! in subsequent cells in column H? Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for any and all empty cells in A2 to A9. It no longer matters if any of the cells are empty, the formula correctly gives the correct addition of A1 plust a sum of everything between A2 to A10 without any #VALUE! results. Was hoping to have the formula above als...

how create Quota filter in WIndows 2003 R2 using Script
Hi all I need create quota filter in Server 2003 R2 using vbscript. quota filter should be applied to directories and not by users. I searched information about it on google without success. thank's in advance Daniel Hi Daniel. First you need open your FSRM (File Server Resource Manager), then you create a quota template, you must specify if your quota is "software" (just monitoring, but never deny the user) or "hardaware" (deny users when they use 100% of the quota), you must specify if you want send e-mail to user when this user use...

Help With Margin Formula
Hello, I need help with a margin forumla (calculated from retail). Say I have a cost of $10.00, and I need the formula to calculate a 40% margin from retail. So the retail should end up at $16.67. Not sure how to get from $10.00 to $16.66, I just know the cost and the margin I need to make. Thanks JR =A1/(100%-40%) -- Kind regards, Niek Otten "JR" <gaspower@aol.com> wrote in message news:eGszf.424$2O6.53@newssvr12.news.prodigy.com... > Hello, > I need help with a margin forumla (calculated from retail). Say I have a > cost of $10.00, and I need the formul...

IE 7 webpage view
How do I make IE7 keep the webpage I want displayed everytime I open it up, without having to resize it everytime? Thanks, RED in NC Try stretching the small window to full size. (Do not use Maximize) Ensure that window is the last one closed and IE should remember the size next time. ....Alan -- Alan Edwards, MS MVP Windows -Internet Explorer On Thu, 14 Jan 2010 06:27:53 -0500, in microsoft.public.internetexplorer.general, "anglirich" <anglirich@lexcominc.net> wrote: >How do I make IE7 keep the webpage I want displayed everytime I open it up, &g...

date function #3
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C73005.0FA093A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a cell A1 with date 5/2/07. If in cell A2 I do month(A1), I get = 5. Is there a way to return May instead? either through a command or = formatting? I can reference cell A2 with the 5 with a if(cell =3D 5, = "May", ) in cell A3, but the date in cell A1 varies from Jan to Dec and = the 12 embedded if's in cell A3 are too long (that is the error I get = when trying to do so), plus i...

Using Publisher 200 with Publisher 2003
How do I covert PUB2000 documents to Pub2003 documents and vice versa? Pub 2003 can open anything, no conversion necessary. Going backward is a bit trickier. File - Save As and chance the file type to a Pub 2000 file. Possible problems can arise if you've used a feature that was not available in the 2000 version and your file size will grew immensely. -- JoAnn Paules MVP Microsoft [Publisher] "nasuco" <nasuco@discussions.microsoft.com> wrote in message news:500C7A7A-4026-434C-8CC2-2DFDB69D81C4@microsoft.com... > How do I covert PUB2000 documents to Pub2003 do...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from http://www.ExcelForum.com The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...

Formula to count the number of different values in a range
I'm looking for a formula that will give me the number of different values in a range. Example: Column A may have five cells that are "4", five cells that are "7", five cells that are "9". Of the fifteen cells that contain data, there are only 3 different values. I'd like to use a formula that will count the number of different values in column A, in this case the result is "3". Thanks, Paul Try... =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&"")) OR =SUM(IF(A1:A15<>"",1/COUNTIF(A1:A...

Formula to display nearest following Thursday in mm/dd/yyyy format
Hello, I have been reading and trying different suggestions here to no avail. What I need is a formula to calculate the nearest following thursday, and display it in mm/dd/yyyy format. To be clear, I have a column of varying dates. I need a formula to return the next thursday for each of those dates. To illustrate, say I have 05/22/2010, 05/23/201, 05/24/2010, & 05/26/2010 in cells A1 through A4. In cells B1 through B4, I would like to see 05/27/2010, 05/27/2010, 05/27/2010, & 05/27/2010 representing the following thursday. Thank you for your help! BW T...

Find Highest Score In List Formula
Hello all, I'm looking to return the highest score for a users with multiple scores in a list of other users with multiple scores. Thank you, Ron Say the data is like: frank 56 joe 9 frank 74 frank 101 jim 143 jim 146 joe 200 frank 164 joe 135 joe 127 joe 177 jim 10 jim 135 jim 53 frank 190 joe 109 jim 193 jim 29 jim 8 jim 107 joe 93 joe 9 jim 153 jim 186 joe 36 jim 174 jim 141 frank 55 jim 92 frank 141 joe 15 frank 5 frank 34 joe 161 jim 103 joe 88 and we want the max score for frank: =MAX(IF(A1:A36="frank",B1:B36,""...

Simple Formula
I have a formula, bt4/37 (bt4 = 6) and it returns 5. However, my calculator and an Access database returns 16. Can someone tell me why Excel returns 5? thanks. -- Kat3n hi, Either I'm reading this post incorrectly or you have a broken calculator and are gettting results out of excel & access that are equally incorrect. 6/37= 0.162 recurring So if we assume that your result of 16 is a typo and you meant .16 there must be something your not telling us about the formula your using in Excel. How is the 6 derived in BT4 ? What is the format of BT4 ? Post the pr...

VC++ .NET 2002: error C2509: 'OnPrepareDC' : member function not declared in 'CEg06aView'
Hi all I am learning the OnPrepareDC Function and ShowFont Helper Function from the Ex06a example of the book 'Programming With Microsoft Visual C++ .NET Core Reference' by G. Shepherd with D. Kruglinski. I started my new MFC Project 'Eg06a' from the MFC Wizard and added/edited the OnPrepareDC Function and ShowFont Helper Function in the Eg06aView.cpp and Eg06aView.h files - see the attached files below. When I did 'Build' on my project 'Eg06a', I got the following error: c:\Documents and Settings\Scott H. Chang\My Documents\Visual Studio Projects\Eg06a\Eg06aVi...

How to use different return email addresses
I use outlook 98. I have a mailbox with three aliases. People sending me emails can use any of the three email aliases and the email will arrive in the same mailbox. Accessing the mailbox once will retrieve all messages regardless of which alias was used. If I reply to these emails though, I want the recipient to think that the email has come from the alias that they originally used. What outlook always seems to do is use the email address of the service that is listed first in Tools->Services->Delivery. Is there any way Outlook can be set up so when I reply to a given email, my email ...