I am trying to use a formula with 13 nested IFs in Excel 2007, which is supposed to allow up to 64 nestings. But I get a message that the formula "uses more levels of nesting than are allowed in the current format" whether I am using xlsx, xlsm or xlsb.
View 3 Replies
I'm building a model that based on the start date (cell C7) needs to display the month of the project (M1, M2, M3, M4, ... M12) under the appropriate calendar month (Dec-07, Jan-08, etc.). The formula below works great (E4 is the calendar month Dec-07 in this case, and when I drag the formula horizontally it updates (F4, G4, etc.). B33 through B44 are my month references M1, M2, M3, etc.)
Here's the problem: The below statement contains seven nested ifs and Excel won't let me add additional if statements. How else can this be accomplished?
=IF(AND(E4>=$C7,E4=$C7,E4=$C7,E4=$C7,E4=$C7,E4=$C7,E4
I have data in a sheet in Excel 2007 that I am trying to move to another sheet in the same workbook.
I am using Vlookup to move the info from one sheet to the other.
However it seems that if the data in the original sheet cell is too large I get a #VALUE! error.
If I copy and paste the info from the cell it is fine.
How do I copy the information (hyperlink) in the original sheet cell to the destination sheet cell?
Here is the formula that I am using.
=HYPERLINK(VLOOKUP(A67,'verified1-4-12'!B:E,4,0))
verified1-4-12 is the original sheet name
Is there a way I can get excel (2007) to limit itself to 2 decimal places for all numbers entered? So effectively I don't want use the formulas TRUNC or ROUND (or any other formula) but I would like rather excel to behave like my calculator where I can limit the decimals to 2 no matter how many I input.
In other words, every time a input a number, excel will round it up automatically to 2 decimal places and the number available will have 2 decimals only. Is there any option hidden somewhere in the Excel options that I couldn't find?
I am trying to create multiple pivot tables from the same pivotcache using VBA. The data range is approximately 270,000 records with 100 columns. When I run the macro, I get "Run-time error '-2147352567 (80020009)' Method 'Add' of object 'PivotCaches' failed". Is there a size limit on the data? If there is less than 65,000 records, it works great; if I use a wizard, it works with no problems with the full dataset. If I bring the data in as an Access table, it can work with some tweaking of the code, but the size of the file is huge because each pivot is taking its own snapshot of the data. I also can only create 3 tables before it runs out of memory, so I have to stop, save the file, and open it back up to create 3 more pivots.
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
[Code] .........
The last line is where I am getting the error. I am running XP, Excel 2007.
As I said, if I import an Access table, I can use the code:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table_Pivot_test.accdb[#All]", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="", TableName:=txtPivotTableName, DefaultVersion:=xlPivotTableVersion12
And loop, but the final file size with 14 pivots can be 500MB! I have tried both "Create" and "Add", but they both give me errors.
Im having some isses nesting two arguments together.
basically(ha, ha), id like to do the following operation in cell J9:
If the Actual Date (F9) is blank leave the cell blank, and if the actual date is blank is the target date is less than NOW(), leave it blank.
If the Actual date is not blank, do Actual minus Target, and if NOW() is greater then the target date, do now() minus target.
if(F9="", ""
if(E9 < NOW(), ""
AND
if(F9=<>, F9 -E9
if(NOW()>E9, NOW()-E9
I have attached a spreadsheet to illustrate.
I tried using this array formula but it has an error in it and I can't figure out the error. =SUM(IF(Tracking!$C$2:$C$5000=$B$4,IF((Tracking!$D$2:$D$5000="15-M Eval")+(Tracking!$D$2:$D$5000="15-M TIC")+(Tracking!$D$2:$D$5000="Misd CRT"),IF(Tracking!$E$2:$E$5000=$A6,IF(Tracking!$J$2:$J$5000>=VALUE($B$1),IF(Tracking!$J$2:$J$5000<=V ALUE($B$2),1,0)))))) (I attached the workbook. In the CSTE worksheet, I didn't use control-shift-enter so you could see the formula.)
What I'm trying to do is come up with a formula that will sum if either of these three Legal Status types (15-M Eval, 15-M TIC, and Misd CRT) are present on the Tracking worksheet with a specified Evaluator within a certain date range. The formula works if I just want to find one Legal Status type. I also will want to come up with a formula that will sum if either of these five Legal Status types (15/30-F Eval, 15-F TIC, 1/90 CRT, 2/90 CRT, and 180 CRT) are present on the Tracking worksheet with a specified Evaluator within a certain date range. I tried researching if I could define a name that would sum these 3 Legal Status types but that didn't work either.
I got a 2 columns (A:B) with values in workbook1 (survey2.xlsm)
I want to open a sheet named according to values from column A wb1 in workbook2 (du_database2)
And I want to fill in the offset values (column B) in the aproppriate sheet
I wrote this sub for it. I am having troubles with the for each statements (how do I set them up properly?). The script itself hasn't been checked might also been wrong there.
VB:
Sub copytest()
'by J
Dim ws As Worksheet
Workbooks.Open "survey2.xlsm"
Application.Workbooks("du_database2.xlsm").Activate
[Code] .....
I am trying to add up a specfic column in Excel based on the parameters or two different ones.
I.e If Col B is blank and Col D is 03 then add up the value in Col P
But I can't seem to work out how to do it, I have tried conditional sums and everything else I can think of, but it is still not working.
I am trying to create a formula with mutiple criterias to come back with results of whether that person is Eligible or Not Eligible for stock. The criteria to be Eligible is as follows:
1) Hire Date before 1st March 2007
2) Band 1 and the performance review = Exceeded
3) If Band 2-5 then performance review must be either Achieved or Exceeded
See attached file for the example. I have got as far as the first 2 points but i am struggling with incorporating the last point.
Im recieving an error saying that i have too many levels of nesting for my formula
Is there any way to rewrite the formula i have here?
=if(I1>99,0.5,
if(I1>89,0.45,
if(I1>79,0.4,[code].....
I know how to do a vlookup and I know how to do an if statement. I also know how to do an IF(ISERROR or IF(ISNA ....
However, I can not get this to work:
=IF(C73<7,VLOOKUP(D73,'Package Mix'!$G$39:$H$45,2,FALSE),IF(C73<13,VLOOKUP(D73,'Package Mix'!$G$6:$H$36,2,FALSE),IF(C73>23,VLOOKUP(D73,'Package Mix'!$G$48:$H$56,2,FALSE),"")))
I can not wrap my head around how to make this work w/ IF(ISNA ....
Also, why must you use < or > .... I actually want the numbers to be 6,12, and 24... However, it seems when I use an equal sign it fails.
I have two cells that each contain their own lists (listA=A1, listB=B1). I'm trying to figure out how to say:
If the selection from (listA= X AND listB=W )or the selection from listB=Y output 0, otherwise output data from C1.
I had the first and third argument working properly but am running into difficulty when I tried to enter the middle formula.
If A1 + B1 = 0 then "0"
OR
If A1 = 0 AND B1 is greater than 0 then give B1's value times .01
If neither of those are true Then A1 minus B1, divide the result by A1 and then multiply it by negative 1.
=IF((A1+B1=0),("0%")*OR,IF((A1=0 and B1>0),(B1*0.01),((A1-B1)/AI*-1)))
I've followed several tips on how to match these formulas, but somehow cannot make them work. I'm using Open office's version of excel. I need a formula that searches on 'tension downfall' for data in the first sheet, and then return a value corresponding to the row and column that match the input on the first sheet.
'tension downfall' shows a double entry table which shows amperage against length and returns a wire size. I would like to input amperage and length on two different cells and have the wire size needed for those values. Is it clear enough? I'm attaching my current work to make it a little bit clearer.
I need to create an 'IF' function that nests four EOMONTH functions.
The following throws an error:
=IF(D12="1st month goes here",EOMONTH(D7,2),if(D12="2nd month goes here",EOMONTH(D7,3),if(D12="3rd month goes here",EOMONTH(D7,5)))
EOMONTH will increase the date in the resultant cell accordingly.
I am having issues writing the VBA code for IF statements. I am essentially trying to nest IF statements within one another. I have it done through the regular If functions as seen in the attachment, but I cannot seem to get it to work with VBA.
I need it to work in VBA without knowing how many rows of data there are. In the attachment you will see the rules that I need the data follow, but here is an example. Essentially, depending upon how large cell C2 is, I need to add a certain amount of months to whatever is in E2.
=IF(C2="","",IF(C2<500000,DATE(YEAR(E2),MONTH(E2)+1,DAY(E2)),IF(AND(C2>=500000,C2<1000000),DATE(YEAR (E2),MONTH(E2)+1,DAY(E2)),IF(AND(C2>=1000000,C2<=2000000),DATE(YEAR(E2),MONTH(E2)+1,DAY(E2)),IF(AND( C2>2000000,D2="Critical"),DATE(YEAR(E2),MONTH(E2)+2,DAY(E2)),IF(C2>2000000,DATE(YEAR(E2),MONTH(E2)+1 ,DAY(E2)),"Reevaluate"))))))
I have a new job a part of which is where I have to provide reports using data from excel 2003 sheets that are sent to me by someone else. I add my own formulas to get the data I need. One such bit of data is. How many rows have "yes" in col E and "w" in range M:V?
View 3 Replies View RelatedThe enclosed spreadsheet includes a couple formulas I’m having trouble with.
The questions themselves are detailed in the document.
I'm trying to return the value of "true" of "false" if a time in a cell falls within 2 time periods I've specified.
=IF(AND(D6>=MorningChargeStart,D6=EveningChargeStart,D6
I have the following Formula:
=SUMIF('Sheet2'!C:C;Sheet1!B3;'Sheet2'!E:E)
In this way I got the sum of all items located in column E wich have the same value than in B3 in Sheet 1....
How could I change the formula in the way that only returns the Values in Sheet2 E:E when (If equals to B2 and C2 in Sheet 1 then Sumif)
The value which I want to use to filter is in the same columns in both sheets
I want to accomplish a few things in one cell. The basics are this:
I want to pull the lowest numbers from a given sample. The sample needs to have a time component (10 lowest out of the most recent 20 results). Those numbers are to be averaged. That average is to be multiplied by .96, then that final yield needs to be truncated to 1 decimal.
Now, I'm not sure this is possible or not. But, if it is, I want to also select the amount of numbers I pull from the sample to average to be based on the sample size itself. For example, if there are 10 results in the sample size, I only want to average the lowest 3 of the 10.
Is it possible to formulate all of these rules into one cell?
I need a formula that will determine if a line is active or expired by first looking at the category and then looking at the number of days. (While still incorporating the ISBLANK section of the current formula.)
For Example: If the category is either FWW or FWW Ext then the line will expire after 365 days. If not, then it will expire after 180 days.
I've attached a sample spreadsheet.
Book2.xlsx
I am currently using the STDEV formula. Is there a way, that i can make
the formula use data from nonblank cells, only. For example: A1, A2, A5 will
have numbers listed. A3 & A4 are blank. Can i get the formula to use only
A1, A2, & A5?
In column A I have the debt name i.e. the company that owes us money.
In column B I state the amount owed to us
In column C the month by which the income is due. This is selected from a drop down list: M1, M2, M3 etc up to M12
In columns D through O, I have the forecast of what payments will be recieved in months 1-12
In the next cell I want to total payment that will be recieved by the due date. So, if the the cell in Coumn C says M5, then it would just add up the amount forecast in months 1-5, but if the first cell equals M2, then it would add up the forecats for M1 and M2. What formula do I put in here?
e.g. Manually enteres numbers would look like this, but I need a formula for cells in column P
A
B
C
D
E
F
G
[Code] .......
The only way I can think of doing this is with nested IFs but the limit is 7 and I need 12. I have used 12 monhts here for example. In fact on the real version I have 48 months and obviously can;t do 48 nested IFs.
=IF(E18>760,(">760"),(IF(E18>550,("550-760"),(IF(E18>365,("365-550"),(IF(E18>210,("210-365"),(IF(E18>120,("120-210"),(IF(E18>90,("90-120"),(IF(E18>60,("60-90"),(if (E18>30, ("1-30"), (0))))))))))))))))
If you try to use this it will not work, as excel takes only 7 arguments
can someone help me with this. Do any one know macro for the same.
if you use this formule it will work
=IF(E18>760,(">760"),(IF(E18>550,("550-760"),(IF(E18>365,("365-550"),(IF(E18>210,("210-365"),(IF(E18>120,("120-210"),(IF(E18>90,("90-120"),(IF(E18>60,("60-90"),(0))))))))))))))
I have this search function that works well: =IF(ISNUMBER(SEARCH("which is < ",E9)),"test ok", "not ok") my E9 cell contains the phase: "I have a number which is < 9, and which is positive..." E9 has "which is < ", then i get back "test ok"
My question is.... how can I do this multiple times within the same function/cell. i.e. if I wanted to search "which is < " and/or "positive" in the same cell, then i get back "test ok", "positive number") I keep trying all combinations to no avail, like: =IF(ISNUMBER(SEARCH("which is < ",E9)),"test ok", "not ok"), =IF(ISNUMBER(SEARCH("positive",E9)),"positive number", "no good")
Keep in mind I am using a numeric example, although I am using this function to check if part of a cell matches specific text...
Is it possible to nest the two functions of match & offset. I am working with two worksheets. One is a constant the other is building. I want to match a text from the first spreadsheet in the second sheet (otherwords I want to find that exact within the other spreadsheet) then I want to retreive the offset value to the right of the matched cell and place it in the constant worksheet where the formula is.
For Example
Sheet 1 Sheet2
Company: First Name: Company: Name:
Adamack's ? Adamack's Patrick
So if I put the formula where the ? mark is, I want to match the first company name on sheet 1, from the range in sheet 2, then I want to get the offset from that match 0,1 to give me the name patrick.
I am using the ISNUMBER formula to verify if specific text is found in a string of text.
This formula worked when I only needed to find 1 particular brand name:
=IF(ISNUMBER(SEARCH(""BrandB"",RC[6])),""B"",""C"")
Now I need to have 2 different brand names ("BrandB" and "J & B") that need to return a "B" and all others should return a "C".
I found a thread regarding this same topic, but I could not get their solution to work for me.
They recommended using:
=IF(ISNUMBER(SEARCH("BrandB",RC[6])),"B","C")&", "&IF(ISNUMBER(SEARCH("J & B",RC[6])),"B","C")
The fact that my second brand name is written with spaces ("J & B") could be adding to the problem, I don't know. I thought replacing the "C" with another "IF(ISNUMBER" function should work, but I have been unsuccessful at writing a formula that doesn't cause run-time errors.
In column G, users select one of three values. I want a list of additional options (my named ranges) to appear in column H based on the selection of column G. I can get this to work when just referring to one of the named ranges, but not all three. I also do not want any of the data to appear if there is no selection in column G.
My not quite right code:IF($G$5="Code of Conduct",COCList,IF($G$5="Integrity",INTList,IF($G$5="Behavior",BEHList,"")))
