Check Sum Then More IF Function Conditions
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
ADVERTISEMENT
Nov 8, 2006
i am trying to create what is probably a simple If statement for a worksheet formula i want to check to see if one of two cells have the text specified if so then the cell value is A4 from another sheet if not the cell containing the formula should be blank, something like below
=If(B1="on" Or D1="on",Sheet2!A4,"")
I also need this kind of formula with the AND operator, something like
=If(B1="off" And D1="off",Sheet2!A4,"")
View 5 Replies
View Related
Sep 30, 2007
I have a sheet here whereby if Bor No = 1, then total incurred should return as incurred. But if BOR No is more than 1, then the total incurred should sum up seq 1 and 2 of incurred. Bear in mind that seq no could be up to 4, 5, 6.. and it's not fix. But if BOR No = 0 or NULL, then total incurred should equal to "0"
View 5 Replies
View Related
Jul 14, 2009
I have the data in this form
YYY
NYY
NYY
NNY
NYY
NNY
NYY
NYY
NYY
NYY
YNN
NNY
NNN
NYY
The output should check two conditions. If all three columns are "Y" then it should return value "Y". If any two columns are "Y" then it should return value "Y". or else it should return "N". Then the output should look like this.... The output column is colored in Red..........
View 5 Replies
View Related
Oct 22, 2009
I want to vary a for next loop, depending on the value of a variable (y). Is there a way of doing this without repeating code as in the example?
If y = 1 Then
For x = 1 To 100
' lengthy code changes Cells(x,1) characteristics
Next x
ElseIf y = 2 Then
For x = 101 To 200
'lengthy code changes Cells(x,1) characteristics
Next x
ElseIf y = 3 Then
For x = 201 To 300
'lengthy code changes Cells(x,1) characteristics
Next x
End If
View 9 Replies
View Related
Jul 22, 2007
I want to create a user form in excel that auto calcs the discount depending on customer type and no. of books purchased. There are two types of customer’s one individual and the other schools, library etc. The discounts are below
More than 50 books - 30%
25 - 49 - 20%
15 - 24 - 15%
5 - 14 - 10%
<5 - 5%
Individual
>25 - 25%
5 - 24 - 15%
<5 - 0%
I have created a form with option and text boxes to capture data but am really struggling with this as in my head I understand a set calculation will need to take place once relevant conditions are met but am really struggling to put it into code (yes I am new to this).
View 2 Replies
View Related
Nov 16, 2009
I have several rows with numbers that is either 0, a balance or a text like n/a. I need a formula that return true if all are 0 or contain n/a. If there is one balance shall it be false. Any suggestion? See example:
View 6 Replies
View Related
May 22, 2006
=AND($AE$2:$AE$10<=$F$2:$F$2000;$AE$2:$AE$10>=$E$2:$E$2000) With this I am checking for conditions to be true or false
Lets say I am checking AND(AE2 <= F2 ; AE2>=E2) this tells me if date in cell
AE2 is smaller or equal to Cell F2, but how can I set this fromula to check if all the cells from F2 to F1000 are equal to AE2 so If I write date into Cell AE2 it checks all dates from F2 to F1000 and check if condition is true or false
(and if I write date in cell AE3 it should check all the dates from F2:F1000, if date in AE3 is in this range)
View 9 Replies
View Related
Oct 20, 2006
Excel file attached! I need a macro to do the following:
Start with cell F4 and read the increment value from cell C4. Then add this with F3 and display the result. Continue with the same increment until it reaches the value equal to cell B4. Then read the increment from cell C5 and do the same until value equals cell B5. Repeat the same step until it reach the value equals B7. I did manually in the column F4 to F28. Moreover, the cell increment will change according to the variable in A2. In this case it is 25 and got 25 values to fill the column R.
View 8 Replies
View Related
Aug 22, 2009
the traditional count if statement doesnt return what I need. I have an array of values that need to be checked.
Column: A B C D E
Data: .25 .49 .18 (Criteria 1 Result) (Criteria 2 Result)
What I need to do is check the data for the following conditions and if it meets the crirteria I need excel to place a 1 in column D or E.
Criteria 1
If any of the coulmn data contains a value less than .5 I need a 1 placed in column D
Criteria 2
If any of the column data contains a value greater than .5 but less than 1.0, I need a 1 placed in Column E. I tried using an IF/ Count If statement, but cant seem to get it to return the result I need.
View 5 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 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
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
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
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
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
Feb 8, 2010
I have created a list for Cells A2:A10 which has the various roles of person. Gave it a name Designation. I have created a drop down list using this list by going to validation. =Designation
I want a function that would check the value entered and search this list would return TRUE or FALSE
View 14 Replies
View Related
Apr 22, 2014
I have a list of employees and the dates they worked. I'm trying to formulate an IF function that will check the name of employees in the list against the to the contents of a particular cell and also check the date the employees worked against another cell that contains a specific date, and if both these conditions are met, mark that cell with an X, if not leave it empty.
The formula I have right now is =IF(AND(A2:A27=G4, D2:D27,H3),"X",""). A2:A27 is the range that contains employee names, G4 is the first employee's name who I want to check if he is on the list, D2:D27 is the list of days they worked on and H3 is the date I want to know if this particular employee worked in. Using this formula returns either an empty cell of a #NAME? error. Is this a cell formatting issue?
View 14 Replies
View Related
Jul 28, 2006
Can you make the first part (the logical test) be anything other than a numerical value? I want a value to display only if a check mark (using the wingding font for the check mark) appears next to certain items in a list. For example: =If(A1=checkmark,B1+C1,""). If I am not clear maybe I can explain it another way.
View 4 Replies
View Related
Mar 26, 2009
Combining two equations with MOD function for UPC check digit calculation.
I wanted to combine these if possible,
View 4 Replies
View Related
Apr 3, 2012
Use look up to check batches of data a return a value dependent of multiple ifs?
I have this formula from here
=LOOKUP(COUNTIF(C4:C7,""&0),{0,1,2,3},{19.9,29.9,39.9,49.9})
What I am trying to achieve is using the above can it check
C4:C7 Grouped and named "first"
C8:C10 Grouped and named "second"
C11:C13 Grouped and named "third"
If one from each group is selected it returns 1 value, if 2 of each are selected it returns a second value and if 3 of each are selected it returns a third value?
I thought by grouping them it may give me the desired results but all it does is change if I select more than one from any of the groups.
View 2 Replies
View Related
Oct 17, 2007
I would like to highlight cells is two conditions are met:Cell = 0Offset(0,-1)>0I tried the conditional format wizard and entered a formula: =IF(AND($J2=0,$I2>0)) But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard
View 2 Replies
View Related
Oct 7, 2006
I need to check a range of cells (B4:B35) and see if any of the contents are less than a specified cell (M1) and then show a message, (the message part I can do). I have tried using For Each but I then get the message for every cell that is below the specified cell (which in theory could be all of them). I have also tried using an If Any statement but didnt work.
View 5 Replies
View Related
Nov 27, 2009
My company uses 4 types of barcodes 8, 12, 13, & 14 number barcodes for our products my problem is that I can't figure out how to force the barcode to format properly no matter how somebody enters it if they don't use spaces or put them in the wrong spots, I can't use custom formats because there is 4 different layouts
8 digit should be "#### ####"
12 digit should be "###### ######"
13 digit should be "# ###### ######"
14 digit should be "# ## ##### ######"
these barcodes are in columns L, M, & N also right now 'm using a formula in another cell to verify the barcodes by calculating the check digit and comparing it to the check digit typed the formula i'm using is
View 9 Replies
View Related
Aug 10, 2012
I have data in Row 53 that spans 7 columns, but stays in the same row. I want to design a loop to select every 7th cell in that row and check if it is empty. If not, add onto a "counter" then display the final number of occupied cells (the value of the counter) at the end. This is what I have so far, but I get all sorts of errors.
Code:
Sub Tester()
Dim WB As Workbook
Dim WS As Worksheets
Dim modCounter As Long
Dim Cell As Range
Set WB = Workbook("Transverse Series.xlsm")
Set WS = WB.Sheets(BM18)
[Code] ......
View 1 Replies
View Related
Nov 3, 2008
I am using this code
View 5 Replies
View Related
Jul 4, 2014
I want to have 1 check box affect 3 others in the following way: check box 1 if checked, allow check/uncheck of check boxes 2, 3, 4 if unchecked, uncheck boxes 2, 3, 4 and do not allow checking check box 1 is linked to D1 which starts with a value of false. cell E1 is if condition to have value 1 when D1 has value of true.
I have attempted to attach an example worksheet.
Test check control.xlsx
View 1 Replies
View Related
Sep 9, 2007
I am creating a userform with 10 checkboxes. The first 9 checkboxes are user options. I want the 10th check box to be a "Select All" option i.e. if the 10th checkbox is checked all the other 9 options are deemed to have been selected.
The way I want the display to work is that if the 10th checkbox is ticked all other checkboxes are cleared. Also if the 10th checkbox is ticked and any of the other check boxes is selected then the 10th checkbox should be selected.
I have tried coding this up but the checkboxes don't seem to operate as desired. I placed some code on the click event for the last option button to set the vlaue for all other buttons to false. This works but the 10th check box doesn't get ticked itself. When I try to code up the other bit I get similar issues.
View 9 Replies
View Related
Aug 20, 2009
When I press a command button I want to:
Uncheck a checkbox made with Control Toolbox
Check a particular Option Button within a group box made with the Forms Toolbar.
EDIT: Clarification, I want both things to happen when I click one button.
View 3 Replies
View Related
