#### Blanks as a condition of a statement

How is it possible to do a function based on if a cell is
blank or not. Such as the following for cell a1:
IF b1 is not blank then add b1-c1 else don't do anything in
a1.
I have tried but can't get anything to go.
Lee
 0
Stuck
11/21/2005 10:52:35 PM
excel.newusers 15348 articles. 2 followers.

2 Replies
511 Views

Similar Articles

[PageSpeed] 33

=IF(B1<>"",B1-C1,"")

--

HTH

RP
(remove nothere from the email address if mailing direct)

<Stuck on Blanks> wrote in message news:4383062f.5bc.41@news2...
> How is it possible to do a function based on if a cell is
> blank or not. Such as the following for cell a1:
> IF b1 is not blank then add b1-c1 else don't do anything in
> a1.
> I have tried but can't get anything to go.
> Lee

 0
bob.phillips1 (6510)
11/21/2005 11:02:11 PM
Thanks, it worked very well. Again thank you.
Lee
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OB9yb%23u7FHA.1420@TK2MSFTNGP09.phx.gbl...
> =IF(B1<>"",B1-C1,"")
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> <Stuck on Blanks> wrote in message news:4383062f.5bc.41@news2...
>> How is it possible to do a function based on if a cell is
>> blank or not. Such as the following for cell a1:
>> IF b1 is not blank then add b1-c1 else don't do anything in
>> a1.
>> I have tried but can't get anything to go.
>> Lee
>
>

 0
11/23/2005 12:24:53 AM

Similar Artilces:

Multiple Sumif conditions
I have a list of data by date of power production. What I want to know is how much power was generated in December 2009, January 2010, etc. The data in a sheet with the dates in column A and power production in column B. I have another tab with the months in the rows and the years on the columns. I am sure there is a way to do this with sumproduct, but i am not sure how to. Thanks in advance. Suppose you have data in Shee1 ColA with dates and Sheet2 with power production. In Sheet2 arrange months and year as below and try the below formula in cell B2 and copy down/across a...

Show blank cells as red with conditional formatting
Hello, what formula can I use with the conditional formatting dialog to show cells that are empty as the color red. Thanks in advance bartmacl Click "Formula Is", and enter this: =A1="" And set your format. HTH, RD "bmac" <bart.macleod@blackpearl.com> wrote in message news:eNF0OOlUEHA.2408@tk2msftngp13.phx.gbl... > Hello, what formula can I use with the conditional formatting dialog to show > cells that are empty as the color red. > > Thanks in advance > > bartmacl > > Hi use the formula =ISBLANK(A1) if cell A1 is your ce...

User Conditional View
Is there a way to create conditional workbook and worksheet views based on the value of a particular cell? Below is what I'm trying to accomplish: I'm building a master budget template with multiple worksheets and not all departments will use all worksheets. So, on the main page of the workbook I want to have users select a department from a drop down menu and based on that selection I only want worksheets that pertain to that particular department to be displayed and worksheets that don't pertain to the department hidden. Additionally I want show/hide certain columns in so...

if between statement
I have a staffing spreadsheet and I need to have a multiple if statement and I cannot figure it out. I have A1 7:00 am, B2 4:00 PM B3 & B4 Break start and stop times, B5 & B6 Lunch break times, B7 & B8 Break times. I have the Following columns set in 15 minute increments. I want to receive a value of a "1", if It falls between A1 & B1, but blank if it falls between breaks and lunches. Your help would be appreciated. Thank you On Jan 6, 12:24=A0pm, Jimbo <coan....@gmail.com> wrote: > I have A1 7:00 am, B2 4:00 PM =A0B3 & B4 Break start and > s...

Open Blank Workbook?
When I open a workbook from Explorer, a second blank workbook opens as well. How do I turn this setting off? ...

Conditional Formatting help....
Hi All, Background: I have a cell That I'm checking these three conditions against. B5= 100%, C5 = Blank Three conditional formats: =AND((B5-C5)>=1%,(\$B5-C5)<=9%) (Background=YelloW (91% to 99%)) =AND((B5-C5)>=10%) - (Background =Red (below 90%)) =AND((C5>=B5) - (Background=Green (100% or greater)) Problem: Before any data is entered the cells are all colored red because the value in them is 0 by default. Is there a way to get around this? I would like the user to enter a 0, have the cell check the conditions, and then turn it ...

