Attached is a small sample which displays what I am trying to achieve - I am trying to create an if statement for cell J2 which says:
IF F2 = 1 then "R", IF F2 = 0 but there is a 1 in either H2, I2, J2 then "W" and IF F2:I2 are all 0's "N"
I Have manually typed the desired output in col J
I Have manually typed the desired output in col J
Attached File : IFFF.xlsx‎
I am attempting to create a multi condition IF statement for work. Essentially what we have is a column dedicated to the date in which a piece of equipment is supposed to go into service.
What we want to do is make it so that if the current date is 40 days past the scheduled in service date, a status column displays the term "Verify".
Additionally, if the scheduled in service date is 365 days from the current day, a status column should display the term "Future".
If it is any day in between the two, it should display the term "Active"
I have two seperate lines of code, one that covers verify and active and the other that covers future and active but I can't combine the two so that I have one function. The code I compiled is included below, any help would be appreciated.
=IF(D7=0,"Active", IF((TODAY()-40)-D7>0,"Verify","Active"))
=IF(D8=0,"Active", IF((TODAY()+365)-D8<0,"Future","Active"))
(D# corresponds to the respective cell that contains the scheduled ISD)
if it's possible to create a statement from cells in a workbook perhaps by using the hyperlink option or if there is any other alternative way.
In brief, the statement should recognise what has been inserted into each cell in the row and then generate a statement.
I hope this is clear. See attached a copy of the excel sheet from which I would like to create the statement.
I almost get my eyes killed whenever at the end of the month
Statement
A
B
C
D
[Code].....
I have issued invoices with reimbursements to Mr. A so on 30th of the month, I issue a statement to summarize my invoices and reimbursements as above example. Note : An invoice and a reimbursement are always in a file(sheet1 and sheet2) and a solo reimbursement is a file(sheet1). "Invoice"(Folder) in drive D contains Invoice00100(SI11) & Reim00111(SE55).
Samples of invoice and reimbursement
Invoice(sheet1)
A
B
C
D
E
[Code]....
Solo reimbursement is the same as above reimbursement table
code that can extract all excel file in "invoice" folder and auto create as my first above table. I would like to manual select the files which are taken to extract to the statement.
I'm trying to set up an if statement that will recognize that if a cell is FHR it will do something...but if it's PHR it will do something else. I think I found the place where I keep getting an error but I'm not sure how to go about fixing the issue.
View 2 Replies View RelatedCreating a formula that basically has a big IF statement. I hope this makes sense, so here goes. I am looking to find the "age" or "elapsed days" of an ID from when they created an account to when they stopped. This might be very complex and if there's not a way to calculate the Elapsed days (even though that's what I prefer), perhaps there is some formula that can return a value like a "1" or "True" or something that lets me know the statement is true.
original formula from post 3261273
{=IF(D2>0,A2-INDEX(A$2:D$16,MATCH(B2&"1",B$2:B$16&C$2:C$16,0),1),"")}
New formulas needed or revised from the above:
Formula 1: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Create to Suspend but NO Stopped)
Formula 2: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped) OR Column 6 (Unsuspend), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Suspend Only, No stopped or Unsuspend)"
Formula 3: The ID in column 2 does not have to be the same, but if there is a value in column 3 only (Create) and there is no value in the Suspend, Stopped, or Unsuspend columns, then place a "1" in the cell. My manual calc is the "Create Only" column.
Event Date
ID
CREATE
Suspend
STOPPED
Unsuspend
Elapsed Days (Create to Suspend but NO Stopped)
Elapsed Days (Suspend Only, No stopped or Unsuspend)
[Code] .........
I am trying to create a formula which looks in 1 cell to determine 'IF' it states either "BUTT CUT" or "DIE CUT" and then does a VLOOKUP in a table array for each which ever column relates to asnwer to 'IF statement.
This is as far as I have got but even this won't work:
=IF(I15="BUTT CUT",=VLOOKUP(J15,V13:Z22,2,FALSE))
I am trying to have a cell in sheet "Summary" count the number of cells in column DX of sheet "Analyses" that are greater than 0, provided that the value in column A of "Analyses" corresponds with the value in B8 of sheet "Summary."
(In "Analyses," there are 106 subjects, each taking up 64 rows. So, columns 1-64 correspond to Subject 1, columns 65-128 correspond to subject 2, etc. In column DX, each subject has 64 values that are either 0 or greater than 0. In "Summary," each subject has one row that summarizes the 64 trials. I want a single cell in the "Summary," sheet to reflect the number of times each subject produces a value greater than 0 in column DX of "Analyses.") I tried using this formula, but it did not work correctly:
=COUNTIF(IF(Analyses!$A$1:$A$10000=Summary!B8,Analyses!$DX$1:$DX$10000,""),">0")
(Summary!B8 = 1, so I am trying to calculate the number of values in DX that are greater than 0 only for subject 1.) When I press enter, this yields a value of 384. This is impossible, given that subject 1 only has 64 possibilities of yielding a value greater than 0. Subject 1 has 2 values in column DX that are greater than 0. I tried making this an array formula by pressing Shift+Ctrl+Enter, and that just gives me a #VALUE! error.
I am currently using an Intersect statement in a worksheet module to perform two things:
1. Insert a time stamp into row 2 when row 1 has a price inserted
2.To clear that time stamp if the price is deleted at some later date.
My problem is with the time stamp value being deleted by the user.
If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.
I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?
look at the attached. In the estimate tab look at the box highlighted in yellow. Then look at the cells in pink (row 70). F70 is selecting the lowest maintenance value from the yellow box but I want C70 to display the hours associated to that value. The correct hours will need to appear according to what value is displayed. (this sounds confusing but look at the formula in F70 and you will hopefully see what im trying to achieve).
View 2 Replies View Relatedi'm trying to ask my spreadsheet to fill a cell with either 'YES' or 'NO' depending on the value of one cell. I've succeeded in getting it to enter 'YES' but can't figure out how to tell it to choose between the two options. This is the formula so far
=IF(L5>2,"YES")
i need to do a if statement to take 10% off the value in cell b2 if a2 =yes or if a2 =no then no discount will be applied.
the yes and no in a2 is a v lookup from another worksheet.
This is the current
Sub IfExample()
If Range("C1").Value = "Yellow"
Range("D1").Value = "COLOR"
End If
End Sub
I get a compile error on the "If" line.
Once I get it working how would I say this correctly?
If Range("C1").Value = "Yellow" or "Red" or "Purple"
The final "hope" is that it will continue down column C and D looking for the condition until first empty row is found at which point the code will stop looking for the condition.
I need an if statement which returns a value if cell B2 contains the value “Liability” The whole value of B is Liability with a 10 digit number (which is changing). I tried:
=IF(B2="Liability","Liability","")
=IF(B2="Liability*","Liability","") and
=IF(B2=CONCATENATE("Liability"," *"),"Liability","")
But nothing is working. Can’t get my head around to get it up and running and couldn’t find previous threads.
I need to match data in cell A to cell B and then if they equal I need to copy the adjacent cell C to cell X . How do I set up a macro to do this automatically? I have over 5000 cells to compare and match up.. I have Office 2003.
View 7 Replies View RelatedI am trying to use the if statement, if a cell = a cell that has a word in it then show content of another cell that has a name in it.
View 5 Replies View Relatedi have the condition below.
1<=x<2 = a
2<=x<3 = b
3<=x<4 = c
4<=x<5 = d
>5 = e
how to put the if statement function in the cell? or any better function to use?
I am trying to do a calculation based on the conditions of two cells but one cell I would need the range of the report. Either way, here is my current statement.
=IF(P2:P15 = "Green Building 15",SUM(COUNTIF(C2:C15,"Over AC")+COUNTIF(C2:C15,"Top Lab AC")),0)
I get a Value# error (though it systematicaly works if you check in the funtion area), and its because of the range I am using, is there anyway to bypass thiss issue or can someone give a better calculation.
I need a simple IF statement that look up in Column C for any text, and then add the value from Combobox "txtfloors" to Column B .
View 1 Replies View RelatedI'm looking for an if/then statement that will check if there is any kind of value in cell b before doing a calculation to cell J
i want to make and if statement in a cell that does the following:
if "J8 > 10" then fail
if "5
I have a cell that containes a concatenate statement for two named formula. The value taht the cell returns is a multiple of 10 (i. e10, 100, 1000, 10000 etc etc.) then in the adjacent cell, i have a nested if statement giviing differing text dependent upon the other cells value, i.e if less than 1000, return text string of "good" , however the formula does not seem to accept the value given in the concatenate cell.
View 9 Replies View RelatedWhen I tried using if & or statements I got an error - so I tried this:
=IF(K7="&","V,",""),IF(K7="1 Space + &"," V,","")
I want to return 'V,' if cell='&' or if cell='(space)&' I want to return '(space)V,' What is wrong with this statement..?
I have an area of a spreadsheet that I want to "disappear" when a particular option button is selected. I can make the text go away, but part of that area has cells that are formatted differently than the surrounding cells. I would like to change the cell background color, text color, and border setting. How would the syntax read?
View 5 Replies View RelatedIf I have any value in cell A1 then the cell should show 1 if true or nothing if false. I have managed this via
View 3 Replies View RelatedI have two sub-system tabs (IAS & CCTV) and one calculations page (estimate). I need G3 (estimate) to give me the total price of hours sold on a project.
Because the systems hours can be marked up differently I wrote an average formula. But I need to add an IF statement saying if there is no hours in the sub system then ignore the hour price.
if this formula doesnt take this into account and I delete H3 (CCTV tab) then the overall price of hours sold in estimate will be wrong.
I have attached the sheet.
G5 - can contain True or false
G6 - contains text but the if is on the basis of this cell being "Hand Delivered" or not
I have tried the following to illustrate what I after:
In a cell I have a description, say "FOOD" then another cell with amount say £2, then in another cell I want it to be looking and if it sees "FOOD" entered, it takes the amount £2 and adds it to a running balance.
Cell A1 = Food
Cell B1 = £2
Cell H1 = (looks and if Food) adds £2 to cell
If C36, C37, C38 or C39 contain a 0 then put 0 if not continue with the the formulae
I have this but I know its not right as this is a sum: =IF(C36:C39=0,0,ROUNDUP((C36/C37)+(C38/C39),0)).
I can't seem to get this If statement to return an empty cell It returns 0
HTML Code:Â
=IF(+B7+C7="","", +$C$3-B7+C7)
The formula will go in D7
If either B7 or C7 is empty, leave D7 empty
If either B7 or C7 has a value, add or subtract from $C$3