Leave Other Cells Blank If No Data In Cell
May 9, 2009
I have 2 similar question.
I'm using a formula that I got from a previously made excel sheet. The formula does what I need it to do and looks like this:
=INT((D10-10)/2)
The problem is, if I don't enter a number in the cell D10, all the cells with this formula show -5.
If I don't want data entered into D10 yet, I'd like all the cells with that formula to be blank until I actually enter a number in D10.
I think it has to do with using an IF statement followed with ""? Am I on the right track?
Also, if I have other formulas like =SUM(AP3:AQ6), but the cells it refers to are blank, how can I make the cell with the formula also be blank rather than show a 0?
I know I can turn off the "Show a zero in cells that have zero value" option, but I was wondering how to do it with the formula instead.
View 9 Replies
ADVERTISEMENT
May 9, 2009
I'm using a formula that I got from a previously made excel sheet. The formula does what I need it to do and looks like this: =INT((D10-10)/2)
The problem is, if I don't enter a number in the cell D10, all the cells with this formula show -5.
If I don't want data entered into D10 yet, I'd like all the cells with that formula to be blank until I actually enter a number in D10.
I think it has to do with using an IF statement followed with ""? Am I on the right track?
Also, if I have other formulas like =SUM(AP3:AQ6), but the cells it refers to are blank, how can I make the cell with the formula also be blank rather than show a 0?
I know I can turn off the "Show a zero in cells that have zero value" option, but I was wondering how to do it with the formula instead.
View 9 Replies
View Related
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.
View 8 Replies
View Related
Sep 13, 2013
I am making a spreadsheet for use by my customers. Is there a way to leave cells that have formulas' in blank until the cells that make up the formula have entries in?
View 5 Replies
View Related
Mar 14, 2014
I need to sum cells that contain this formula: =if($c$5=0,"",c5*b5). I am getting a wrong data type value error.
View 7 Replies
View Related
Mar 31, 2007
A1 has a date of 03/10/07
B2 has formula =A1 + 7
I want to copy that formula down the column B2 but if
B3 is empty I get 01/07/1900
How Do I make so that if there's no date entered in
A column I get blank cells but still retain formulas,
I've been running into this issue with other formulas but I couldn't find any solution searching this forum,
View 9 Replies
View Related
May 22, 2007
I'm trying to create a worksheet to automatically make a football league table update once results are entered. I have entered a formula using the "IF" function which compares scores and then awards points. However before a game is played and the cells are left blank, the formula detects this as a "draw" and awards a point. how to prevent the formula operating until a result is entered?
View 7 Replies
View Related
Jul 8, 2014
I am wanting to have certain cells of data from one worksheet appear on another worksheet once the cells on the first sheet have data entered in them.
I find that I can do a simple formula to copy the cell but I want the cell to remain blank until data is added in the first sheet (it will be a mix of text and numerical entries).
View 4 Replies
View Related
Sep 19, 2009
i have a mock up football issue here i want to enter scores in sheet 1 and they automatically fill sheet 2 etc i know i can just =and copy sheet 1 A5 and so so but that leaves zeros which will start allmy formulas with 1 point i need a formula that will leave sheet 2 3 etc blank untill any score is entered
View 3 Replies
View Related
Mar 6, 2014
I have a spreadsheet that contains given answers to a multiple choice test. I want to count the number of times each possible answer has been chosen at the bottom of the column. I have tried to use COUNTIF and that works fine to give me the number times each answer has been chosen but there is just one annoying thing. If the given answer hasn't been chosed by anyone, a "0" is automatically entered into the cell. This tends to really clutter up the spreadsheet and I would prefer for the cell to be left blank if the answer hasn't been chosen by anyone.
The closest I can come up with is: {=IF(D1:D10="","",COUNTIF(D1:D10,"A"))} but unless the answer "A" is chosed in D1, the cell remains blank.
If "A" is chosed in D1, then the formula works and counts all the rest of the cells that have "A" as an answer.
View 2 Replies
View Related
Apr 20, 2014
I have a 'Total' Cell (I23) that has the following Formula: =IF(SUM(D23:H23)>0,SUM(D23:H23),"")
But I would only like the formula to 'work' if another "Total" cell (I57) further down the sheet is Blank.Therefore if Cell I57 has a value, the Cell I23 is left Blank.
View 1 Replies
View Related
Nov 23, 2007
I'm want this formula, =IF($C30="Y",$A29+1,""), to leave cell A29 blank if cell C30 doesn't have a "Y" in it. If cell C30 does have a "Y" in it then I want to add one day to cell A29. Cell A30 should return 28-Feb, but in stead it returns 0-Jan.
************************************************************************>Microsoft Excel - 2007log.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA26B26A27B27A28B28A29B29A30B30C30A31B31C31A32B32C32A33B33C33=
ABCD2624-FebSat**2725-FebSun**2826-FebMon**2927-FebTue**300-JanWedY*310-JanThuN*320-JanFriN*330-JanSatN*Feb*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
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
Feb 22, 2010
Every time I think I've got this thing beat, they throw another curve at me!
If cell Q19 is blank, leave target cell blank
If cell Q19 is not blank, return the value of cell E$4
View 2 Replies
View Related
Feb 26, 2007
Excel Version: 2000
Workbook has the possiblities of 366 tabs at the bottom, but for a normal calendar year will only have 26.
The tab name is the ending date of the pay period without the year. So for Feb 25, 2007 the tab would be named 0225
On a yearly calculations page I am simply trying to say IF TAB 0225 exists then cell equals '0225'!A2 otherwise the cell should be blank.
I tried this, but gets a reference failure instead of putting nothing...
=IF('0102'!A2=0,"",'0102'!A2)
In this example the possible tab name would be 0102. If the tab exists, everything works fine, but since in this case it doesn't, I get a reference failure. #REF!
I NEED the result to be empty if the tab doesn't exist.
Any idea would be helpful. I'd rather not have 366 tabs when only 26 are needed for any calendar year.
View 9 Replies
View Related
Jun 18, 2014
cell A1 has the time (09:00), cell A2 has the minutes (60), cell A3 is the sum of A1+A2.
Im using this formula =A1+TIME(0,A2,0) - which is fine, except A1 is sometimes blank, so therefore I would like A3 to be blank.
I thought I could use this: =IF(A1,"","",(A1+TIME(0,A2,0)) But it doesn't work.
View 2 Replies
View Related
Mar 31, 2013
I am creating a worksheet for my buisness and I have input date of birth and have done a formula to work out ages but I dont know how to hide the formula and leave the cell blank so that when I create new information it automatically puts the age in.
View 1 Replies
View Related
Jan 14, 2010
=IF(OR(A1>0,A2>0,A3>0),*2,"")
if A1,A2orA3 is greater than 0 then multpy by 2 or leave the cell blank
View 9 Replies
View Related
Nov 5, 2011
I'm trying to set up my formula so that if there is no criteria match, rather than returning 0 it leaves the cell blank.
(I recommend skipping the below text and just opening the attached spreadsheet, instructions/what I'm looking for and trying to achieve and well laid out requirements and examples are in the spreadsheet and much more thoroughly explained then I can do below)
I'm setting up a table that grabs data from columns in a raw data spreadsheet.
For the purpose of the table I'm making, the formula needs to match up both the House, and the year/month from the raw data sheet, then if the two match and are side by side, return the value to the right either in the Plan Field or the actuals field depending on which field in the table the formula is in. If there is no entry or matching criteria for both the house and Month then it should leave the cell blank, not show a 0
So I have a SUMIFS formula that I'm trying to set up so that if there is no criteria match it leaves the cell blank.
So =SUMIFS(sum range, criteria& range, criteria&range), if no matching criteria can be found, leave cell blank, else if matching criteria found then return value from the sumrange.
This is the exact formula I am using:
=SUMIFS(INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$B9, 'Raw Data'!$1:$1, 0)), INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$A$8, 'Raw Data'!$1:$1, 0)), Table!$A$9, INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$B$8, 'Raw Data'!$1:$1, 0)), TEXT(Table!C$8, "yyyymm"))
Yeah kind of hard to follow, so attached is a very detailed and well laid out spreadsheet showing the example of what the formula is, how it's working/what it's returning and an example of what i want it to return/look like, so please check out the spreadsheet, it explains it a million times better.
Essentially though i want the formula set up so that if there is no criteria match, rather than returning 0 it leaves the cell blank.
View 3 Replies
View Related
Jan 16, 2009
I have 6 columns and would like to combine them. If the cell is blank the result should not leave a space.
- Prefix
- First Name
- Last Name
- Middle Initial
- Last Name
- Suffix
Example: Mr. Henry J. Weeks, III
Example: Henry Weeks
Example: Mr. Weeks
Example: Henry J. Weeks, III
View 5 Replies
View Related
Sep 28, 2011
how to write a formula that will leave a cell blank if nothing is entered. I do not want it to show a 0 unless the cell entered is a 0.
In cell G16 - I am adding U46 and U58 together.
I do not have a problem when it is 1 cell - my formula works fine. When I have 2 cells added together, the formula does not work.
View 7 Replies
View Related
Nov 19, 2013
I have made a table which contains 2 date columns, Both formatted as date (dd-mmm-yy), the second column is calculated as the first column, + 30, The dates work out fine when adding to a date in the first columns, but from a blank cell it displays 30-Jan-00.
e.g
Started
Finishes
12-Nov-13
12-Dec-13
30-Jan-00
09-Nov-13
09-Dec-13
11-Nov-13
11-Dec-13
Rather than delete the rows with the blanks in the Started column, is there a formula that I can use so that the cells in the "Finishes" column is left blank when the "Started" cells are blank?
The current formula for cells in the "Finished" column is:
=[@Started]+30
View 2 Replies
View Related
Dec 19, 2007
The worksheet (Payments) contains a list that allows the user to input accounting transactions. Almost every input cell contains a formula or data validation enabling selection from a dropdown.
The worksheet feeds the data to 3 separate spreadsheets (Debits / Credits / VAT) and the formulas for that are contained within dynamic ranges in the other 3 sheets. A command button macro then feeds that data from the 3 sheets to the Master sheet. This all works perfectly.
After the macro is run and the data is copied to the Master sheet, I would like all of the data in the 4 other sheets to be cleared without losing the formulas and data validation, ie so that the sheets are empty and clear for the next batch of inputs.
View 5 Replies
View Related
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
Feb 2, 2009
I have cells B5:AL22 I want to put 1, 0, or leave blank. I need the zero (0) to turn red with white letter, the 1 and blank cells to remain in the present format. I can get the 1 and zero (0), but I don't know how to get the blank cells to stay in regular format.
View 3 Replies
View Related
Feb 12, 2010
I need a formula that will do the following:
Sum K4 (unit price) and M4 (shipping) and return the answer in N4 (total), if K4 and M4 are empty then leave cell N4 blank
All responses will be welcome as this has been driving me mad for over an hour, and the answer is probably so simple!
View 9 Replies
View Related
Dec 24, 2009
What i want to do is clear some data within a merged cell but leave the rest.
I have added a sample workbook. The data i want to clear is highlighted in Red so the 11111 will be replaced with ...... and the 09/12/09 will also be replaced with .......
View 7 Replies
View Related
Dec 17, 2008
I could really do with is this: =IF(F4>0,F4*3.4%+0.2,"") but I need to be able to make it either 3.4% OR 3.9%. What I thought was if I use another cell, say F3 which I can leave blank or put a 1 in, and tell it if it has a 1 it is 3.9% + 0.20 or if F3 is blank then it is 3.4% + 0.20.
View 3 Replies
View Related
Jul 28, 2014
When i use
Code:
=IF(ISBLANK(I31);"";I31-IF(ISNUMBER(J31);J31;0))
The cell gives me an error, i think because I31 has a formula that returns no value.
How can i stop the formula if cell I31 gives me no data??
View 5 Replies
View Related
Jan 15, 2014
I created this formula =G2*2.9%+.3
what I am trying to do is take the number in the G column multiply it by 2.9% and add 0.30. For instance if 20.00 is in the G2 cell, the number I want the formula to produce is .88
the formula works for me but what happens is the rest of my sheet that does not have any numbers in the G column gets filled with .30
How do I prevent the formula from calculating if the G column is blank?
View 3 Replies
View Related
