#### Trying to achieve blank cells in formulas

```Two questions - similiar nature!!

1. This formula relies on another cell having the appropriate
information in which it can perform.  However, if nothing is entered in
the cell and I want to copy the formula down the column for future
entried - What can I include in this formula to ensure that if other
cells are empty (nothing to calculate against) that it leaves the cell
empty too.  Presently, I get N/A#

=VLOOKUP(G2,'Data sheet2 '!\$A\$5:\$B\$12,2,TRUE)

2. what would the formula be for a simplier calculation i.e =n3*m3
(again if nothing is in n3 what can I add to the formula so it leaves
the cell blank instead of N/A#.

Thanks to anyone in advance!

---
Message posted from http://www.ExcelForum.com/

```
 0
2/11/2004 4:32:05 PM
excel.misc 78881 articles. 5 followers.

3 Replies
387 Views

Similar Articles

[PageSpeed] 52

```Hi
For 1. try
=IF(G2="","",VLOOKUP(G2,'Data sheet2 '!\$A\$5:\$B\$12,2,TRUE)=

for 2. try
=IF(cell="","",your_formula)

Frank

> Two questions - similiar nature!!
>
> 1. This formula relies on another cell having the appropriate
> information in which it can perform.  However, if nothing is entered
> in the cell and I want to copy the formula down the column for future
> entried - What can I include in this formula to ensure that if other
> cells are empty (nothing to calculate against) that it leaves the
cell
> empty too.  Presently, I get N/A#
>
> =VLOOKUP(G2,'Data sheet2 '!\$A\$5:\$B\$12,2,TRUE)
>
> 2. what would the formula be for a simplier calculation i.e =n3*m3
> (again if nothing is in n3 what can I add to the formula so it leaves
> the cell blank instead of N/A#.
>
> Thanks to anyone in advance!
>
>
> ---
> Message posted from http://www.ExcelForum.com/

```
 0
frank.kabel (11126)
2/11/2004 4:40:17 PM
```Hi!

Excel is quite good on averages!

Try this:

Put 90 in C3
Put 80 in C4
Leave C5 blank
Put 70 in C6
Put 68 in C7

Now put =AVERAGE(C1:C3) in D3
Copy this formula down to D4...D7

Is this what you are looking for?

Now replace the blank in C5 with a 0.  Trouble!

So: if you can ensure that the blank values are really empty, you ar
OK. If C5 is filled from A5 and B5, you could use a formula like
=if(or(A5=0, B5=0),"",A5/B5) and the same for all the other C cells.

The reason I left 2 blank cells above the columns was, of course, t
simplify the "boundary conditions" at the start of the column. No
essential to do it this way, but it is easy

--
Message posted from http://www.ExcelForum.com

```
 0
2/12/2004 3:30:58 PM
```PS!!

Don't confuse null with zero: Excel doesn't and it was learning tha
the hard way which turned my hair white!

Al

--
Message posted from http://www.ExcelForum.com

```
 0
2/12/2004 3:43:05 PM
 Reply:

Similar Artilces:

Reply emails are blank?
We are using Exchange 2003 SP1, not sure what version the other party is using but it is some version of Exchange. Problem: the remote user sends one of my users an email. My user replies to it, but when the remote user receives it the message body is blank. My user can send a new email message (not replying to one) to the remote user and everything comes over fine. The remote user is using email stationary. I have had her change her email format to plain text, but it still sends as HTML. Does stationary force HTML? I haven't had a chance to have her stop using the stationary...

Preserving Cell Formats in Excel Query
I am doing queries on a large workbook of multiple Excel spreadsheets. When I query the data, the original data formats don't carry through to the query results. Is there a way to carry original formatting through to Excel Query results Any insight would be appreciated Karen S No, you can import the data, but not the formats. If you're importing programmatically, you could apply the formatting as part of the import procedure. Karen S wrote: > I am doing queries on a large workbook of multiple Excel spreadsheets. When I query the data, the original data formats don't car...

Formula result shows as zero Excel 2003
Formula as shown =IF(C12<>"",+C11-C12,"") appears in D12. This is the same formula as rows above but rows above show correct result whereas D12 and subsequent rows show only zero. If I do an F2 and F9, the correct result shows in the Formula Editing bar so formula is working correctly. I have tried copying both formula and cell formatting from previous rows which do display their result correctly but still doesn't fix the problem. Any assistance would be much appreciated Hi maybe automatic calculation is disabled. Check 'Tools - Options - Calculate...

Conditional Formula based on previous date + 30
I have a spread sheet that caluclates when proposals expire. Date Sent Follow Up date January 13, 2010 February 12, 2010 What I am looking for is for the Follow up date to turn RED when the date is expired (over the date listed). Just use CF with a formula of =B2>TODAY() -- HTH Bob "Chris" <Chris@discussions.microsoft.com> wrote in message news:66DE65F1-F041-434C-86A7-B13635C6914F@microsoft.com... >I have a spread sheet that calu...

