# Sum Returning Blank Cell

May 28, 2009

I have a current formula using SUM formulas obviously in the answer cell the answer is returning 0 due to no data inputed, how do i make the cell show empty.

eg. =SUM(A1*A3) returns answer 0 need cell to be blank.

## Returning 0 From Blank Cell

Jan 7, 2013

I'm linking one sheet in a workbook to another. The formula is simple,

If there is data in the cell, it replicates it which is what I'm after. However if there is no data in the cell it returns a "0", not what I want at all.

## Formula Cell To Be Blank (Vlookup Returning #N/A)

Nov 13, 2008

If it can not locate the correct responce I want it the formula cell to be blank. How do I adjust my formula to do this?

This is the forumal I am currently using
=VLOOKUP(A26,Subs!A:F,5,0)

I have also tried this one as well
=IF(ISNA(VLOOKUP(A6,Subs!A:E,5,0))+(VLOOKUP(A6,Subs!A:E,5,0)=""),"",VLOOKUP(A6,Subs!A:E,5,0))

## How To Get Referenced Cells Returning Blank Cell Instead Of Zero

Apr 6, 2014

Referenced cells returning zeros: can these be made blank or string data?

But why I still get the value 0 instead of blank cell when I press ctrl+shift+enter?

My formula = IF(U15="Bank", PROPER(E15),"")
and E15 = VLOOKUP(B15,'Customer Data'!\$D\$6:\$K\$100,3,FALSE)

## Trigger Formula - Returning Blank Cell

Dec 19, 2011

I am trying to trigger a formula if a cell (B2) is populated (with anything) but if it is blank for the destination cell to remain blank.

The formula I am trying to trigger is =IF(G18=0,100,100-(100/(1+H18))).

## Returning 0 Instead Of Blank

Oct 16, 2012

Below formula matches two criteria

