Another IF Function With Multiple Conditions?
Mar 1, 2014
My spreadsheet looks like this:
Column A: Individual
Column B: Not used in formula
Column C: Not used in formula
Column D: Location
Column E: Duration in Minutes
Column F: Pay (Column E)*(Column G)
Column G: Formula
(simplified version listed below)
[Code].....
To be clear of what I'm trying to achieve:
When in location 1 & 2 (Column D), the pay for each individual is a different rate depending on the duration in Column E, but when in Location 3, the rate is consistent no matter what the duration.
My question is:
How do account for individuals who aren't individual1? That is, my understanding of the above formula is that I need to list each individual that will appear in Column A for it to work. What if I just want to apply the above to individual1, but apply some other calculations to all other individuals?
I had tried adding these lines (with their corresponding closed-parentheses at the end)
[Code]....
But it doesn't work. I'm guessing it's some sort of OR, but I don't yet grasp how.
I should note that my formulas are all on one line in my spreadsheet, I just have them on separate lines to make it easier to find my way around.
View 14 Replies
ADVERTISEMENT
Jan 5, 2010
I am having serious trouble trying to make this work:
I need to create an IF function for the following:
If B13
View 9 Replies
View Related
Jun 7, 2013
I am trying to write a code in excel vba where I need to match specific text in multiple columns and if text is matched then I need to insert new blank column after the specific text.
My code is like this.
Dim Col_num As Integer
Col_num = Application.WorksheetFunction.Match("Customer Defined 06", Range("6:6"), 0) + 1
Columns(Col_num).Select
Selection.EntireColumn.Insert
Cells(6, Col_num).Select
This code is working fine in single condition but when I enter another condition then its giving me an error i.e Type Mismatch.
View 9 Replies
View Related
Nov 23, 2008
I have attached my sheet. I want to apply IF-THEN formula in P1 cell in this condition:-
IF C1=Y then =IF(OR($D$4="",M8=""),0,IF($D$3="USD",J8,IF(J8="na","na",J8/ D$4)))
OR (IF NOT)
C1=N then=IF(OR($D$4="",M8=""),0,IF($D$3="USD",M8,IF(M8="na","na",M8/$D$4)))
View 3 Replies
View Related
Mar 13, 2012
I want to rank the employee corresponding to their overall rate while satisfying two conditions. Below written is how database will look like with different departments and employee names. I want function should give the employee rank when I enter his/her name and department. Check below for graphical view:
ABCD1Employee NameOverall RateDepartment
2Andy1HR3Gary1.5HR4Rina1.67HR5Rose1.2HR6Mary1.7HR7Joe1.5HR8Rose2HS9Rose1IT10Dany1.9HS1112
Employee Performance 1314Search: Enter Folowing15Enter Employee Name:Andy16
Enter Department:HR17Employee Detail18NameAndy19DepartmentHR20
Overall Rate121Ranking within Dept.#REF!22Department Average1.43
When I enter employee name in c15 and department in c16 i get all the desired values apart from rank.The formula which I use for rank is as appended below:
{=RANK(INDEX(B2:B10,MATCH(1,(A2:A10=$C15)*(C2:C10=$C16),0),B2:B10),INDIRECT("IF(C2:C10=$C16,B2:B10)"),1)}
If i but the cell range as b2:b10 instead of INDIRECT("IF(C2:C10=$C16,B2:B10)") then it give rank by considering all the values in that cell range. My problem is how to define the desired cell range. which gives me rank for HR department only.
View 9 Replies
View Related
Jun 22, 2008
=IF(H10=0,IF((R10-I10)>$AH$1,(R10-I10),0),IF((R10-N10)>$AH$1,(R10-N10),0))
I have a small issue with the formula above. Basically the sheet is used to input start and end time of shifts and create breaks and lunches accordingly. The formula above resides in column S. Basically it warns us if the end of the shift is over 4.00 hrs past the end of the last lunches. A shift can have no lunch, one lunch or two lunches. H10 would be the start of the first lunch, R10 is the end of shift, I10 is the end of the first lunch, AH1 = 4:00 or 4:00:00 AM, N10 is the end of the second lunch.
What happens is that if there is no lunch given in cells H, I, M, N or in this case H, I, N then I get a strange number. For instance, I put in 6:00 for the start time [E10] (actually we input military time and the result is displayed in AM or PM) and the end time as 8:00, then I get a result of 8:00 in the S column [titled +4hrs warning). 6:00 to 9:00 results in 9.00 hrs., etc, When we input a six hour shift, a lunch is automatically starts in H10 and ends in I10. Once this begins, the formula works correctly.
View 4 Replies
View Related
Oct 8, 2007
I've got no clue about all this, but I've had to get specific formula examples and fill in the blanks in order for my timesheet to work. There's just one final problem if somebody could please help.
This is a timesheet for a 5 day work week. I need to count the number of unique log numbers for a specific activity. The log numbers counted must be unique across the entire week, not just for each day, which means I want the formula to count the unique log numbers across multiple sheets.
The formula also has multiple conditions. I got 2 columns. The first part of the formula needs to verify a word, say, "split" and if it does it checks the adjacent cell for a unique log number. If both arguments are true, it counts the log as 1 unit.
Here is a working formula for only one page.
=COUNT(IF(D4:D29="split",IF(FREQUENCY(C4:C28,C4:C28)>0,1,)))
Here's 2 problems with this formula:
1. I will count if it encounters a blank cell in the Log numbers the first time (which will happen as not every activity we do has a log#), but it will stop counting if it encounters a second blank cell.
2. I don't know how to make it work across several sheets.
This is an alternate formula which works and skips the blank cells, but I don't know how to add the multiple condition of "split" and to have it work across multiple sheets. I just copied it Microsoft. As I said, I don't understand it, I just fill in the blanks.
SUM(IF(FREQUENCY(IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""), IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""))>0,1))
View 11 Replies
View Related
Feb 5, 2007
In the RANK() function I can rank a value depending on one set of values (one condition), but when you SORT data you can choose a second (and a third) condition.
Is there a way to RANK with two sets of values (two conditions)?
View 9 Replies
View Related
Apr 24, 2002
Can I have more than 3 conditions for the conditional formatting function?
View 9 Replies
View Related
Aug 21, 2009
1. I have 4 columns (let's say D,E,I,J);
D E I J
Account1 Sum1 Account2 Sum2
151 93,79 BF2479 30,00
1476757 93,79 BF7 22,00
BF2479 30,00 151 93,79
86041 84,03 23235 30,00
534 6608,45
2. I need to clean the multiple identical data in the list by both Account no. and Sum (of course the list is much longer!) so that the list should look like this:
D E I J
Account1 Sum1 Account2 Sum2
1476757 93,79 BF7 22,00
86041 84,03 23235 30,00
534 6608,45
I must say that I am a beginner in Excel, so I tried to use this:
=IF(OR(E5=$J$5:$J$430);IF(INDEX($I$5:$I$430;MATCH(E5;$J$5:$J$430;FALSE);1)=D5;"";E5);E5)
for column E, and
=IF(OR(J5=$E$5:$E$452);IF(INDEX($D$5:$D$452;MATCH(J5;$E$5:$E$452;FALSE);1)=I5;"";J5);J5)
(which is reverse of the first one) for column J; (The ranges are the ones used in the real sheet), not for the ones in the example above!); The formula does seem to eliminate a dew unwanted data, but does not give me all the wanted results!
View 4 Replies
View Related
Oct 16, 2009
I'm trying to divide my data into 6 different groups, based on 2 different criteria. First, I am not sure how to write the logical test to take 2 columns of data into consideration (using "&" and "AND" do not work; I am not sure what else to try), and second I can't figure out how to write the formula so that it can select from 1 of 6 conditions.
So, overall here is what I want:
If DL2=3 and CK2=1, then I want this to be labeled as '1'
If DL2=3 and CK2=2, 2
If DL2=1 and CK2=2, 3
If DL2=2 and CK2=2, 4
If DL2=1 and CK2=1, 5
If DL2=2 and CK2=1, 6
These 6 conditions cover all possible combinations of numbers in the two columns.
View 13 Replies
View Related
Nov 29, 2008
My data looks somewhat like this :
X Y Z
DOU Quarter Sum
0 Q1 = sum of 1's in Q1
1 Q1 = sum of 1's in Q2
1 Q2 = sum of 1's in Q3
0 Q2 = sum of 1's in Q4
1 Q3
1 Q1
1 Q4
0 Q2
A cell in the SUM column must contain the sum of 1's in column DOU if under column Quarter the value is Q1. Similarly for Q2, Q3 & Q4 if only the corresponding value under DOU is 1.
Its like.. count the number of 1's per quarter and come up with the sum in 4 distinct cells for each quarter.. i.e. quarter-wise sum/count of 1s
View 9 Replies
View Related
Aug 9, 2009
If ActiveCell.Value < -25 And ActiveCell.Offset(-2, 0).Value < -25 And ActiveCell.Offset(-3, 0).Value < -25 Then
Range("C4").Select
End If
End Sub
I am looking to do something similar on excel 07 as follows:
Range("O16").Select
IF ActiveCell.Offset(0,-1)Value ="1" And ActiveCell.Offset(0,-2).Value = "1" Then
ActiveCell.Value = "1"
Compile error expected: Then or GoTo At the First value (which I have highlighted in red)
Also would like the If formula to repeat for range O16:O36.
View 9 Replies
View Related
Jan 29, 2007
see attached workbook.
I need to create a conditional sum in the report tab in the yellow cells that will sum all the values from the list sheet, based on the following conditions.
1) The name is in the list
2) The code in the list is one from the codes tab.
I saw somewhere that I need to put an 'OR' statement in the conditional sum but I can't get it work. It seems to add up values for the code 'HOLIDAY' and I don't want it to.
View 3 Replies
View Related
Nov 29, 2013
I cant seem to rack my brain around how to go about making a formula in order to achieve my desired effect using the IF Formula. The data is as follows:
Worker Status %
S1 75% and higher
S2 42.8571% -74.9999%
SH Anything below 42.8571%
My sheet pulls the workers status from a preset data table, lets say S2 for Joe. I want the cell beside his status to compare what his percentage is and display what the status should be (based on the conditions of the above table). If hes working 73% it will turn up S2 and I can compare the value from the table and the value generated based on his working percentage (I would use conditional formatting to color the cell red or green to indicate if it matches or not.)
View 4 Replies
View Related
Dec 17, 2013
Is VLOOKUP suitable for if one wants to lookup a value from a defined range based on two assumptions?
Enclosed is an example file.
I want to lookup the values in sheet1 in the defined range in sheet2 and write enclosure and density to sheet 1
what method to use?
nicolaiBook1.xlsx
View 6 Replies
View Related
Aug 4, 2005
I have a table in Excel:
The first row is time in years.
The second row is method name,say,"A","B","C".
I want to count the number when the time is less than 5 years AND "A"
method is adopted.
I tried this:
count(if(AND(C2:Z2<5,C3:Z3="A"),C2:Z2) but it didn't work.
how to revise the formula?
In the mean time, count(if(C2:Z2<5,C2:Z2))worked as well as
countif(C2:Z2,"<5")
View 9 Replies
View Related
Jan 4, 2007
I'm trying to get a sum only if 2 conditions are met.
Column A has dates (ex. 01/02/2007) in a random order. Column B has a location in a text format (ex. London). Column C has a series of values formatted as [h]:mm.
What I'm trying to do is get a sum of the values in column C where the corresponding values in columns A and B = the date and loction I specify. I've tried...
=SUM(IF(('Jan ''07'!A$2:A$750="1/2/2007")*('Jan ''07' E$2:E$750="London"),'Jan ''07'!J$2:J$750))
but it's returning zero values regardless of the criteria being met or not.
View 12 Replies
View Related
Nov 16, 2008
I have a table like this:
Name (A).....Date-in (B)....Date-out (C)
Name1........27/12/2008....3/01/2009
Name2........25/11/2008....28/11/2008
...
I want to count the rows (in the entire table) that B and C dates intersect with a reference dates (say J1 and J2).
It is a booking table so I want to know if the apartment is available for the reference dates (i.e. no bookings for that days).
I tried first a simple double conditional to know if a date is inside two dates but it didn't work:
=SUMPRODUCT(--(B:B<J1),--(C:C>J1))
View 9 Replies
View Related
Jan 13, 2009
I have the following formula entered into cell O4: =IF((AND($K4="1",$B4="1 - C",$I4="open")),(1),(0)). When the three conditions are met in the corresponding cells, I get a "0" returned in cell O4. I would like the formula to return a "1" when all three conditions are met.
View 2 Replies
View Related
Jan 15, 2009
I want cell B to be blank if 1 of 2 different conditions are met. If Cell A is blank then so too should be Cell B. If cell A contains data, then I want cell B to perform a formula $D$1-E6.
However, if Cell C contains data then Cell B should return to a blank status.
View 6 Replies
View Related
Apr 14, 2009
In my Sheet "List" I have list of persons working on different projects.
I prepared graph after putting conditions on Project Type, Project Size, Project Year & Position (PM Project Manger). Every thing was done a in a nice manner with the help of below formula.
PJ TYPE , PJSIZE, PJYEAR, POSITION are ranges names.
=SUMPRODUCT(--(PJTYPE=$A$2),--(PJSIZE=$A6),--(PJYEAR=B$4),--(POSITION="PM"))
But the problem was occured that in a year if a person work on small project more than once then he will be counted only once. But if he has worked in same year on Medium or Larage project then they will be counted separately. I tried to oversome the problem with the help of Pivot Table and put manually some legend P1, P2 & P3 against the person name if he is working on same type of project in same year. then count only P1 in my formula to count how many Project Manager worked on Project. like
=SUMPRODUCT(--(PJTYPE=$A$2),--(PJSIZE=$A6),--(PJYEAR=B$4),--(POSITION="PM"),--(PMCOUNT="P1"))
Now i am trying that in a separate columm of # of PM there must be a formula which only put P or 1 for a person if he is working on same project in a year but i want that p or 1 only appear against his first entry i duplicate. for other persons it automatically enter 1 or p if they are appearing only once. i have tried a lot while using countif with multiple conditions but all in vain.
View 3 Replies
View Related
Aug 28, 2009
I am trying to sum numbers based on three counditions.
My problem the third condition needs to use a wild card. I have attached a sample file, in the last condition I am only wanting to sum numbers where the characters in the last column begins with bd*
The yellow cell contains the formula I am using.
View 4 Replies
View Related
Oct 27, 2009
I currently have this equation: =AVERAGEIF($B$2:$B$900000,">="&L5,$C$2:$C$900000). Where L5 = 100. I'm interested in having the formula averageif B column value is between L5 & L6. Where L6 = 200. So I want it to find all values in B$ that are between >= 100 but < 200, and then average their C$ counterparts.
View 3 Replies
View Related
Jan 28, 2009
I'm having some problems getting a simple Do While loop to run. Essentially I'm trying to get it to run as long as one of two conditions is met, so I used an Or function in the loop condition. This then produces the "Run time error 1004: Application defined or Object defined error" error on that line.
I'm certain my syntax for the two conditions is correct (one is a simple check value in a cell, the other is check the value of a VBA variable) so it would appear the error is caused by the inclusion of the Or function. So I guess my question is this, have I done something wrong? If so what and if possible why is what I did wrong (I like to understand these things)? Is this something that has to be done in a different way or even is it something that cannot be done? I'm using excel 2003 on XP SP3.
View 5 Replies
View Related
Dec 3, 2005
way to return a value from a data table by specifying 3 conditions to be met.
Eg. My data table is in cells W1:Z100
(Column names = Track, Distance, Class, Time)
I want to be able to return the time value, based on track, distance and class values.
View 9 Replies
View Related
May 31, 2006
My book is telling me that I can't use multiple conditions with a SUMIF
statement but other sources are telling me I can. Either way, it's not
working!
My situation:
Look at data in $V$2:$V$144 (range = "group"). If cells in "group" =
"Broth" then look at $Y$2:$Y$144 (range = "bldg"). If "bldg" = "1" then sum
corresponding cells in $R$2:$R$144 (range = "fcst").
This is one of my attempts - feeble...
=SUM(IF(group="Broths",IF(bldg="1",fcst,0)))
View 9 Replies
View Related
Dec 22, 2006
I need to set up something like an IF formula that will populate 1 cell based on which of 11 possible values will be represented in another cell. I can do this up to 7 with no problems using the following IF statement:
= IF(G23="1","a", IF(G23="2","b", IF(G23="3","c", IF(G23="4","d", IF(G23="5","e", IF(G23="6","f", IF(G23="6","g",IF(G23="7","h", "OTHER"))))))))
But if I try to put more than 7 conditions in there it tells me I've entered too many arguments. Is there a way I can either use more than 7 arguments or some other formula
View 9 Replies
View Related
Nov 10, 2008
My worksheet contains a database of systems that have been installed since 6/08. Each system has its own unique serial number, however, this serial number appears more than once.
I'm trying to count the number of systems that have been installed since 9/08/08 but I do not need Excel to count each s/n entry, I only want it to return a count per s/n once to get a total of all installations since this date.
I have attached the sheet in pk zip format.
View 8 Replies
View Related
Jan 8, 2009
ABB
MetricsCategoryJan 2008VisitsTravel50view_offerTravel10
Above is the data I want to lookup on. I need to be able to do a lookup for travel visits and a seperate lookup for travel view_offers.
Is there someway to do this with a vlookup or maybe match index?
View 9 Replies
View Related