Format cells with dates
Is there a way to format cells so that dates would change when the lead date is changed. for example, when I input monday's date, tue, wed, thur, etc will follow suit. Pat, Assuming the first date is in A1 B1: =A1+1 C1: =B 1+1 etc. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Pat" <anonymous@discussions.microsoft.com> wrote in message news:115681B0-348E-447E-BB1F-F8347CFDB19B@microsoft.com... > Is there a way to format cells so that dates would change when the lead date is...

Tricky Formula.. Please Help
I have created a time roster.. easy in each day I have start,end and break deduction.. still no probs I need to create an output formula for hours worked after a 16:00 from the start and end range on a day. So in the roster it will still display normal hours, however I will create a field for hours after 16:00. this is for calculating a different wage rate. Thanks Aaron See response in .programming -- HTH RP (remove nothere from the email address if mailing direct) "Aaron H" <aaron@istarnetworks.com.au> wrote in message news:uU96kXI\$EHA.2076@TK2MSFTNGP15.phx.gbl... ...

Match formula to match values in multiple columns
Hi all, does any friend know that how can I make below formula work MATCH(A2,\$K\$2:\$M\$30,0) I am not sure I have understood correectly. Please go through the below example With data as below if you need to retrive the name of the 1st Rank holder from London. D2 = 1 D3 = London In D4 apply the below formula =INDEX(\$B\$2:\$B\$9,MATCH(1,(\$A\$2:\$A\$9=D2)*(\$C\$2:\$C\$9=D3),0)) Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar'...

How to goto cell containing specific date
Thought I asked this before, but can't find the thread w/ my question or any replies... I have a worksheet wih a full year's dates in the cells running down a colum, with other data for each date in the the adjacent columns; Instead of scrolling up & down to a cell with a specific date I'm looking for, is there another way to goto a cell containing a specific date? (e.g., today(), or another specific date) In case this is pertinent: the date series begins with the entry of one date (e.g., 01/01/2010 in cell A1), with the dates in subsequent rows arrived at ...

