Multiple IF And ISTEXT Within Same Formula
Jun 23, 2014
I have a rota sheet whereby shifts are written in as start time, finish time, and then a calculation for the hours.
At the bottom I have another SUM formula to calculate total daily hours.
However, within the total personal hours I have a VLOOKUP formula to check if the start time cell has a holiday text reference.
Please see below:
Take the "Sergei" example: he starts at 9am, finishes at 8pm, and the formula automatically calculates an 11 hour working day.
However, in the "Tom" example, the "TH" refers to another sheet which houses a reference of the amount of hours to place in the total hours box if those letters appear, thus imputing holiday hours at the same time.
"=IF(ISTEXT(F24),VLOOKUP(F24,'Hol-Meet Hours'!$F$7:$G$27,2,0),(G24-F24)*24-IF((G24-F24)*24>6,0,0))"
What I am struggling with is to use an IF and ISTEXT formula in the "Daily Hours" formula to ignore any holiday inputs, thus only giving me trading hours.
The only way I could think of doing it was something along these lines:
"IF(ISTEXT(F16),SUM(F17:F31),SUM(F16:F31)...et al"
The issue here is that I need to repeat the formula for every staff member within the same cell's formula, which I don't know how to do (multiple functions within a formula repeated 20 times), and is a right drag.
View 5 Replies
ADVERTISEMENT
Jan 28, 2010
I am summing 3 cells, A3, A4 & A5. Cells A4 and A5 may contain text. I want to write a formula using the IF(ISTEXT funciton which says something like this:
Sum(A3:A5), if cell A4 and A5 contain text then sum just A3. If only cell A5 contains text then sum just A3:A4.
Im not sure exaclty how to construct this.
View 9 Replies
View Related
Mar 15, 2009
I have this formula in a cell: =IF(ISTEXT(F5),F4,""). The problem is F5 is visually blank and has this formula =B3. so it returns F4. Is there a way to rewrite the formula or to use another formula, so it will return blank?
View 2 Replies
View Related
Feb 21, 2010
I've been trying to use excel's Isnumber or Istext function to evaluate a range of cells, but have been unsuccesful so far. Is it even possible? or is the formula just restricted to evaluating only single cells?
For example, if I do, Isnumber(A1:A10), is there any way to make that work?
View 9 Replies
View Related
Jul 31, 2009
I have a workbook with worksheets named by peoples names. There are cells in my summary sheet that contain all the names of the worksheets (or peoples names).
Each worksheet has a table in it that I am trying to search one column to find a specific name and then report if there is text in another column in the same row. If I use the following formula:
=IF(ISTEXT(INDEX(INDIRECT("'" & E$4 & "'!f10"),MATCH(INDIRECT("A11"),INDIRECT("'" & E$4 & "'!h10"))))=TRUE,1,0)
It works and I'll return a "1" for cells in column F that have text and a "0" for columns that don't.
However, I am trying to sum up the cells in F that have text and when I enter:
{=SUM(IF(ISTEXT(INDEX(INDIRECT("'" & E$4 & "'!F10:F200"),MATCH(INDIRECT("A11"),INDIRECT("'" & E$4 & "'!H10:H200")))),1,0))}
it returns "1" when that should not be the answer. Am I doing something with the array in my INDIRECT formula? I used this similar application for another purpose in this workbook and refereced the array through INDIRECT the same way (but didn't use MATCH or INDEX) and it worked fine. This was the formula:
{=IF(SUM(IF(INDIRECT("'" & C$4 & "'!H1:H200")=INDIRECT("A5"),1,0))>0,SUM(IF(INDIRECT("'" & C$4 & "'!H1:H200")=INDIRECT("A5"),1,0)),"-")}
View 9 Replies
View Related
Mar 20, 2014
I'd like to search a cell A1 containing for example 'hello_L765'. If the 7th character is L and the 8th character is text(ie not a number) than display yes, otherwise display the 7th character.
View 3 Replies
View Related
Jul 5, 2007
I'm currently designing a new spreadsheet and I can't change the formula I'm using below to search more than one cell or a range. Is either choices a option?
I'm using =IF(ISTEXT(E135),G135) Can I add cell E139 (and how many additional cells could be added) or can I set it up to search E135:E144.
View 13 Replies
View Related
Nov 12, 2009
I'm trying to apply the istext to a cell that has multiple values eg: [car, boat, truck] so I have setup the cell with the formula
=istext(c147 = "car",1,0)
it returns false constantly. Is that becuase of the other values separated by comma's in that same cell?
Is there a way to do this so that I don't have to go through 300+ cells that have multiple values to separate them?
View 9 Replies
View Related
Jan 13, 2005
I am trying to create code that deletes lines in a spreadsheet when the first column is either blank or has text in it. However, when writing the VBA code, ISNUMBER and ISTEXT do not work. Amazingly, ISBLANK does work.
View 4 Replies
View Related
Mar 24, 2009
I have 4 cols, x rows. I need Column C to check Column B for a numerical value, and if true, return the value in B, and if not true, then return value from A. Column B are functions formatted as general. The reference made by Sheet2!B is numbers formatted as text as to retain leading zeros. Here is what I have so far
=IF(ISBLANK($B10),$A10,$B10)
View 5 Replies
View Related
Dec 3, 2012
I'm trying to validate a cell in Excel 2007 that should contain only two letters.
Formula:
Code:
=IF(AND(LEN(A1)=2,ISTEXT(A1)),TRUE,FALSE)
When the value of A1 is JK, the result is TRUE
When the value of A1 is 12, the result is FALSE
When the value of A1 is 3K, the result is TRUE
When the value of A1 is K3, the result is TRUE
When the value of A1 is 123, the result is FALSE
When the value of A1 is JKL, the result is FALSE
When the value of A1 is 3, the result is FALSE
When the value of A1 is K, the result is FALSE
The LEN function works as expected, but the ISTEXT function does not, whenever the cell contains a number and a letter. I've tested the LEN and ISTEXT functions separately, and get the same results.
Is it me, or is it Excel?
View 7 Replies
View Related
Apr 4, 2009
was workin' on a text recognition question with someone here:
http://www.excelforum.com/excel-new-...ml#post2071064
and another user posted this:
View 4 Replies
View Related
Feb 9, 2014
I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.
I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.
Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.
I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.
View 12 Replies
View Related
Aug 11, 2014
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))
View 7 Replies
View Related
Feb 17, 2010
I have listed data below for purposes of the formula:
HTML CustomerInvoice Invoice Amount
(Rep. Invoice Month)
Nike1 1,000
Reebok1 600
Adidas1 700
Puma1 600
Nike1 (250)
Reebok2 1,000
Adidas2 600
Puma2 700
How do I get the data above, into a spreadsheet like the one below? I have tried If/Than Statements with no luck. I need to total the amount of invoices during a specific month for a specific customer.
View 4 Replies
View Related
Oct 21, 2009
When producing a summary sheet that feeds off several tabs, is there a way I could drag the formula accross with it selecting data from the same cell in multiple tabs?
View 12 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
Jan 16, 2014
I need to create a formula that takes into consideration 9 possible scenarios using IF and AND. I have 3 performance measures (exceeds requirements, meets requirements, improvement needed) and 3 potential measures (low, medium, and high). I have a spreadsheet where each individual is rated for both. Each combination correlates to a numeric rating on a 9 box grid. I need to include every option in my formula so the correct rating is determined for each individual. I've tried several versions on my own, and can't get past one set of conditions.
Performance PotentialRating
Exceeds ExpectationsLow4
Exceeds ExpectationsMedium7
Exceeds ExpectationsHigh9
Meets Expectations Low2
Meets Expectations Medium5
Meets Expectations High8
Improvement NeededLow1
Improvement NeededMedium3
Improvement NeededHigh6
Unacceptable Low1
Unacceptable Medium3
Unacceptable High6
View 3 Replies
View Related
Mar 12, 2009
I am not sure what the best way to describe this is but here I go.
Year Account Net Amount
1 2006 Travel Expenses $5,000.00
2 2007 In Kind Time $2,500.00
3 2008 HR Time $3,000.00
4 2006 Travel Expenses $1,500.00
5 2006 In Kind Time $2,500.00
6 2007 Travel Expenses $6,000.00
I need to create a formula that will say: If column A1:A6 has a ,2006, and column B1:B6 has "Travel Expenses", than Sum C1:C6. The answers should be:
2006 Travel expenses = $6,500
2006 In Kind Time = $2,500
2007 In Kind Time = $2,500
2007 Travel Expenses = $6,000
2008 HR Time = $3,000
View 4 Replies
View Related
Sep 23, 2009
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)))
View 3 Replies
View Related
Jul 17, 2006
How can I make the following formula work =(B3+C3+D3)*E3*F3? I need the
numbers in brackets to be multiplied by E3 and then that number multiplied by
F3,
View 11 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
Apr 24, 2008
When you have a total of 11 columns, but 6 of those columns(yellow) can count only as one, and 2 columns (orange)can count as 1, how do you write the formula
View 12 Replies
View Related
Nov 25, 2013
Writing a formula for the info below. Here is what I need basically. If B2>2, then I need it to count all central and field, STK and NSTK in column D.
location
days
stock type
Central
3
nstk
field
1
stk
[Code] ...........
View 9 Replies
View Related
Feb 28, 2014
I am trying to tell the spreadsheet to look at each separate month and return different criteria. Current formula for single month search is:
=IF(VLOOKUP($E5,'PO Info'!$A:$AW,2,0)"","B",IF(VLOOKUP($E5,'PO Info'!$A:$AW,3,0)"","C",IF(VLOOKUP($E5,'PO Info'!$A:$AW,4,0)"","D",IF(VLOOKUP($E5,'PO Info'!$A:$AW,5,0)"","E",IF(VLOOKUP($E5,'PO Info'!$A:$AW,6,0)"","F","")))))
I would like to add an AND function so that the IF statement also looks at the month which is represented in this case as $E$7, the formula I have been trying is:
=IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,2,0)"","B",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,3,0)"","C",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,4,0)"","D",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,5,0)"","E",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,6,0)"","F")
Then carry this forward for other months, for example, a later look at this formula would be:
=IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,2,0)"","B",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,3,0)"","C",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,4,0)"","D",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO
[Coe] ...........
View 2 Replies
View Related
Apr 16, 2006
in cell A1 and cell B1 I have a "yes", "no" or "maybe"
in cell C1 and D1 I have a number
if cell A1 and cell B1 "yes" multiply cell D1 times 2 (all results in cell E1)
if cell A1 "yes cell B1 "no" multiply cell D1 times 1.2
if cell A1 "no" cell B1 "yes" multiply cell C1 times 1.2
if cell A1 and B1 'no" multiply cell cell C1 times 2
if cell A1 "maybe" cell B1 "yes" multiply cell D1 times 2
if cell A1 maybe cell B1 "no" multiply cell C1 times 2
if cell A1 "yes" cell B1 "maybe" multiply cell D1 times 2
if cell A1 "no" cell B1 "maybe" multiply cell C1 times 2
View 6 Replies
View Related
May 11, 2006
I need a formula for:
If nothing is entered into J1, leave it blank
But if J1 is greater than or less than H1, have the text "why?" appear in the cell. Late in the day and getting a lil brain dead.
View 4 Replies
View Related
Aug 9, 2006
I need a formula that will calculate if a Category is at Very High, High, Med, or Low level risk. I've attached a sample file for review. In the sample I use a countif and countblank formula to count the number of specific statuses used. On my summary page I want to use the counts to determine if a category is in one of the 4 risk levels. I've broken down additional information within the attached file.
View 9 Replies
View Related
Mar 8, 2007
I need to create a formula where if (example) letter A was placed in cell A1, cell A2 would assign (example) value of 1, but if letter B was placed in cell A1, A2 would assign different value.
Ideally the formula would be able to handle multiple values from a range on seperate sheet.
View 3 Replies
View Related
Apr 24, 2007
I am trying to calculate phone call rates based on several different conditions. *if my phone call has a "DESCRIPTION" of "Local" then I want to add 2 cents onto the "COST" *if my phone call has a DESCRIPTION of "Mobile" then I want to use the formula = SUM(G7*24*60)*0.4+0.1 to calculate the charges *if my phone call has a "DESCRIPTION" of "13 Number" then I want to add 5 cents onto the "COST" I am able to use a formula that allows 2 condition but I have been unable to work out a formula where I can add more conditions.
I have attached a spreadsheet
View 2 Replies
View Related
