Improving Formula; IF Statement Is Too Long
Dec 21, 2009
I have a formula which is currently too long and which I'm sure could be done more efficiently. That plus Excel is saying the formula is too long. I have pasted the formula below and broken it up for clarity:
---------------------------------------------------------------------------------
=IF(AND(E3="Three",I3<=100),
(I3*VLOOKUP(C3,costsnetwork,10,FALSE)/100),
IF(AND(E3="Three",I3<=250),
(100*VLOOKUP(C3,costsnetwork,10,FALSE)/100)+((I3-100)*VLOOKUP(C3,costsnetwork,11,FALSE)/100),
IF(AND(E3="Three",I3<=850),
(100*VLOOKUP(C3,costsnetwork,10,FALSE)/100)+(250*VLOOKUP(C3,costsnetwork,11,FALSE)/100)+((I3-350)*VLOOKUP(C3,costsnetwork,12,FALSE)/100),
View 14 Replies
ADVERTISEMENT
May 28, 2014
formula to find specific text from statement.
I manage to use formula find for 1 text in 1 statement, but in confuse how to get another text found if i have "8 text" to find. Generally, there will only be "1 text" for each statement.
Maybe in 1 statement there have only "IC" or "Staff Tagging" or Name" or "Product" or etc till 8.
View 8 Replies
View Related
Jan 6, 2009
I have created a very long switch statement, which is too long to be placed in one row in VBA. I have attempted to put a space and underscore at the end of one line and continue the statement on the row below by placing a comma at the start of the second line. VBA will accept my efforts, but when I run the statement in the immediate window, the following error appears.
"Invalid procedure call or argument"
I understand that there are certain rules where I can split a switch statement onto two lines, yet I do not know what they may be.
View 9 Replies
View Related
Mar 4, 2014
I'm working on a spreadsheet that i have a long if statement and it keeps till me i have a error. I stated reading and come to find out you can only have 7 statement.
=IF($R$6="X",VLOOKUP($A25,VISION,2,FALSE),IF($R$7="X",VLOOKUP($A25,VISION,3,FALSE),IF($R$9="X",[code]....
View 14 Replies
View Related
Jan 5, 2010
I've attached an example of what I am trying to achieve and an explanation as well. I'm trying to avoid the use of a macro as I don't understand them and I will need to modify the sheet later on which could cause problems. However, if a macro is the only solution, then I will have to make due with one.
View 5 Replies
View Related
Sep 20, 2012
I was trying to search for a set of keywords in column1 and if the logic is true then print predefined word in column 2. The problem is I have 14 sets of keywords for which I tried creating the longest if statement only to find out that the limit is 7. Below are few of the sets.
=IF(COUNT(SEARCH({"SAN","lpfc","scsi","adap","path"},S2)),"STO")
=IF(COUNT(SEARCH({"process","nim","root/bin","agent","genhkdly","script","dbsync_dly","vcs","listener","horcm","msoffline","cpu mdmprd"},S2)),"APP")
=IF(COUNT(SEARCH({"percent","inode","space","check-kerberos","nfs","file system","jfs"},S2)),"FLS")
[Code] ....
View 3 Replies
View Related
Jul 7, 2006
I am trying to write the following statement in a cell in excel lets say O3 so I can drag it down to the rest of the column, I was trying to see if there was a previous posting about this but I didn't find it. the if statement goes like this:
=IF(M3=Sheet3!$B$2,Sheet3!$C$2,IF(Sheet1!M3=Sheet3!$B$3,Sheet3!$C$3,IF(M3=Sheet3!$B$3,Sheet3!$C$3,....until it reaches to if(Sheet3!$B$20,Sheet3!$C$20,"PENDING"))
Is there a way I can do this I know probably through a VBA but I have no idea how to set it up.
View 7 Replies
View Related
Feb 12, 2009
I have created a UDF with much appreciated help from this forum but, now I have to consider another set of variables in this UDF which I am not too sure how to put it into the codes. I hope to get some help with this. Previously I have a set of variables classified under Codes that ranges from "AI to "II" now I have to introduce another set named "Status" they are
"Accepted"
"For Acceptance"
"Rejected"
Another request if possible is the UDF can incorporate the "If funtion" formula I have used together with this UDF. The formula basically says, if Agreed Column is "blank" use the value in "Estimate".
Function AwardImpact(Code As String, Amount As Single)
Select Case Code
Case Is = "AI", "ai" 'Transfer from DD Provisional Sum (-) to Award Value(+)
AwardImpact = Amount
Case Is = "BI", "bi" 'Transfer from Award Value(-) to DD Provisional Sum (+)
AwardImpact = -Amount
Case Is = "CI", "ci" 'Transfer from Other Packages (-) to Award Value(+)
AwardImpact = Amount
Case Is = "DI", "di" 'Transfer from Award Value (-) to Other Packages Value(+)
AwardImpact = -Amount
Case Is = "HI", "hi" 'Tranfer from Provisional Sum (-) to Award Value (+)
AwardImpact = Amount
Case Else
AwardImpact = 0
End Select
End Function
View 9 Replies
View Related
Jul 31, 2012
I have an excel file as attached. I have two departments Digital and Industrial. Under this I have done a drop down option for different quarter for different projects.
When I open my excel file I just want to see the Quarter column under Digital but when I double click on Digital all other columns appear.
Second this is it possible to create a master filter or sorting for Quarter above the table so that if I select Q1 in it it will displays all the project and corresponding values in both Digital and Industrial.
Attachment 47491A.xlsx
View 4 Replies
View Related
Aug 12, 2007
This task joins a string together based on a number of characters per cell in the range.
I want to isolate one range, Col N, and add an IF condition to it.
There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference....
View 9 Replies
View Related
Jul 3, 2013
I am using the sum function to sum the following cells and the formula is too long. How can I make this work?
'Raw Data'!B3,'Raw Data'!D3,'Raw Data'!F3,'Raw Data'!H3,'Raw Data'!J3,'Raw Data'!L3,'Raw Data'!N3,'Raw Data'!P3,'Raw Data'!R3,'Raw Data'!T3,'Raw Data'!V3,'Raw Data'!X3,'Raw Data'!Z3,'Raw Data'!AB3,'Raw Data'!AD3,'Raw Data'!AF3,'Raw Data'!AH3, 'Raw Data'!AJ3, 'Raw Data'!AL3, 'Raw Data'!AN3, 'Raw Data'!AP3, 'Raw Data'!AR3, 'Raw Data'!AT3, 'Raw Data'!AV3, 'Raw Data'!AX3, 'Raw Data'!AZ3, 'Raw Data'!BB3, 'Raw Data'!BD3, 'Raw Data'!BF3, 'Raw Data'!BH3, 'Raw Data'!BJ3, 'Raw Data'!BL3, 'Raw Data'!BN3, 'Raw Data'!BP3, 'Raw Data'!BR3, 'Raw Data'!BT3, 'Raw Data'!BV3, 'Raw Data'!BX3, 'Raw Data'!BZ3, 'Raw Data'!CB3, 'Raw Data'!CD3, 'Raw Data'!CF3, 'Raw Data'!CH3, 'Raw Data'!CJ3, 'Raw Data'!CL3, 'Raw Data'!CN3, 'Raw Data'!CP3, 'Raw Data'!CR3, 'Raw Data'!CT3, 'Raw Data'!CV3, 'Raw Data'!CX3, 'Raw Data'!CZ3,
View 7 Replies
View Related
Feb 8, 2010
im not really sure what div/0's are na dhow to counter them but i understand from the error that im dividing by something that is not valid yet.
below is my formula, among others like this. i need some assistance in getting the ISERROR code in as i am struggling; ....
View 9 Replies
View Related
Nov 13, 2008
how can in compress the following formula
=IF(B3="Monthly",IF(B25="Windows 2003 Std",15,IF(B25="Windows 2003 Ent",30,IF(B25="Windows 2008 Data Center ",40,IF(B25="RHEL 5","30",IF(B25="Windows 2008 Web","15",0))))))+(IF(B3="Quarterly",IF(B25="Windows 2003 Std",40,IF(B25="Windows 2003 Ent",75,IF(B25="RHEL 5","89",IF(B25="Windows 2008 Data Center ",120,IF(B25="Windows 2008 Web","45",0))))))+(IF(B3="Semi-Annually",IF(B25="Windows 2003 Std",75,IF(B25="Windows 2003 Ent",140,IF(B25="Windows 2008 Data Center ",200,IF(B25="RHEL 5","149",IF(B25="Windows 2008 Web","90",0)))))))+(IF(B3="Annually",IF(B25="Windows 2003 Std",150,IF(B25="Windows 2003 Ent",300,IF(B25="RHEL 5","299",IF(B25="Windows 2008 Data Center ",400,IF(B25="Windows 2008 Web","180",0))))))))
View 5 Replies
View Related
Oct 31, 2009
I need to add more arguments to a countif formula, but I have maxed out the 1024 character limit.
Is there another way of writing this formula or is there a way I can add more arguments to this formula.
Formula is:
=countif(A2:A9999,"AH")+countif(A2:A9999,"ARHC")+countif...
I have a list of the arguments, can I do something like:
=countif(A2:A9999,B2:B50)
(with the list of arguments in columns B2 to B50)
View 13 Replies
View Related
May 23, 2006
Got a formula that goes like this: =IF((L1=1),"Product 1",IF((L2=2),"Product 2",IF((L2=3),"Product 3",IF((L2=4),"Product 4".... and so on for a further 100 or so products. Obviously, this formula is too long for one cell.
View 2 Replies
View Related
Aug 18, 2009
I need to write a formula but excel shows an error message "Formula Too Long." There are two long strings of the formula that I repeat several times. Is there any way to put these strings in other cells and reference them within the formula? Each of the strings looks very similar to this:
View 9 Replies
View Related
Oct 21, 2008
I want to do is copy a formula from one workbook to another workbook that I have open - have tried just copy and paste (only get the result, no formula is copied) and have tried paste special, but there doesn't seem to be anything relevant on the list. I'm sure there is a simple way of doing it.
View 2 Replies
View Related
Nov 22, 2006
I'm trying to get the max value in an array that has 3 different values that are:
array(.0027, -.0001, -.0083)
...with worksheetfunction.max(). I get the correct return, however it's 2.70000007003546E-03! How can I get it to return .0027?
View 3 Replies
View Related
Jun 12, 2014
I want to set formula to cell S1 via vba.
This is the formula: =IFERROR(VLOOKUP(H3;'[VATCompanies.xlsx]1'!$A:$B;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!$D:$E;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!$G:$H;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!$J:$K;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!$M:$N;2;0);IFERROR(VLOOKUP(F7;'[VATCompanies.xlsx]1'!$Q:$R;2;0);I7))))))
View 8 Replies
View Related
Jan 29, 2010
Is there a work around to pull data from cells in excel to another tab with standard language? For example the template language will stay the same except in example A's case the last sentence "0.0" hrs will vary based what's sitting in B34.
It's stating the formula is too long. Is there too much text?
Sometimes the link would be in the middle of the text as shown in example B below with dates 00/00/00.
EXAMPLE A
For any leave of absence that is granted for more than 10 days, time off is governed under the Disability Bank Policy. Under the Disability Bank Policy there is a 10 day elimination period which must be satisfied prior to gaining access to your disability bank. During this 10-day elimination period, you are required to use your sick time for the first 6 days (48.00 hours) and vacation time for the remaining 4 days (32.00 hours). After this elimination period, we use all the time accrued in your disability bank. Currently, you have a disability bank balance of "00" hours.
EXAMPLE B
After this disability bank is exhausted, you will use all remaining and future paid time off balances from your vacation/CME/holiday banks. Because your leave begins in 00/00/00 and ends in 00/00/00 we will advance you future paid time off balance thru 00/00/00. Once those balances are exhausted the remainder of the leave will be unpaid. Per our policy, should you not return to work, you agree to pay back any time advanced to you within 30 days of your separation from the company.
View 9 Replies
View Related
Oct 8, 2007
I'm receiving this message when I attempt to use the Find, Replace function. Formula is too long". I have a column of cells containing text only. ( about 2-3 paragraphs worth) I'm trying to replace a name with another name, which works fine where the cell contains a single or few sentences, but fails to replace when the cell contains too much information.
View 8 Replies
View Related
Nov 25, 2008
I am working on using an excel workbook as part of a roll playing game and I am trying to record a macro and then put that in a command button which uses the randbetween, multiple if statements and multiple vlookup functions. I have successfully done similar things by just recording a macro and then pasting that into the VB editor. However this time I get a unable to record macro after putting the formula in the selected cell. I have tried copy and paste and just typing the formula with the same results.
Upon executing the formula I then want it to copy and paste special- values the result.
I'm assuming I have to put the VB code in manually but when I tried to do the formula I got a syntax error.
The worksheet I am working on is named new and I've included the formula in the a text box. I would like the result to show in J12 and be triggered by pressing the cmnbutton in k12.
View 9 Replies
View Related
Feb 12, 2010
I am using the below formula to distinctly count the number of customers that match the criteria that I have in Cells C7 and B10. The data is in a separate worksheet, that I am showing Named Detail of which will be changing on a monthly basis, so a pivot table does not want to be used. The detail data ranges from row 7-40,000, and the file is currently 8610KB's, and can potentially grow.
=SUM(IF(FREQUENCY(IF(Detail!$A$7:$A$40000=C$7,IF(Detail!$B$7:$B$40000=$B10,IF(Detail!$D$7:$D$40000<> "",MATCH(Detail!$D$7:$D$40000,Detail!$D$7:$D$40000,0)))),ROW(Cust)-MIN(ROW(Detail!$D$7:$D$40000))+1),1))
This formula works but takes an excessive amount of time for one caluclation, and I need this for multiple column and row critera. So, can this calculation be changed in order to get the same result with faster calculation time? I am using Excel 2003.
View 6 Replies
View Related
Mar 15, 2012
I get distinct word with this formula i have 30,000 rows with below formula taking lot of time with dragging to all cell
{=INDEX(List,MATCH(0,COUNTIF($b$1:b1,List),0))}
View 5 Replies
View Related
Jan 22, 2010
I have a formula I've been using for a long time which uses VLOOKUP to find results based on various reference cells, and then adds them up. To avoid errors caused by VLOOKUP not finding anything for one of the references I have also used ISERROR. The formula returns a blank if the calculation returns a zero.
I now need to bring further references in to the calculation but, using the format I have been, the formula is now too long. Here is my extensive formula:
View 3 Replies
View Related
May 8, 2012
I need a formula that will search the range D8-D100 to confirm that all cells within that range are either 11 characters in length or blank. I will use it inside of an error message that will look something like this:
=IF(****formula that checks to make sure all of the cells in that range are blank or 11 characters****=TRUE, "", "Please make sure that all cells are 11 digits or blank")
View 7 Replies
View Related
May 15, 2007
I have a large spreadsheet, within which i am trying to remove commas from all cells. I get the error 'formula is too long' when I carry out the search. Some of the cells are >1024 characters in length and contain dates, text etc.
View 5 Replies
View Related
Jul 9, 2009
I have worked on this really difficult long formula, but the problem is, I have been working from my Mac. And my office, which this formula was made for, is all PC. Now generally this isn't a problem, but apparently PC's have a limit on how long the formula can be. Is there anyway to overcome this? I really need this formula to work..
View 9 Replies
View Related
Mar 30, 2009
How do you get around this? I only have TWO criteria!
The list is A1:T1000 in size.
View 9 Replies
View Related
Dec 19, 2008
I currently use the following formula: =workday(H21,H23,J20:J34) which calculates my date based the date entered in cell H21. While testing I realized that I needed to add another variable to the formula for a specific scenario. When my Date in H21 is less than todays date I require another workday formula to be calculated. How can I add this if statement to the existing formula?
View 2 Replies
View Related