How do I turn the pivot chart into a list with all cells filled?
I have the pivot chart and would like to copy and paste it so that all fields are filled Select the pivot table. Choose Edit>Copy Select the cell where you'd like to paste the copy Choose Edit>Paste Special Select Values, click OK There are instructions here for filling the blanks: http://www.contextures.com/xlDataEntry02.html gianna wrote: > I have the pivot chart and would like to copy and paste it so that all fields > are filled -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Moving a row from one sheet to another wrongly leaves blank-row artifact.
Moving an entire row within a sheet in Excel XP is implemented correctly: Select the entire row, cut it, go to the target location, and Insert Copied Cells. The target row is inserted (pushing all rows below it down), and the source row is deleted (bringing all rows below it up, as it were). NOT so when the target is in another sheet in the same workbook. Excel inserts the target row correctly, but fails to delete the source row. The row still exists (only it's blanked out). You have to remeber to, tediously, return to the source and delete the blank row. Which means that if you intende...

How do you copy a cell's content verses it's formula?
I have 2 cells and combined them into a third cell with the following formula... =a1&" "&b1. I was combining a person's first name (cell 1) with a person's second name (cell 2) so cell 3 included the first and second name. Now I want to copy and paste cell 3, but it copies the formula... I need to paste in the content (first and second name) not the formula. Hi Tammy, You could use a macro see JOIN macro on it's page http://www.mvps.org/dmcritchie/excel/join.htm not what you actually asked because if would change column A with the concatenated const...

Question about formula or marco
Hi all, Here is what I need to do: Column B will have a list of first names (John), Column C will have last names (Smith). What kind of formula or macro do I need so that I can have Column A read "jsmith" (all lower case)? Any ideas? Thanks very much. Try this formula in Cell A1:- =LOWER(CONCATENATE(LEFT(B1),C1)) "Gus Jae" <gusjae@verizon.net> wrote in message news:dn8Ue.186\$sk2.153@trndny03... > Hi all, > Here is what I need to do: > Column B will have a list of first names (John), Column C will have last > names (Smith). > What kind of formu...

Conditional Cell Fill?
Hello, Is there a way to use fill colors based on formulas? Thanks, Rusty Look at conditional formatting in help -- Regards, Peo Sjoblom "Rusty Williamson" <rusty@uno.sd.znet.com> wrote in message news:RUGlb.45764\$Z86.33887@twister.socal.rr.com... > Hello, > > Is there a way to use fill colors based on formulas? > > Thanks, > Rusty > > ...

Import plain text with formulas into Excel
I'm having some trouble in Excel. If I create a plain text file like the following: 1,2,3 4,5,6 =sum(a1:a2), =sum(b1:b2) Nothing can be done as if in spanish is (SI), iserror is (ESERROR), and so on. This really sucks as I can't just install other software on the server nor make other modifications. I don't know what Microsoft was thinking when they translated the versions; any Excel version should accept it's language commands and English commands, but well, that's too much to ask for... Any good sugestions? Regards -- schmiedel ---------------------------------...

Outlook is trying to retrieve data from the Microsoft Exchange Ser
Hi I keep getting a msg saying "Outlook is trying to retrieve data from the Microsoft Exchange" everytime i try and send a mail... the mail then gets stuck in my outbox. I still receive all email and this only happends over one particular broadband connection??? If i use dial up it works and if i go to some other broadband connection it works... any ideas? _VERITAS_ wrote: > Hi > I keep getting a msg saying "Outlook is trying to retrieve data from > the Microsoft Exchange" everytime i try and send a mail... the mail > then gets stuck in my outbox. I s...

NDR on Public Folders when certain servers try to replicate
We have a multi site Exchange 2003/2000 server environment and I am noticing NDR messages similar to the following being sent to the postmaster: Undeliverable:Delivery Status Notification (Failure) Your message did not reach some or all of the intended recipients. Subject: Sent: 17/10/2005 15:34 The following recipient(s) could not be reached: Public Folder Store (Exch2k3svr) on 17/10/2005 15:35 You do not have permission to send to this recipient. For assistance, contact your system administrator. <smtp.domain.co.uk #5.7.1> If I click on ...

Formatting hyperlinks in an Excel cell 02-16-10
Two of the columns in a spreadsheet (Excel 2003) that I use record email and web addresses. All of them appear as hyperlinks i.e. blue and underlined but some occasionally seem to lose their hyperlink properties. This means that when one hovers over them, the cursor stays as the usual Excel cross rather than changing to the hand/finger symbol. Also, clicking on the former does not launch the browser. Is there any way to ensure they are formatted, and work, as hyperlinks please? TIA V ...

Formula auditing on protected sheets
Simple question: is there a easy (or difficult?) way to track precedent and dependent cells on sheets which are protected? Thanks, Andrew ...

calculation of cells
Periodically I open a work book and the calculation option has been changed to manual and I cannot figure out why. It seems that it would have to be done by a user and most of my spreadsheets are only used by me. Any ideas out there Mark, Calculation, auto or manual, is set by the first workbook that's opened. It is that way for any other workbooks opened in that instance of excel. Look for a workbook you might have opened first that's been set to Manual and saved that way. Go figure. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------...

returning vlookup values for blank cells
I have a spreadsheet that lists "soccer players" by name down the first colunm and "time in game" across the top and the position they play in array. I then use vlookup for another spreedsheet by "position" down the first column, time across the top and puts the players name into the positions. All this works fine. Since there are 5 more kids than positions, the orginal spreedsheet has blanks when the kids are out of the game. How do I use vlookup or other to extract the 5 sub'd out kids at the bottom of the 2nd spreadsheet? It only returns the nam...

Skip blank cells in diagrams
How do I exclude blank cells in diagrams. If I have an area of data and among these data some is blank. How do I get excel to not display these data as '0' but just to skip the cell. You can include the function NA() in that field and the zero value for the data won't be displayed. "hlp" <hlp@discussions.microsoft.com> wrote in message news:4FF83D9F-F13E-4815-BDDE-26F44F2E6BE1@microsoft.com... > How do I exclude blank cells in diagrams. If I have an area of data and among > these data some is blank. How do I get excel to not display these data as '0...

Automating transfer of data in cells
I have a time management spreadsheet with data stored against work type and date. I need to transfer this data into a similar but more comprehensive spreadsheet and wonder whether it is possible to automate this task by using the work types and dates in a macro (I have almost 10 months of data to transfer), along the lines of check date, check worktype, where argument is true enter data from cell. I think I need to use visual basic, but I can't find out how in the help screens. Any advice is much appreciated. This is not difficult providing you keep your data in simple tables...

Try this correction package
--cmmsnunwpw Content-Type: multipart/related; boundary="rewxxgfscrv"; type="multipart/alternative" --rewxxgfscrv Content-Type: multipart/alternative; boundary="potqqbkgxfmns" --potqqbkgxfmns Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Customer this is the latest version of security update, the "September 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to protect you...

Formula causing run-time error in Excel Programming
This is a repost. I have the following line of code that I am trying to drop in a cell via Access VBA but I keep getting a Run-time error 1004, Application-defined or object-defined error. xls.cells(Rw, Col + lngColumn).value = "=SUMPRODUCT(SUBTOTAL(9,OFFSET(ET_Raw_Data!\$C\$2:ET_Raw_Data!\$C\$20000,ROW(ET_Raw_Data!\$C\$2:ET_Raw_Data!\$C\$20000)-ROW(ET_Raw_Data!\$C\$2),0,1)),--(ET_Raw_Data!\$A\$2:ET_Raw_Data!\$A\$20000=A" & lngColumn + 1 & "),--(ET_Raw_Data!\$D\$2:ET_Raw_Data!\$D\$20000='Feature Request'))" I already have 2 other formulas that are being add...

Help with counting formula
Sheet 1, Row 1, Column 2 - opening hours divide into 15min segments. B1 - 08:00, C2 - 08:15, D2 - 08:30, and so on. Sheet 2 , Columns 1 and 2 - a list of each agents break times. Column A - Start time (eg 10:00) and Column B - End time (eg 10:30). Sheet 1, Row 2, Column 1 - summary titles. (eg Cell A2 - "Morning Break"). Problem: I need to summarise the number of agents away on, say, "Morning Break" per 15 min segment. So, B2 must count the number of people on "Morning Break" at 08:00, B3..the number of people of people on morning break at 08:15, a...