Conditional, Variable Formula: Leave Blank Or Put A 1

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


ADVERTISEMENT

Conditional Format; Put 1, 0, Or Leave Blank

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

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

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

Hide Formula And Leave Cell Blank?

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

If No Data Leave It Blank (cell Contains Formula)

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

Leave Cell Blank From Formula Until Data Is Posted

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

Formula To Sort And Leave Spaces Blank Where One Column Doenst Have The Same Value

Aug 15, 2008

Every AM I run a report that has ALL of our company order numbers from the 2 systems we use. I get those reports and put them into 2 columns. instead of manually inserting so they all match up, is there a way to do this automatically? ....

View 9 Replies View Related

Excel 2003 :: Write Formula That Will Leave Cell Blank If Nothing Is Entered?

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

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.

View 8 Replies View Related

IF SUM Unless Another Cell Has A Value Then Leave Blank?

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

Leave The Cell If Its Blank

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

Leave Cells Blank Without Zeros

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

Leave Target Cell Blank

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

Sum If Values Entered If Not Leave Blank

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

If Tab Exists Put Cell, If Not, Leave Blank

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

Add Time (minutes) Or Leave Cell Blank

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

Leave Other Cells Blank If No Data In Cell

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

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

Summing Cells That Contain IF Statement To Leave Cell Blank If Zero

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

Leave Fields Blank Until Number Is Entered In Other Field?

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

Sort Rows Based On Two Columns And Leave Blank Row Between Them

Jul 23, 2014

I am trying to create a macro that sorts rows based on value of 2 columns, but every time that a value changes in either of them to leave an empty row between them. Is that possible? In simple words i want to separate my rows with the empty row while sorting them out alphabetically based on two columns.

Please check my screen shots. Test 1 is my spreadsheet before the macro. Test 2 is how i need it to be after the macro. The two columns that i want it sorted out is First GDS then Rate.

View 6 Replies View Related

If A1,A2orA3 Is Greater Than 0 Then Multiply By 2 Or Leave The Cell Blank

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

Count Number Of Cells In Column That Contain Specific Value But Leave Blank If None?

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

SUMIFS - If No Criteria Match Leave Cell Blank Else Return Value

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

Combining Names: If The Cell Is Blank The Result Should Not Leave A Space

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

Conditional Format - Formula For Blank Cells

Jul 1, 2012

I am using a conditional formula to compare if a cell in column A is higher than a cell in column B. If a cell is blank the cell is formatting. I am trying to highlight only cells with numbers. Any formula to place in the conditional format formula that will evaluate the cells excluding a blank cell?

ie: grapes and pears should be highlighted because value in column B is less than column A but the conditional format is highlighting apples also where apples should have no highlighting because the value in column B is none or blank.

A B
oranges 2.49 3.25
grapes 1.99 1.89
apples 1.00
pears 1.11 1.03

View 2 Replies View Related

Conditional Format Formula: Allow The User To Blank Out The Cell Within The Year

Sep 26, 2009

i have a set of conditional formatting set up for my workbook, the first is simply that if the result is false the cell is blank(white text,white background). the second if positive result,keeps the text black till 1 year has passed. the problem is the third, i.m trying to allow the user to blank out the cell within the year (white text,white background)if certain criteria are met. the idea is that they change the entry in cell D from T to TX or from M to MX and the formula pick this up, but i keep getting a error saying "your formula contains an error", the formula im using is, =if(or($D5="tx","mx")) then i set the format to (white text,white background). its probably really simple but i,ve been trying to solve this now for around 8 hours and i.m stuck

View 2 Replies View Related

Keeping Cells With Formula And Conditional Formatting Blank Until Data Entered?

Feb 22, 2013

I have a spreadsheet filled with formulas that depend on a value being entered into A2, A3, A4, etc... So column A starting at A2 is where I will manually input a number and the formulas I have in columns B, C, and D will import information from another sheet based off what is put in column A. In column D the formula I have to import data

is =IFERROR(VLOOKUP(A2,Master!C:M,11,0),"").

This will import another number. Additionally in column D, I have conditional formatting that will return a red, yellow or green light based off the rule I have in place. Everything works fine, the only problem is that column D has a green light all the way down even without a value being placed in column A. I would like to find out a way to keep the cells in column D blank until a value is entered in column A. Also, if I go back and delete the value in column A, I would like the corresponding cell in column D to go back to blank as well.

View 2 Replies View Related

Employee Leave Tracker & Check Leave Time Is Valid

Feb 27, 2008

I need to create a leave tracker wherein i will get the names of the person who want to take leave in that particular month. I have prepared a calendar to make an entry. I need to take care of the following things

1) The person must have sufficient leave balance available to take the leave. so i have to first check if he/she has sufficient leave balance.

2) There are four team leaders and each team has around 13-14 members. so if lots of people from the same team apply for the leave then they will not be eligible for leave. this i want to know in percentage as to how much percentage of people are taking leave from that particular team.

View 5 Replies View Related







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