=SUMPRODUCT(('[Value.xlsx]test!\$B\$5:\$B\$50=\$B5)*('[Value.xlsx]test'!\$C\$5:\$C\$50=\$C5)*('[Value,xlsx]test'!D\$5:D\$50)).

What it does is:

Matching B5 value in range B5:B10 of tab "test" in worksheet "Value"

Matching C5 value in range C5:C10 of tab "test" in worksheet "Value"

AND capturing the matching value in range D5:D50.

The problem is that i am getting 0 if the matching value is a blank or 0.

I want the formula to return nothing (i.e. blank) when the matching value is blank and 0 only when the matching value is actually 0.

## Returning Blank Where Criteria Not Met?

Oct 22, 2011

I am using the following formula to return an "F" in the cell. If there is no "F" it just returns "#N/A". Is it possible to have the cell blank instead of the #N/A? If so how do I do it?

=INDEX(E1:E99,MATCH(1,(A1:A99=Selections!J5)*(E1:E99="F"),0))

and then ctrl + shift + enter

## Nested IF / AND Returning 0 Instead Of Specified Blank?

Apr 14, 2014

My formula is returning a 0 when true instead of being blank as specified. If I designate a value other than "blank" (i.e. "A Word") it still returns a 0. What am I doing wrong?

=IF(AND(CBAuditType="PHYSICAL",TxtInsuredName=""),"",TxtInsuredName)

## Returning Last Non-blank Value In Horizontal Range?

Apr 25, 2014

I want to be able to return the value of the last non-blank cell in the horizontal range L3:BF3. This would need to account for any blank cells that might occur within the range. Those 'blank' cells would contain formulae and the non-blank cells would contain GCSE grades from A* down to U or National Curriculum levels in the format nx (e.g. 3a). I would need to be able to copy the formula down to other rows. The reason I'm not keen on an array formula is because they can slow things down, as I understand it.

I'm afraid I don't have a sample worksheet as this is all, for now at least, an emerging plan in my head. I should probably add that the grades will not necessarily increase in value in the range, so we're not looking for the largest.

## Excel Web Query Is Returning Blank Data?

Jun 23, 2013

I am trying to get data in excel sheet using web query from the following web link:

But I the data returned is a 'hypen' (i.e. a dash symbol). I am getting the row/column headers but not the figures or the numbers under them.

## Combining TODAY Formula And Returning Blank Value?

Apr 11, 2014

I'm currently using the following formula to return either a "PAST DUE" or "DUE" value in a particular cell based on the date entered in the cell in the previous column.

=IF(AND(TODAY()>=BG43+5,TODAY()=BG43+10,"PAST DUE",""))

However, if the reference cell is blank the formula automatically triggers to put in "PAST DUE" as the value. I would like to modify this formula so if the reference cell is blank that this cell will also remain blank. I've tried a few different options, but I'm getting an "too many arguments" error on what I've tried.

## Find First Blank Row Ignoring Formulas Returning Empty Text

Oct 2, 2007

I have a range that is filled with formulae and the the number of rows in the range with data changes as the formulae reevaluate the inputs. So the rows with visible data changes between one row and 200 rows. By looking at the output in the first column of the range, Col M and finding the last row where the formulae returns data, I need a way of selecting across the columns of the range (M1 through to Qi) where i is the number of rows that have data in.

## Referenced Cells Returning Zeros: Can These Be Made Blank Or String Data?

Aug 30, 2007

I have a summary worksheet consisting of columns that are referenced to other sheets within the same workbook.

If the cell in the 'referring' (original) sheet is blank then the forumula returns a zero instead of a blank in my summary worksheet, which is messing up some other calculations.

Is there a way to make these cells truly blank, or possibly into string variables instead, so that they do not interfere with my calculations?

## 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.

## 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?)

## IF Statement To Leave Cell Blank If Multiple Cells Are All Blank?

Mar 12, 2014

I am looking for an IF statement that would leave a balance cell blank if both the revenue and expense cells are blank, otherwise a formula would be calculated.

## How To Populate Blank Cells With Sequence Until Non Blank Cell Is Encountered

Mar 22, 2014

I presume this is fairly simple to do, since it's certainly easy enough to do manually by filling in a couple of rows and dragging them down, but I need it to be performed in a macro that I can run before other macros run.

What I need specifically is for the macro to go to G1 and insert the number .01... Then go to G2 and insert .02... Then G3 and insert .03... And repeat this until it finds the first non-blank cell ( row number this occurs at varies), at which point it ends and does nothing to that populated cell or any other cell in the column thereafter (including other blanks farther down).

This all needs to be done in Arial, 10pt, white.

## List - Filling In Each Blank Cells With Value Contained In First Non-blank Cell Above It

Feb 27, 2013

I have a list that looks something like this:

Column B

Row 4 Item 1
Row 5 Item 2
Row 6
Row 7
Row 8 Item 3
Row 9
Row 10 Item 4
Row 11
Row 12
Row 13
Row 14 Item 5

The range of cells in column B containing the items has a name "ColStreams"

I need to go through the list, filling in each blank cells with the value contained in the first non-blank cell above it - so, in this case, rows 6 and 7 would contain "Item 2", row 9 would contain "Item 3", rows 11-13 would contain "Item 4" and so on.

## Replace #VALUE! Error For Blank Cells With Blank Cell

Nov 17, 2008

The formula below calculates appropriately, however, if any of the cells (E12,E14, E21, E22, E28, E29) are blank, it returns a #VALUE! error. I would like the cell to remain blank. How can I do this? The formula is listed below.

=(SUM(E34-E6)-(SUM((E12)+E14)-(SUM((E21)+E22)-(SUM(E28)+E29))))

## Making A Cell Blank Referring To Another Blank Cell?

May 8, 2014

Im currently writing a payroll sheet within excell and trying to write an IF statement to make a cell blank if referring to another blank cell but it is showing the formula is incorrect. Below is the following IF statement that im using;

=IF( F4 <97, AM4*0.8, IF( F4 >=97, AM4*1.1, IF( F4=" ";" "; AM4)))

## 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] ...........

## Insert Blank Row If Adjacent Cell Value Is Blank

Jan 7, 2013

If the value of the adjacent cell in column A is blank, insert a blank row through Column B:J.

## Return Blank If Concatenating With Blank Cell?

Apr 3, 2014

in one column of my spreadsheet and it is returning the correct concatenation for the appropriate lines. However, I wish to have the column's cells return an answer of blank (" ") if B15, B16... etc is blank. In other words, at the moment, a correct answer would read something like '1810sd0000' C10 and B15 used, while an incorrect answer would still display '1810', but I want it to be a blank cell.

## Copy Or Create 'blank Cell' As 'blank'

Feb 4, 2009

using a formula to copy a cell A1. if A1 is blank, i need forumula result in blank instead 0...is it possible..

## First Blank Cell In Column & Offset To 1st Blank In Row

Jul 11, 2007

I have checked archives, some similiar but not quite what I want.

In code below I want the output instead of going offset one column to right in same row to go one column to right into the next blank cell.

## Cell Returning #n/a

Mar 29, 2008

i have this formula in cell e28 =IF(B28<>"",LOOKUP(B28,STOCK!A1:A500,STOCK!B1:B500),"") and when its empty it returns a blank cell

but in cell e29 i have exactly the same formula =IF(B29<>"",LOOKUP(B29,STOCK!A1:A500,STOCK!B1:B500),"") but i returns #n/a

why is this and how do i stop it i want the cell to be empty unless there is a code entered in b29 in which case it will look up the relevant info for me

## Looking For Value In Column Then Returning Value From Different Cell In That Row

Apr 23, 2014

Basically, I want to use look in one cell to get a value then in a table look in a coloum to find the same value then return a value from a different column in that row.

This is to organize groups of children participating in an event.

See the attached spreadsheet for details.

Groups example.xls‎

## Returning The Value Of The Larger Cell

Feb 5, 2009

I am looking to return the larger value of 2 cells into a seperate cell.

## Empty Cell Not Returning Zero

Jul 30, 2009

I had this problem a few days ago and NVBC suggested Text to Columns, which worked initally but didn't seem to 'save' or stick... also, I'm not sure what is causing the problem to begin with. It was my understanding that Excel couldn't return a truely 'blank' value. If a cell was blank or empty, Excel still 'sees' it as Zero. WHY then, for the Love of Gummi Bears, will Excel not calculate my formula. It's a simple formula!

=A13+B13

I've tried
=SUM(A13+B13)

I've tried
=IF(ISERROR(A13+B13),"",A13+B13)

But if don't TYPE in a 0 or a number, then it just does nothing. 50 + 0 = should be 50 not blank or #VALUE!. 50 + Nothing = should be 50 not blank or #VALUE!

Attached is a small copy of what I'm trying to do. I've double checked that all number cells are formatted to numbers, not text. I tried the Data:Text to Columns trick. I've tried Tools:Options:Show Zeros. I've tried Manual and Automatic Calculation. They shouldn't have to type in 60 cells of Zero to get the simple calculation.

## Returning Cell With A YES Or No Calculation?

Nov 11, 2013

I am trying to return a cell with a simple yes or no answer and having a little difficulty.

I have entered the following formula which is returning #NAME
=IF(E4<=1.25,ORH4<=0.01,yes)
Calculation needs to be as follows:

Install Cost <1.25 and Profit atleast 0.01
The YES NO needs to be in the Invite? column