=IF(ISTEXT On Blank With Formula?

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


ADVERTISEMENT

IF (ISTEXT Formula)

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

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 View Related

Isnumber Or Istext Formula

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

ISTEXT In Array SUM

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

Searching A Cell With IF And ISTEXT

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

Can ISTEXT Search A Range

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

ISTEXT Returning False

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

VBA ISNUMBER, ISTEXT Functions

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

Check Cell For Data. Tried ISREF, ISBLANK, ISNUMBER, ISTEXT.

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

Excel 2007 :: Validation With ISTEXT Not Working When Number In Cell?

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

Leaving Blank Cells Blank In Dragging Formula Combining Different Formulas In One Cell?

Aug 2, 2014

I can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.

View 8 Replies View Related

IF Blank Or If Not Blank Function That Works With Adjacent Cell With Formula In It?

Aug 12, 2014

I have one column that contains an If statement formula and would like the next column to then work off of the first column (i.e. if that 1st column returns a value then then adjacent column uses that result).

What is happening now is that it is returning #value (because I guess technically the cell isn't blank?)

View 5 Replies View Related

"istext" Behaviour/recognition

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

Keep Cell Blank If Referencing Cell Within IF Formula Is Blank

Jul 28, 2014

My workbook is for financial planning but I'm attempting to streamline an input page (name, birthdate, etc) that will be referenced throughout the entire workbook to trigger automatic calculations (present value, education calculations, etc).

The cell in the input page is a birthdate - which when populated will trigger a cell on a different worksheet to calculate the respective age using this formula:

Code:
=IF(MONTH(TODAY())>MONTH('Input Page'!B30),YEAR(TODAY())-YEAR('Input Page'!B30), IF(AND(MONTH(TODAY())=MONTH('Input Page'!B30),DAY(TODAY())>=DAY('Input Page'!B30)),
YEAR(TODAY())-YEAR('Input Page'!B30),(YEAR(TODAY())-YEAR('Input Page'!B30))-1))

The problem is if there is nothing written in the birthdate cell then the age cell will automatically calculate 114 (reference photo below). Ideally I'd like that cell to be blank if nothing is in the birthdate cell on the input worksheet. I'm assuming since I'm using an IF formula to calculate the age already then I'll need to use a macro to to an "ignore".

[URL] ...........

View 2 Replies View Related

Do Not Want To Add An IF Formula If Value Is Blank

Sep 8, 2008

I have an IF formula that says if there is no data on an specific cell, then blank the cell. My problem is that when I try to count cells with data on the cells that have the formula, it is counting the blanks too. How can I make those blanks cells not to count?

View 13 Replies View Related

If Blank Formula

Oct 4, 2008

I have two cells G151 & G152

In G152 there's total

But in Cell G151 there's no total

what I'm trying to do if G152 has a total but G151 has no total to not show the total until both cells have totals.

View 9 Replies View Related

Formula- Blank Cells

Jan 22, 2008

i've got a pricing spreadsheet that's doing my head in. hopefully someone can help me with my latest formula issue.

when i expor the file as a pdf, you loose the column names. so...

a b c d e f g h
part ID qty 1 price qty 2 price qty 3 price discount price

i want to calculate the discount price as a perctange of either b/c or d/e or f/g groupinsg (qty break & price for that paricular qty amt). The problem is b/c are populated for all rows. some parts have a secdonary price break (d/e) and some have a tiertary price break (f/g).

I want to take the percentge discount off the high qty price break. So if f/g is populated I want to take it from that. If it's not populated, then e/f. and Only if f/g & e/f are NOT populated do i want to take the discount from b/c. is there a formula i can insert into H to do this?

View 9 Replies View Related

Formula For Blank Return

Mar 5, 2009

=IF(Q20+R20+S20>0,Q20+R20+S20,"")

V20
=SUM(T20*O20)

V20 gives me #VALUE

How can I have V20 blank if T21 is blank?

View 2 Replies View Related

Insert Blank Row With Formula

Jul 4, 2013

I have table as show below and i need to insert a blank row after every row and insert a constant data on clolumn B and C and formula on Coloums D, E , F , G ,H and I

Constant data on B : INS Acc
Constant Data on C : INS Desc

Formula:
DX= D(X-1) X- Cell number
EX= F(X-1)
FX= E(X-1)
GX= H(X-1)
HX= G(X-1)

[Code] ........

Expected Output:

Line
Acc
Desc
Cur
ED
EC
AC
AD
Date

[Code] ........

View 1 Replies View Related

Mutliply By Zero Or Blank Formula

May 18, 2009

I am trying to calculate for when a cell is blank or equals zero. I want the reference to give me the number one instead of a blank or the zero value but i keep coming up with "true or false"

Scenario

B24 = $50,000 (this will always have a positive value in it)

B25 can be blank or 0 due to user error. It would normally be a range from 0.60 to 1.50
B26 same as above

I need to obtain the value in B27 for
sum (( b24 * b25 ) * b26)

View 9 Replies View Related

Formula Count If Blank

Feb 3, 2010

On sheet4 i need the total from sheet1

here is what i'm looking for in sheet1

IF COLM A2 to A600 contains the name "AUSTIN" and if in colm M cell is blank then add total to sheet4

View 9 Replies View Related

Formula Which Changes If Cell Is Blank

Jul 19, 2006

I have a spreadsheet which projects the amount of work on hand each week. It runs up to the end of the year and shows the estimated figures and then each week I input the actual figures. The way it works at the moment is that it looks at the estimated work on hand, adds the work on which has been received and then takes off the work that has been cleared...this then gives an estimated level of on hand work.

What I want it to do is to look at the cell which would show the actual work on hand and if thats empty then go on to look at the estimated work on hand. In other words - I want a formula that looks at a cell to see if there's a figure in it and if not uses another cell instead. I have tried: =If(u5=0,r5+y5-z5)

but when I input a figure into U5 it inputs FALSE into the cell. I don't want it to do that, I want it to look in cell U5 and if there's something there then do U5+Y5-Z5.....and if U5 is empty then do R5+Y5-Z5

View 4 Replies View Related

If Formula Leave Blank With Condition

Apr 1, 2014

On the attached worksheet if the data in col F changes from FLT to AWF,col M will flag up "faw".I would like to leave a blank cell instead of false if the condition is not met. Col P is an example of the result im trying to achieve.

IF TEST.xlsx‎

View 2 Replies View Related

Displaying Blank Entries In A Formula?

Jul 22, 2014

I am trying to display a blank entry as a blank instead of Jan 00. I have tried the following formula but no joy?

=IF''"&$A30&''!B:B"="";"";MAX(INDIRECT("'"&$A30&"'!B:B))+$B$4)

View 14 Replies View Related

Formula Blank If Columns Not Filled Out Yet

Aug 10, 2014

I have this formula:

=DATEDIF(C3,I3,"MD")

However i need that formula to be blank if columns C and I are not filled out yet

View 2 Replies View Related

Copy And Paste One Formula Into Every Other Blank Row?

Jan 21, 2014

way to copy and paste a formula into every other blank row?

The formula will be staying in the same columns the whole way down the sheet. Eg: A1, A2, A3 ..... ETC

View 1 Replies View Related

Cells With Numbers Appear Blank In Formula

Feb 26, 2014

I have been trying to import CSV files into Excel on my new computer. I previously had a windows 7 system with office 2007. I have now moved across to a windows 8 system with office 2013. The problem I am faced with is the CSV files which I imported without problems on my old system don't seem to work on the new one. The excel spreadsheet shows the numbers, but when I try and use any formula, the cell comes up with 0, as if there is nothing in the cell. When using my old system, it shows that it is a picture that needs to be converted to text, but I cannot find out how to sort this out in Windows 8. I have even tried various versions of office, 2007, and 2010 on the windows 8 system with the same problem. I have tried all the things I have found online, checking the regional settings and those type of things.

View 10 Replies View Related

Formula In Cell Show Blank If Zero

Jun 4, 2009

I have a spreadsheet which imports data from worksheets week 1, 2 etc to a monthly summary.
Is there any way I can show a cell as blank if the return equals 0?

I'm sure the resolution is probably simple but then so am I.

View 6 Replies View Related

Formula To Leave A Cell Blank

Aug 25, 2009

Probably a simple method that i have not tried. I have a table where some cells are populated with #N/A. I am trying to work out a formula in another cell saying if the cell that contains #N/A then leave this cell blank.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved