making cells blank that arent ready to be calculated

hi,

i have some spreadhseets where the formulas are calculating data that
isnt ready yet, i.e. the formulas are copied down in advance but are
for monthly balances in the future, so some of them say "#DIV/O!" and
others say "-100%", is there any way to have excel just make these
cells be blank until they are actually being calculated but retain the
formulas??

i hope i made my question clear...

thanks


-- 
RalphSE
------------------------------------------------------------------------
RalphSE's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29931
View this thread: http://www.excelforum.com/showthread.php?threadid=507350

0
2/1/2006 6:11:00 PM
excel 39879 articles. 2 followers. Follow

3 Replies
398 Views

Similar Articles

[PageSpeed] 33

Hi Ralph,

If your formula is something like =A1/B1, you could change this to:

=IF(OR(B1=0,A1=0),"",A1/B1)

This will give you a blank if either A1 or B1 (or both) are empty or 0.

Hope this helps.

Pete

0
pashurst (2576)
2/1/2006 6:52:40 PM
Thanks Pete!  :) ;)


-- 
RalphSE
------------------------------------------------------------------------
RalphSE's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29931
View this thread: http://www.excelforum.com/showthread.php?threadid=507350

0
2/1/2006 7:14:05 PM
You're welcome.

Pete

0
pashurst (2576)
2/1/2006 7:48:47 PM
Reply:

Similar Artilces:

How to copy only to cells where there is data
Hello: I have created a simple macro that, among other things, places a formula in I2 that subtracts H2 from G2. The macro, then, copies this formula down the entire column of I and formats column I to be "values" instead of "formulas". That's all well and good. But I only want the macro to conduct this sort of copying and formatting of column I only where there is data in column H. I mean, if the data in the spreadsheet does not go past row 131, then I do not want any data in column I that is beyond I131. Could someone please review my macro below...

Delete row if cell starts with ...........
I have a couple of values in Column 1 where there are values that start with PJR. I would like to delete the whole row if a cell contains a value that starts with PJR. I would be grateful if you could provide me a Macro that would do just this. Many Thanks. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-new/200605/1 You have some examples with code in this page: http://www.rondebruin.nl/delete.htm "mohd21uk via OfficeKB.com" wrote: > I have a couple of values in Column 1 where there are values that start with > PJR. I would like to delete...

Calculated controls in reports in Projects
Why can I not use calculated controls in Access 2007 reports - the expression builder is not available ...

Blank Mail Body
We are using Outlook 2003 with SBS 2003 and are having trouble with sending messages. The message on a reply is blank when format is set to html or rtf. We also can not set format to rtf at all as all messages sent will be blank. Anyone have resolution to this issue? All machines are current on updates to windows 2000 and outlook 2003. Thanks, Randy- ...

Counting Non-Contiguous Cells
Hi, this is probably very basic and I am being very stupid but if I wanted to count the values of Cells D1 F1 and G1 I which have the background colour of yellow I would use the following CoiuntYellow(D1)+CountYellow(F1)+CountYellow (G1) Is there an yeasier way and if so what is the correct way to enter the formula. I ahve tried CountYellow(D1,F1,G1) and it throws an error Any help appreciated Thanks Sam hi Sam, what does "CountYellow" is it a new function of xl2012 ? -- isabelle Le 2012-02-16 18:11, Sam Hamman a �crit : > Hi, this is probably very basic and I am ...

make labels (increasing number) in Excel
I want to make labels in the following format using Excel. What I did was type the first row number (e.g. Full 0051) and then drag the mouse so that the number will be increasing. But I have to do it in every column, that is tedious work. Anyone knows faster way to do that? Also, how can I change the size of each cell consistency? Full 0001 Full 0051 ... Full 1951 Full 0002 Full 0052 .... ... Fulll 0050 Full 0100... Full 2000 Thanks! You can do the entire table with two drags! Just enter the first two values in the first two columns Full 0001 Full 0051 Full 00...

Load ws cell data into a textbox via click on chart datapoint
Not sure if this is a chart question or programming question. If I have data in a worksheet like this: A B C 1 Date Number Text 2 2 Dec 2000 234 Sunny Day 3 1 Dec 2000 117 Wrecked My Car and I create a line chart, I'd like to be able to hover or click the mouse on the data point in the chart and have the text from column C load into a textbox (the text may be many paragraphs long). Are there any ways to do this, either with VBA, C# or other techniques? Thanks, Paul Tushar Mehta has a utilit...