Conditional Formatting formula problem
Hi, I want to conditionally format a cell (C1), but here's the problem. I send off a form to someone and leave "Awaiting" in cell B1. I pu the date I sent off the form in cell A1. If the date exceeds 21 day from A1 then "Awaiting" in B1 is highlighted in bold red. However, i a form comes back I will type a date into B1, overwriting "Awaiting" to which I want the font to normalise. I hope that makes sense!! If you know the conditions for this I would be most grateful! Thanks, Dan -- Message posted from http://www.ExcelForum.com Dan, I think you mea...

Dates Within IF Statements
I am attempting to include dates within an IF statement for example =IF(P23>"01/04/04",L23, " ") I have tried with and without quotation marks. Neither work. Is i possible to incorporate greater than a specifiied date? Regards TON -- Message posted from http://www.ExcelForum.com Try: =IF(P23>DATEVALUE("01/04/04"),L23, " ") -- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot>com ---- "TONYC >" <<TONYC.1cigoo@excelforum-nospam.com> wrote in message news:TONYC.1cigoo@excelforum-nospam.com....

Ok, I've been using MS Money 2000 for some time now but have just now tried to use the online statements option to download my checking account statement into Money. Things were going along pretty well until I finished and clicked the 'Done' button. Since my account balance did not match up with the bank statement I started doing so looking around. It seems I must have mis-matched a downloaded transaction with the incorrect entry that was already in the check register. How do I fix this? In the check register it shows an 'E' in the cleared/reconciled column and I ...

rms database blank
Good evening, I want a RMS 2.0 (created with latest sp if possible) beucase I would to write a .NET library to manage rms db from external software. I have only rms 1.3, our partner now don't send sp from 1 year .. thank you cod ...

Combining SUM Function with Nested If Statement
Is it possible to combine a SUM Function with an IF statement that will allow me to add multiple cells, but leave the cell blank if the answer is zero? Here's what I tried, but to no avail. In cell F5, I have the following formula: =(SUM(C5:E5),IF(F5=0,"",(SUM(C5:E5))) Is there something wrong with the formula, or is the combination of functions/statements not allowed? Any help would be greatly appreciated. Sincerely, Josh =IF(SUM(C5:E5)=0,"",SUM(C5:E5)) Regards, Peo Sjoblom "Somecallmejosh" wrote: > Is it possible to combine a SUM Function wit...

Building the required SQL statement
Hello, I'm having difficulty writing a query and was hoping someone could help me out. I have a table 'orders' which contains numerous fields, of which 3 are of particular interest to me 'order_status', 'clientid1' and 'clientid2'. I also have a table 'clients' which has a primary key 'client id'. What I am trying to do is return all the data from the 'clients' table for the associated clientid1 and clientid2 where the order_status="conf". I get lost because of the need to get the info for clientid1 and clientid2. ...

Conditional Formating question
Is there a way to trigger a Conditional Formatting based on the value of another cell? Example, if Cell B10 = 1 then Cell A10 background color change to Orange? Thanks! mac Try this... Select cell A10 Goto the menu Format>Conditional Formatting Select the Formula Is option Enter this formula in the box on the right: =B10=1 Click the Format button Select the Patterns tab Select a color of your choice OK out -- Biff Microsoft Excel MVP "mac" <mac@nospan.com> wrote in message news:00D19CC2-1653-424F-A7F3-BF6DF456C490@microsoft.com... >...

RMS Custom Statements
I just switched from QuickSell to RMS. Today I ran statements for the first time. I would like to remove the MINIMUM PAYMENT line on my statements. My customers are expected to pay entire amount due. I don't want minimum payment due line on the statement. Can this be customized and removed? bentoncopcs@hotmail.com Thank you very much. open the statement.xml receipt template and remove this line and save. <ROW> "||Minimum Payment:|" AccountStatement.MinimumPayment </ROW> You will need to re-set the statement template in the Manager application. "Vicki&q...

Importing bank statement
When I import a file into MS Money 2004, it will show my bank balance and local balance are different. When I go into my acount to accept or change the entries from the bank in Money, there are no entries to accept or change. How can I fix this problem. Thank you for you help Ruben In microsoft.public.money, ruben wrote: >When I import a file into MS Money 2004, it will show my >bank balance and local balance are different. When I go >into my acount to accept or change the entries from the >bank in Money, there are no entries to accept or change. >How can I fix t...

Count if 3 different conditions exist
I have 3 columns of information. Column A has a date (01-Jan-05), column B has a name, column C has either F, Inf, or Inv. I want a formula that will count any date in column A that is January, and column B is Adam, and column C is F. -- AnnaV ------------------------------------------------------------------------ AnnaV's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9619 View this thread: http://www.excelforum.com/showthread.php?threadid=496989 Put the number of the month you're looking for in D1 (Jan=1, Feb=2, etc....), Name in D2, And ID's in D3...

