Nested If Formula, Lookups, Named Formulas
Jul 27, 2006
On a spreadsheet, I want my formula to caculate the following: if "certain cell" = "certain name", then do this calculation, if "certain cell" = "certain name", then do this calculation....and so on.
I have a drop down list of employees, which would be "certain name" and it is in a cell, which would be "certain cell". The calculation I want to do is if it is a certain employee, I want to take the manually inputted hours in another cell and multiple it by the employee's rate of pay, which would be it's own calculation including labour burden. I have 13 employees that I want to have as part of this. So, if the first valuation comes up false, it carries on to the next and so on until it finds one that matches. I have tried IF commands, but it is too long, so I tried to split it, didn't work. Tried lookup and it didn't work. Tried named formulas, didn't work. It is entirely possible that I am totally on the wrong track or in over my head, but I know this should work.
View 6 Replies
ADVERTISEMENT
Sep 23, 2006
creating Named Formulas to handle nested IF & And statements, to get around excel's limit of 7 nested IF statements.
First of all, is it possible to create a Named Formula with combined IF with AND statement such as: =IF(AND(F26>=54,F26<=77.99),"2x3"," ")--I am getting a periodic error message: "Cell with block IF function should not contain anything else."?
I am trying to: 1. Contstruct two separate Mega formulas using IF & AND to determine what range of sizes , located in Col "F", should fall into which SIZE_CAT, sample of ranges are as follows:
SIZE_CATSIZE IN TOTAL INCHES
2X3 54 thru 77.99
3X5 78 thru 108
4X6 108 thru 138
2. Name these two formulas, FirstNamed and SecondNamed
FirstNamed formula is:=IF(AND(F26>=54,F26<=77.99),"2x3",IF(AND(F26>=78,F26<=108),"3x5",IF(AND($F26>=108,$F26<=138),"4x6",IF(AND($F26>=138.01,$F26<=168),"5x8",""))))
SecondNamed formula is:=IF(AND($F30>=168.01,$F30<=198),"6x9",IF(AND($F30>=198.01,$F30<=234),"8x10",IF(AND($F30>=234.01,$F30<=270),"9x12",IF(AND($F30>=270.01,$F30<=320),"10x14",IF(AND($F30>=320.01,$F30<=500),"Larger","")))))
3. Combine two named formulas with a "Master Formula" such as:
=IF(FirstNamed,FirstNamed,SecondNamed)
I got the IF with AND formulas to work in a test columns, but cannot seem to get them to respond properly when trying to get them into a Named Formula(s).
I have attached an example of the workbook.
View 3 Replies
View Related
Dec 30, 2009
I've got an Excel 2003 sheet where column E has a 6-digit code input into it. I'm able to right a function off that code that outputs if E2 (for example) equals 123456 then H2 outputs "Dog," if E2 equals 123457 then H2 outputs "Cat," and so on until I reach the max of 7 nested functions. Problem is, I have a lot more than 7 codes that I need to analyze. This Excel sheet will be used every two weeks, with lots of codes input that need to have what the code means output. I created a named formula called "part1" where: =if(sheet1!E2=123456,"Dog",if(sheet1!E2=123457,"Cat", etc. It didn't work when I had $ in front of E & 2.
All variables in "part1" work when tested where H2 = part1. I created a "part2" with more variables, checked each and it works too. My problem now is that when I have H2 set to =if(part1,part1,part2) to test all the variables, it'll work on any variable in "part2" but not in "part1."
View 3 Replies
View Related
Dec 21, 2006
=IF(E23="A",4,IF(E23="A-",3.67,IF(E23="B+",3.33,IF(E23="B",3,IF(E23="B-",2.67,IF(E23="C+",2.33,IF(E23="C",2,IF(E23="C-",1.67)
Obviously im writing a script that correlates letter grades with GPA values. The problem is, after i try to put in the next grade (a D-) it throws up an error saying "the formula you typed contains an error". Is there some kind of limit to how many IF THEN statements you can put in a script? if so is there another easier way to what im trying to do?
View 4 Replies
View Related
Apr 13, 2007
I'm working on a formula that will determine the lowest number in a range.
The complication is that the range varies based on how many rows match a certain criteria.
The formula I have created works correctly, however, I need to have the range go up to 10, and because of the limit of 7 nested IF's, I have to stop at 6.
=IF(A2=A1,"",IF(COUNTIF($A:$A,A2)=1,G2,IF(COUNTIF($A:$A,A2)=2,MIN(G2:G3),IF(COUNTIF($A:$A,A2)=3,MIN(G2:G4),IF(COUNTIF($A:$A,A2)=4,MI N(G2:G5),IF(COUNTIF($A:$A,A2)=5,MIN(G2:G6),IF(COUNTIF($A:$A,A2)=6,MIN(G2:G7),"")))))))
After a result is returned from this formula, I have to do the almost exact same formula, based on the min value from these results.
View 9 Replies
View Related
May 28, 2007
I undrstand that you can only ave a certain amount of nested IF statements within one formular, is there a way round this?
What i'm looking to do is this.
VLOOKUP(VLOOKUP(location,IF(postcode="BA",LocationBA,IF(postcode=" BS",LocationBS,)),2,FALSE)
I have 13 options that can be chosen, but only have room for 6 nests of which BA and BS are currently above. what would be the best way to have all 13 within the lookup table array?
View 9 Replies
View Related
Dec 8, 2009
I receive montly investment performance projections going out monthly for 10 years. I need to show the return annually for each year. I created a SUMIFS formula to sum the percentages between two date ranges (beginning of the year and the end of the year for each year). However, my SUMIFS formula does not take into considersation compounding. How can I modify the SUMIFS formula I created to take into consideration compounding?
I know how to convert monthly percentages to an annual percentage,using the PRODUCT formula =PRODUCT(1+T15,1+U15,1+V15,1+W15,1+X15,1+Y15,1+Z15,1+AA15,1+AB15,1+AC15,1+AD15)-1, whereas T15:AD15 is the monthly percentage gain. Is there a simplier/alternative way that I can incorporate into the SUMIFS formula?
If it is too complicated to utilize a SUMIFS formula, I would like to be able to select a range of cells and have Excel calculate the result vs typing "1+[select the cell]," for each month. It takes me forever and open to input errors. It would be much easier to be able to select a range of cells when creating the formula.
View 3 Replies
View Related
Jun 12, 2006
I need to combine the following formulas into one but obviously it won't work due to the limitations. Would the following be better using VBA? If yes. At the moment, they are in 3 separate columns and working but indepenantly. It basically says, if the agreement is older than todays date, write expired, if not if its for 12 months, then calculate the percantage according to number of days. and so on for 24 months and 36 months...................
View 2 Replies
View Related
May 23, 2008
I am currently having trouble with what I thought was a simple IF statement. As you will see from the formula I want to complete a statement for every month of the year but Excel will not let me go past July. Is there a limit to the number of arguments in an IF statement and how do I overcome this? =IF($A$3="Jan'08", SUM(C7),IF($A$3="Feb'08",SUM(C7:D7),IF($A$3="Mar'08",SUM(C7:E7),IF($A$3="Apr'08",SUM(C7:F7),IF($A$3="May'08",SUM(C7:G7),IF($A$3="Jun'08",SUM(C7:H7),IF($A$3="Jul'08",SUM(C7:I7),"n/a")))))))
View 5 Replies
View Related
Dec 19, 2006
i want to do I have a list of data like this
Machine Date Value
A 1 1.1
A 2 1.2
A 3 1.3
B 1 1.1
B 2 1.2
B 3 1.3
C 1 1.1
C 2 1.2
C 3 1.3
This is imported from else were and AutoFilter by machine then number and value every time a new record is added to the list.
I want to have three charts in the sheet (one for A, one for B and one for C) and i need to be able to set the data source ranges dynamically.
I have a macro i mind that will search the first column cells one at a time. something like:
Sub marco1()
'find A limit
range("a1").select
For row=1 To 100 Step 1
If cells(row,1).value<>"A" Then
ARowFin = row-1
exit For
End If
Next row
End Sub
This will find the last "A" cell's reference by index numbers as cell(ARowFin,1) But i can't find how to then use this format to select a range.
The "range" function only uses A1 notation for multiple cell selection. How do i use index notation to set a range?
If i can work this out then i can set: r1 = that range and a second range r2 in a similar fashion, then set the data source range as a union of these ranges.
View 3 Replies
View Related
Apr 11, 2014
I have the following formula.
=SUMIFS('Register Sales'!$E$51:$C$2500,'Register Sales'!$C$51:,A2,'Register Sales'!$L$51:$L$2500,D2)
The range changes each month so I want to use a named cell "LastRow" where I can change the number to replace the 2500. How do I use the range name in my formula without giving me a #value# error?
View 1 Replies
View Related
Dec 23, 2008
i have an excel sheet with two columns, and depending on the status of the equipment, i need to compare the two columns and if the data is the same, fine, otherwise i need to display a msg box with an error. the columns have formulas in them, and i assigned a name to the cells i want to check, i keep getting errors with the following code, when the colums are the same, it is still displaying the msg box with the error. what am i doing wrong?
For Each Row In Range("Dev_Found") 'Loop through each row in Column C
If Row.Value ActiveWorkbook.Names.Item("Dev_Left").Value Then
blah = MsgBox("Your % Dev for after does not match % Dev before, please correct on form!", vbOKOnly, "Error")
Exit For
End If
Next Row
View 9 Replies
View Related
Apr 18, 2006
I am wanting to build a macro to calculate the average of a range of cells. I have about 2000 lines of data, and I want to average the first 12 cells (then paste the answer somewhere else), then average the next 12 cells, and so on. Using a loop to do this is simple enough. My problem is that I can't insert variables into the average formula as the cells to be averaged
Sub AutoAverage()
For x = 0 To 20
For y = 0 To 171
FirstRowRef = Workbooks("NP FT01-03 010206.xls"). Sheets("NP - FT01") _
.Range("a5").Offset(12 * y, 12 * x)
LastRowRef = Workbooks("NP FT01-03 010206.xls").Sheets("NP - FT01") _
.Range("a16").Offset(12 * y, 12 * x)
Workbooks("Mega Spectrums.xls").Sheets("NP - FT01").Range("a5").Offset(y, x).Select
ActiveCell.FormulaR1C1 = "=AVERAGE(FirstRowRef:LastRowRef)"
Next y
Next x
End Sub
View 2 Replies
View Related
Sep 29, 2006
I have been handed a workbook which has been used and amended by a number of people over about 5 years. The workbook has lots of named ranges, many of which are obviously now defunct. Others may be defunct but I'm not sure. I would like to delete all of the defunct ones.
Does anyone know a way in which I can identify the cells in which a named range is currently being used?
View 8 Replies
View Related
Dec 30, 2013
I have a Dynamic Named Range (Entries) that shows in my Name Manager...but is not being recognized in formulas.
The range is defined with a standard DNR formula, and selecting the code in Name Manager highlights the Dynamic Range.
[Code].....
But, when I use the NAME in a simple formula, I get an error. (#NAME?)
[Code] .....
View 6 Replies
View Related
Jun 17, 2009
Formula: B2+C2
In columnD I want to reference the "formula" and have it calculate based on the values in whatever row references the formula.
As it stands I can only get the formula to calculate within the same row.
View 12 Replies
View Related
Jan 25, 2012
I have a workbook with several sheets and named ranges like ES_Date, ES_Range, ES_Volume or MC_Date, MC_Range, MC_Volume. The 1st two letters are the name of the sheet where the named ranges are located (one is dates, another volume, etc.).
What I'm trying to do is calculate averages, extract maximum and minimum values from the the named ranges using a start and end date that are entered in cells "H6" and "I6". The formula I am using right now is:
{=AVERAGE(IF((ES_Date>=H6)*(ES_Date
View 2 Replies
View Related
Feb 5, 2012
I am really struggling with the following formula:
MATCH(MIN(ABS(Ann_TaylorRegularBust-B4)),ABS(Ann_TaylorRegularBust-B4),0)
I am trying to find the cell in the named row "Ann_TaylorRegularBust" that is closest to the input bust size in cell B4. This formula works when I use actual range instead of the named array.
View 5 Replies
View Related
Nov 17, 2007
I need to select in a sheet the cells which have name reference to see if the whole sheet is dependent , i have tried to trace the dependents and precedents but that didnt work,
View 5 Replies
View Related
Aug 24, 2014
I am trying to use a combination of Concatenate and IF formula to produce an email.
My input.
D4: First Name:
D5: Middle Name:
D6: Last Name:
D8: Organization:
Once all these are filled, I want the formula to produce a result like
FirstName.M.LastName@Organization.com
I have used the following formula.
[Code] .....
Problem is if there is a middle name the formula works fine, but in case where there is no middle name, it produces the following result.
FirstName..LastName@Organization.com
How do I remove the additional (.) in cases where there is no middle name.
Attached File : Email Generator.xlsx
View 3 Replies
View Related
Jan 16, 2014
Is it possible to hide formulas from the formula bar while still having the formulas active?
View 8 Replies
View Related
Feb 20, 2009
I have a rather large formula that I put together and it's shown in column 'C' in the small sample file I have attached. I'd like to add one more criteria such that I avoid duplicate solutions in column 'C'. i.e. I only want the first occurrence of a group.
The formula works fine 'bucketing' the first entry. If I have an solution in C I want the formula to not give me another answer if the cell immediately above has an answer. In other words, rows 11 and 15 would be blank because rows 10 and 14 have an answer.
I've tried inserting another criteria in the AND statement, like ISBLANK for the cell above, but I can't get it to work. What am I doing wrong, OR is there a simpler solution?
View 2 Replies
View Related
Sep 17, 2009
=IF(C2="Windows",B2,IF((AND(A2=A3,C3="Windows")),B3,IF((AND(A3=A4,C4="Windows")),B4,IF((AND(A4=A5,C5="Windows")),B5,IF((AND(A5=A6,C6="Windows")),B6,IF((AND(A6=A7,C7="Windows")),B7,IF((AND(A7=A8,C8="Windows")),B8,"None")))))))Here is a link to the spreadsheet (D2 being the formula cell):
http://img199.imageshack.us/img199/8500/29072008.jpg
Basically, I am trying to put the columns data (if it has a porch, conservatory, windows) into one row for the contract number, as the dump which I have, is giving me seperate row's instead of one.
I am trying to get the formula to check if the contract number matches on the following row, and if it does, to check if the Product Description matches the header of that row (if it's a window, conservatory) if so, copy the quantity cell into D1, however, it's copying it even if the contract numbers do not match and I do not know where I am going wrong!!
View 9 Replies
View Related
Feb 20, 2009
Objective: To find out which customers order certain items and which customers dont order certain items. Many customers may order the same item eg customer A, B, C, D all order item "4567"
I have 2 worksheets.
Worksheet 1: Showing 30 item codes, item description and customers. Items in col A (A2:A31), description in col B (B2:B31) runnning down vertically. Customer name in cell 1 of all other columns running across horizontally, eg C1, D1, E1.... (C1:GF1). There are 186 customers. (A formula needs to start at C3 and dragged to GF3)
Worksheet 2: Raw data showing customers in column A and items in column B, There are 3,753 rows. Customer in column A are duplicated as the same customer may order a number of items so for eg
ABC PTY LTD 5671
ABC PTY LTD 5683
ABC PTY LTD 5692
ABC PTY LTD 5610
ABC PTY LTD 5611
Tried the below formula
=if(C1='S2'!A2),=if('S1'!A2='S2'!B2),"ordered","")
View 8 Replies
View Related
Apr 1, 2009
I have a spreadsheet which contains dates (amongst other info) of when a specific form was completed for a client. We have 'date windows' which these should fall into. I'm really struggling with how to work this one out and whether to use nested IF's or try some kind of code (although my vb knowledge kind of stops after recording a macro). Bascially I need to know for each 'date window' if the window has been met or missed. There may be up to 30 different dates per person (in my example sheet I have up to 9).
View 2 Replies
View Related
Aug 7, 2009
He works at state tax administration. He had this formula at work to calculate income tax.
View 3 Replies
View Related
Aug 20, 2008
I currently use 3 different formulas to return a value I want. The first one is =LEN to return a value. I then want to decuct 1 from this length value so I just use =A2-1 in the next cell. I then use =right so my returned result is always the X right most characters -1. Is there a way I can nest all these formulas so I'm getting the desired result in just one cell?
View 3 Replies
View Related
Nov 18, 2012
where the issue with the following formula is?
=IF(AND($E$2="N",$AA$550),"BT1",IF(AND(Z5="F",Y5>=0),"CLEAR",IF($AA$5
View 2 Replies
View Related
Jul 23, 2013
I have to write a formula which states the following:
if cells AA1,AB1 &AC1 = 0 then "Slow-Moving",
if of these cells AA1,AB1 &AC1 contains a number then "OK",
if cells, AA1,AB1,AC1,Z1,X1,Y1 all = 0 then "Non-Moving"
I believe an If and AND combination could work but its not working for me.
View 8 Replies
View Related
Nov 1, 2006
I have the following IF formula. It is working fine except the underlined part; it does not returned the value 15 ...
View 4 Replies
View Related