Summation cell formatting
I have a four column worksheet - col A contains text for descriptions, cols b thru d contain comma formatted numbers - 2 decimal places, the worksheet has about 30 rows of data. When summing col b thru d the total for each column should be zero, the row totals can be various amounts. The problem is col b shows 0.00, but cols c and d show - (dashes) representing zero. This has happened randomly to several worksheets over the years. I've tried reformatting columns, formatting tool, but can't seem to get rid of the 0.00. Any help would be appreciated. Thanks, -- James W. Doe...

Same formula Entry , Series Of Cells!!!!!!!
Hello Pros how do i enter one formula in a series of Cells like =Portfolios!H12 in A1 when i Copy A1 & Paste it in A2 it turns to =Portfolios!H13 but i want it to be the same =Portfolios!H12 in A1 to A80 Sam Information -- Message posted from http://www.ExcelForum.com Use =Portfolios!$H$13 instead -- Kind Regards, Niek Otten Microsoft MVP - Excel "Pivotrend >" <<Pivotrend.zim2n@excelforum-nospam.com> wrote in message news:Pivotrend.zim2n@excelforum-nospam.com... > Hello Pros > > how do i enter one formula in a series of Cells > like =Portfoli...

Making page display horizontal?
How do you format only a single page in a document to display horizontal rather than vertical? I want the other pages to remain vertical. Using Word 2008. Thanks! Hi Rick; What you want to do is change the Page Orientation form Portrait to Landscape for that one page. In order to do that you need to create Section Breaks to isolate that page, then change the orientation for that section. See Word Help on the topic: Change page orientation HTH |:>) Bob Jones [MVP] Office:Mac On 11/18/09 4:29 PM, in article #LZ2tYJaKHA.1640@TK2MSFTNGP06.phx.gbl, "Rick"...

calculate percentile for indexed numbers. Thanks
Hi, I have a large series of numbers saved in one column [34,5,21,98,34,3,432,55,27,90,1125,......]. These data are indexed, e.g. by [1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,......] which are in another column. I need to calculate percentiles (25%,50% and 75%) of these numbers for different indexes. In another word, I need to calculate percentiles for those numbers with index of 1, 2, ... respectively, not the percentiles of all the numbers. Thanks a lot! Assuming: Index Percentage to use 1 25% 2 50% 3 75% and number is in column A and index is in ...

Delete hidden spaces in cells
Hello, I've got Excel 2002 SP3. I've got a big spreadsheet of names and addresses that I use as a data source for a mail merge in Word. If I select a cell in the city column in Excel and put the cursor up at the top where you can type or edit the cell contents, the cursor is not immediately to the right of the last character, rather it is a few spaces over to the right as if after the last character in the city name someone hit the spacebar a few times. For example: Instead of seeing Fesno| I see Fresno | This causes a problem when I do the mail merge because the label...

Copy one cell to another
How can i get a cell to input the same information i type in one including text, for example: cell 1 F3: John Doe 23023 and i want the same information that i display to show up on another cell f20: John doe 23023 Help please thank you In F20 write this simple formula: =F3. "gus" wrote: > How can i get a cell to input the same information i type in one including > text, for example: > cell 1 > F3: John Doe 23023 > > and i want the same information that i display to show up on another cell > > f20: John doe 23023 > > Help please tha...

Exchange connector makes mail disappear
Hi, I have enabled the POP3 connector on an SBS Exchange Server 2003 The connector seemed to work properly, but one user needed a return address that didn't match the domain.I added a second smtp email address for him and set it as the primary address. His outgoing emails now carry the correct "from" address, but the exchange connector no longer delivers his email properly. It worked for several hours but now his email is removed from the ISP's POP server every 15 minutes, but never appears in his Exchange mailbox. I have already checked for enabled filters, wrong delivery...

Wordwrapping within a cell
I have a cell's formatting set to word wrap, which it does, up to a point. After a couple of paragraphs of text, the word wrapping function seems to stop and the sentence suddenly trails off into the abyss. I've attempted to adjust the cell's size and several formatting options but nothing seems to work. I've reentered the text data into blank cells, new worksheets, and even re- typed the entirety of the text. I thought perhaps the 1,024 character limit was a cause, but if I adust the cell's size by width, it allows more characters to show. It will not however...

assigning "missing value" to cells
I have missing values in my data, which appear as blank cells. When I apply formulae across these cells, it automatically assigns a 0 value to these cells and includes them in the formula calculations. I want these cells to be disregarded when I apply a formula to large sections of data and to remain blank after the formula is dragged acros them. I can't get this to work at the moment and have to go through my data manually checking which values were blank, and then deleting the appropriate cells. Is there a formula / symbol that tells excel that a blank cell is missing data a...