Conditional Formatting Access 07
I can use the sorting feature on my date columns in a continuious form but when I add a Conditional Format equal to a simple date value to one of them, I get a message stating "You have entered an expression that has an invalid reference to the property page - The property may not exist or may not apply to the object you specified" This is true on all of my fields but when I remove the formating, the message does not appear. How do I stop this? What are you entering into the Conditional Formatting? -- Bob Larson Access World Forums Super Moderator Utter Access VIP Tutorials at...

Getting rid of blank space
I have 5 reports that have a part name as the header record which is then grouped on in the report and then a sub-report that have the heights, widths and quantities of the different parts for that part name. I have put every single CanShrink property to Yes (PartName Hdr, the sub-report control, detail section on the sub-report and the size of the sub-report is at a minimum height) and I still have blank spaces. On the grouping page I put the keep together as No for the Part Name. But still I get extra space. Craig You don't mention where you get the extra space. If there's a...

how to count#cells w/= value in other column and not count blank c
In column A, I have values equal to either YES, NO or NA. In column B, I have values equal to either YES, NO or NA. I want to count the # of cells in column B that equal to their counterpart in column A, but I do not want to count blank cells, ex: A B 1 Yes Yes 2 No No 3 NA No 4 5 Yes Yes 6 Yes Yes 7 8 No No 9 No No Result for B10 should be 6 as I do not want to count row 4 and row 7 thanks in advance! =SUMPRODUCT(--(A1:A9=B1:B9);--(NOT(ISBLANK(A1:A9)))) -- Regards! Stefi „aganoe” ezt írta: > In column A, I have values equal to either YES,...

Mutiple Conditional Formatting (more than 4)
Is there a way to have more than the 4 conditional formatting? I have a sheet that I want 12 conditional formatting. All looking in colum A and highlighting each one with a different color. Hi Phillip You need VBA to do this Here is a example that use the Change event of the worksheet Right click on a sheet tab and choose view code. Paste the event in there and press Alt-Q to go back to Excel. This example will only work in column A Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 Then Dim Number Number = Target.Value Select Case Number Case 1 To 5 Target.In...

Query Criteria IIf statement
I'm using MSAccess 2003 and this seems like a simple process, especially compared to some of the things I'm trying to do in my database. In the criteria of a query field, I am using the following IIf statement: IIf([FYAGR]>3000000,3,<4) [FYAGR] is a different field in the same query. When [FYAGR] is greater than 3,000,000, I get 3 as expected, but no matter how I word the range of numbers I'm looking for in the false part of the statement, my query returns no records. If I remove the IIf statement and place '<4' I get the correct range, but no ...

Conditional formatting in range
I have a range named "Range1" and have a conditional formatting putting letters into red colour if the condition meets "K6=1" When ever I insert a row between the range the conditional formatting formula is not copying, every time i have to use the format painter and copy it. Is there any code, any body pls help me thanks Go to: Tools<Options<Edit tab Make sure there is a check mark next to "Extend data range formats and formulas". Hope this fixes your problem. -- -Brigette "RE: VLOOKUP fORMULA" wrote: > I ha...

Conditional FormattingEOMONTH Test
Using Conditional Formatting I was trying to do an end of the mont check on a preceding cell date entry to control whether or not the cel after that one would show an orange border (the date entered in the cel had to be less than or equal to the EOMONTH of that date to give th subsequent cell an orange border). I could do similar tests with othe functions (e.g. MONTH, TODAY, Others) but when I tried to use EOMONT I'd get an error message ("You may not use references to othe worksheets or workbooks for Conditional Formatting criteria"). I wa able to do pretty much the same thing...

2003: Having trouble with conditional formatting with custom user function (Repost)
First posted a week ago to excel.programming. Thought I'd try again, adding excel and excel.worksheet.functions hoping for a reply this time. Office 2003 SP3 on XP Professional Looking for advice as to where to start looking for my problem. I have written a function to perform validity checking on selected portions of my worksheet data. The new function works great, except that as soon as I had tested it and added it to the conditional format conditions on the relevant columns some of my other custom toolbar macros began to misbehave. I wrote a custom function so that I could test mu...

Conditional Formating
Hi - I have a series of cells which have number in them. I add conditional formats so that if the value in the cell is above certain thresholds the cell is coloured red/amber/green (traffic light repoprts). The thing is when the report is printed the numbers still show. Anyone got any ideas as to how I can "blot" out the numbers so they don't show?? I have tried colouring the numbers white/transparrent, adding conditional formats the the font (or something similar)!! Cheers Andrew Have you applied the same conditional format colour to the text as the cell? -- HTH ...