clicker that advances one number per click in a cell
Hi, I'm new to the newsgroup. I hope that someone can help me with a small challenge. After reading the through the help sections, I just can't figure it out. How can I add a "clicker", where every time that I click on a button on a sheet, a cell advances 1 number? My gut feeling is that this is not a simple usage of a formula, but, then maybe there is one? Please help. Thanks for your help. Rayco See reply in .Misc Biff "Rayco" <raycotf@hotmail.com> wrote in message news:bc16d$42f59851$d85678fd$6680@PRIMUS.CA... > Hi, > > I'm new to...

how to display values in 3 cells into one cell
Anyone can help me how do I display the 3 separate different values in 3 cells into just one single cell ? thanks so much in advance. Either there was a typo or there's an echo in here. That line should have been: = A1 & " " & B1 & " " & C1 "Bradley Dawson" <bradleydawson@earthlink.net> wrote in message news:l464b.2224$Lk5.2093@newsread3.news.pas.earthlink.net... > In the target cell, type: > > = A1 & B1 & C1 > > where A1, B1, C1 are the cell addresses that you want to concatenate. > > If these have number...

Macro to select only cells within the range that contain data
Thanks to this board, I now can sort my text+numerical values the way I would like, with Excel Ignoring all characters but the numbers. Now, this data is in a column that contains 43 entries. In the future there may be more entries, but my current macro will only select the 43 entries (I made the macro using the macro builder) and selected only the cells with data. If I select the additional cells, the sort puts the blanks at the top of the form and this is not acceptable. (We have no one else at work that will be able to change the macro, if I'm not there and our list increases) I ne...

how can i add cells that are not in a range?
need help with formula. i want the total number of cells that contain "yes" from the following cells,G9,G10,G12,G13,G14,G18,G24 .. appreciate the help.. Try =COUNTIF(G9:G24,"Yes") Regards, Alan. "ladynole" <ladynole@discussions.microsoft.com> wrote in message news:9E4640C4-5508-4D44-BA19-850EE823F9DE@microsoft.com... > need help with formula. i want the total number of cells that contain > "yes" > from the following cells,G9,G10,G12,G13,G14,G18,G24 .. appreciate the > help.. =COUNTIF(G9:G10,"Yes")+COUNTIF(G12:G14,&q...

How can I Copy cell names from one workbook to another?
I have a clinet bundle (one workbook with about 20 sheets), I link it to another workbook that has current client portfolio info. I've just added some cell names to my template and want to add them to each clients info as I work with them. I don't want to copy the whole sheet or I'll loose all the client specific data. Is there an easy way I can just copy the cell names from one workbook to another without fussing with the macro? Maybe you could take the opposite approach. Paste the formulas from the portfolio worksheet into that template worksheet. Then copy that whole...

how do you delete blank rows in a spreadsheet using an "if" funct
what syntax is correct in the false part of an "if" statement to delete a blank row...eg =if( a1= "Tom", B1,{ what goes here to delete the row so as blank rows are not returned} Formulas can't be used to hide rows. You can use a filter to hide rows based on a condition select your data Data=>filter=>Autofilter select the column that has the values you want to filter on. Select the dropdown and set the condition. so you would filter on Tom, so only rows containing tom are visible. Repeat the sequence to in the menu to remove the filter. -- Regards, Tom...

Is there a way to lock out the format for the cell and still allow data entry and C&P?
I've set the spread sheet and have locked all the cells but those that I want to have filled with data. There are time when I would like to be able to C&P the data to other sheets but when I do I also copy the format. I know I can use "paste special" but I want it so anyone can use the C&P without having to use "paste special". Marc Hi, You could write a macro that uses a string for the cell values. sub cpme() dim strCP as string strcp=range("A1").value strcp.copy range("A2").paste end sub I am a bit rusty but this sub should give y...

How to make X, Y axes of Excel charts to have same scale on the screen?
Hi: I have a set of X and Y data that will make a square. But when I plot them by XY chart in Excel (2003), it looks like a rectangle, even if I have made the maximum, minimum, major unit, minor unit exactly the same for both X, and Y axes, and I can see the X and Y data maximum almost the same, but the graphy on the screen still look like a rectangle rather than square on the screen. How I can fix this? (by the way, if in Matlab, I can use 'axis equal'). p.s., this is not an Excel programming question, but since I can't find an Excel usage group so I sent the questio...

If cell in a Col of doc1is = to cell in a Col in doc2, copy cell .
If cell A1 in first document is equal to cell A1 in 2nd document, I want to copy amount next to cell A1 of 2nd document onto cell B1 of 1st document. I tried vlookup. It worked only on the first cell, but when I copied the formula, it didn't work. For ex: 1st document 2nd document Col A Col B Col A Col B 610001 610001 2,665 610021 610023 3,000 610023 610024 ...