blank cell instead of x

I need a formula to have a blank cell instead of a "x"  eg:
                                                                     result 
should be:
     aa  ab  ac   ad   ae    af  ag  ah    ai    aj      ak  al  am  an  ao
2    6    3    6    2     5    17  20  15   205  18      6    3    6   2x   5
3    1    5    7                19  12  133                   1    5   7x

the formula i used is:  =IF(AF2>=42,AA2&""&"x",IF(AF2="","",AA2))
on row 3 there are blank cells (null string contains formulas ) when 
I apply the formula to those cells it returns a "x" instead of a blank cell

can someone help please

bill gras
0
Utf
1/4/2010 12:02:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
843 Views

Similar Articles

[PageSpeed] 29

Don't forget that any string, including your null string, evaluates to 
greater than any number, so "">=42 is TRUE.
Perhaps you want your conditions in the opposite order?

Try changing
=IF(AF2>=42,AA2&""&"x",IF(AF2="","",AA2))
to
=IF(AF2="","",IF(AF2>=42,AA2&""&"x",AA2))
--
David Biddulph


"bill gras" <billgras@discussions.microsoft.com> wrote in message 
news:0B1527A6-47AE-4A31-BD66-77A8F34AD2F7@microsoft.com...
>I need a formula to have a blank cell instead of a "x"  eg:
>                                                                     result
> should be:
>     aa  ab  ac   ad   ae    af  ag  ah    ai    aj      ak  al  am  an  ao
> 2    6    3    6    2     5    17  20  15   205  18      6    3    6   2x 
> 5
> 3    1    5    7                19  12  133                   1    5   7x
>
> the formula i used is:  =IF(AF2>=42,AA2&""&"x",IF(AF2="","",AA2))
> on row 3 there are blank cells (null string contains formulas ) when
> I apply the formula to those cells it returns a "x" instead of a blank 
> cell
>
> can someone help please
>
> bill gras 


0
David
1/4/2010 12:42:34 PM
Hi David Biddulph

Your suggestion works perfect . Thank You
Have a prosperous new year
-- 
bill gras


"David Biddulph" wrote:

> Don't forget that any string, including your null string, evaluates to 
> greater than any number, so "">=42 is TRUE.
> Perhaps you want your conditions in the opposite order?
> 
> Try changing
> =IF(AF2>=42,AA2&""&"x",IF(AF2="","",AA2))
> to
> =IF(AF2="","",IF(AF2>=42,AA2&""&"x",AA2))
> --
> David Biddulph
> 
> 
> "bill gras" <billgras@discussions.microsoft.com> wrote in message 
> news:0B1527A6-47AE-4A31-BD66-77A8F34AD2F7@microsoft.com...
> >I need a formula to have a blank cell instead of a "x"  eg:
> >                                                                     result
> > should be:
> >     aa  ab  ac   ad   ae    af  ag  ah    ai    aj      ak  al  am  an  ao
> > 2    6    3    6    2     5    17  20  15   205  18      6    3    6   2x 
> > 5
> > 3    1    5    7                19  12  133                   1    5   7x
> >
> > the formula i used is:  =IF(AF2>=42,AA2&""&"x",IF(AF2="","",AA2))
> > on row 3 there are blank cells (null string contains formulas ) when
> > I apply the formula to those cells it returns a "x" instead of a blank 
> > cell
> >
> > can someone help please
> >
> > bill gras 
> 
> 
> .
> 
0
Utf
1/5/2010 9:46:01 AM
Reply:

Similar Artilces:

Blank page at the end of report (no margins problem)
In an Access report I've created, the last page is always blank (so not every other page, only the last one). This is very strange considering the fact that I've set both page headers and footers and they don't appear on this page. Now I've checked all margins, both "can shrink" and "can expand" options and I really can't seem to find where this problem is coming from. Another strange fact is that in preview mode (and in report preview mode) I don't see this blank page, it's just there when I print the report. Can anyone tell me what I'm m...

protecting only a few cells????
Hello, Excel wants me to define which cells I do NOT want to block for input. I want to select a few cells and tell Excel to lock only these, since entered data that I want to protect. How to -- fren ----------------------------------------------------------------------- frens's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1505 View this thread: http://www.excelforum.com/showthread.php?threadid=26840 "frens" <frens.1e06ta@excelforum-nospam.com> wrote in message news:frens.1e06ta@excelforum-nospam.com... > > Hello, > > Excel wan...

Validation Criteria & Ignore Blank (cells at bottom)
Hi All, I have a named list that has a bunch of blank cells at the bottom so the user can grow the list as needed. The ignore blank check box in data validation looks tempting, but I still get all the blank cells at the bottom of my drop down list. Does anyone know how to get it so the blank cells don't show in the list. Worksheet called License_Only has data from C2:C210. The data is pulled from a second file as follows: C2 - =IF(ISBLANK([Ontario_MGS_Price_List.xls]License_Only!C2),"", [Ontario_MGS_Price_List.xls]License_Only!C2) C3 - =IF(ISBLANK([Ontario_MGS_Price_List.xl...

Return blank cell using a formula
Here is my formula: =if(iserr(h3/g3),"",h3/g3) The problem is I am using this data in a line chart and whenever there is a "", my line drops all the way down to zero. Can I make the formula completely blank the cell rather than filling it with a space? Try =if(iserr(h3/g3),NA(),h3/g3) -- Regards, Peo Sjoblom "asach" <asach@discussions.microsoft.com> wrote in message news:15E33E9F-6AE0-4028-B2C5-27BDD580701D@microsoft.com... > Here is my formula: =if(iserr(h3/g3),"",h3/g3) > > The problem is I am using this data in a line c...

Repeat value from cell above
Setting up a data entry screen with 9 columns. The first 3 columns (Entry date, salesman, customer) might repeat several times: How do I repeat and display the value from the cell above if there is no value entered in the current cell? Thank you! Carl Try this: Select from the first category down through the last relevant blank cell Press the [F5] key......a shortcut for <edit><goto> Click the [special cells] button Check: Blanks Click the [OK] button (that will select all of the blank cells in that range) WHILE THOSE CELLS ARE STILL SELECTED... Type: =...to start building a...

Macro that looks at cell contents
I have a column filled with ID numbers. Some begin with E. example E12353. Other ID numbers are 5 digits long. example 12345. Every day I copy and paste these numbers from a program and into my excel spreadsheet. Along with these numbers is important data that I also copy and paste into the spreadsheet. On the other side of the company they use the same ID's but add "00" to the end of all 5 digit ID numbers. It is not important to either side that these numbers dont match so they dont care. I have to deal with combining these ID's to get good information multiple times...

pictures in table cells
I am using publisher 2003. I created a table in publisher that I want to add pictures to. I need to be able to insert more rows into the table and I want the pictures to move down with the rows. Is there any way to link a picture with a cell or table row? With your cursor in the cell, fill, fill effects, picture tab... you may have to check the aspect. If you add rows the picture will stay in the cell where you inserted it. -- Mary Sauer http://msauer.mvps.org/ "Dan" <Dan@discussions.microsoft.com> wrote in message news:5A099BA8-9FFD-46BC-ACE7-0732679F4C54@microsoft...

Word wrapping paragraphs within cells
Hi everyone, I support a user who stores narrative data within a spreadsheet. They want to know why Excel 2000 SR-1 inconsistently wraps text within a cell after the narrative exceeds 8 lines. This occurs regardless of cell height. They also want to know how to fix it so the text wraps consistently regardless of narrative length. I know the obvious answer (store the data somewhere else) - but does anyone have any more politically acceptable ideas? Thanks in advance. I don't store that much text in cells, but you may want to have them try putting some alt-enters in their text. Exce...

Grouping random cells..............................can it be done????? TIA
I have an Excel sheet with data. I want to select cells at random and group them together so at a later time I can select one of the group members and it will select all the members in the group. I tried to do it and Excel complains back that this can not be done with non adjacent cells. Hope someone can help. Thank you. Boll Weevil, what you can do is select the cells and define a name for them (Insert > Name > Define). Then at any time you can press F5 (Go to), click the name, click OK, and Excel will select them all. -- DDM "DDM's Microsoft Office Tips and Tricks"...

Printing of cells in Excel with conditional equations?
How do I suppress printing of cells in Excel that have a conditional equation in the cell and appear as blanks in the worksheet, but when I print I get a blank label. I am setting this up for a user and I don't want them to have to highlight the cells and then use SELECTION in the print parameters? Can you pick out a column that always has data when the row should be printed--and is always blank when the row should not be printed? If yes, then I'd apply Data|Filter|Autofilter to that range/column. Filter to show just the non-blanks and then print, then data|filter|showall to get...

Framework X library loading error
I have reinstalled Office X on a new computer - a G5 running 10.3.2. When I initially reinstalled it, I just copied the application over to the new computer, then applied all the patches / updates. I get an error every time I start up, "An unexpected error occurred while trying to load the Microsoft Framework X library". The program still works then (or appears to). I have tried reinstalling from the CDs, but still get the same problem. Is there some file somewhere that I have to change the permissions on? Does anyone have a solution to this? Thanks in advance, Simon Simon, I'...

Show blank cell if less than 1
If a cell's number is less than 1 (negative number), how can I make it show a blank cell? TIA ....Will 0.9 is less then 1. Do you really want a negative to activate the blank cell result ? What the cell contains will determine which is the best solution. If the cell contains a formula : =A1*B1, you could try: =IF(A1*B1<0,"",A1*B1) If the cell will contain only user entry, you could try either DataValidation (Do not allow negatives), or Conditional Formatting, where any negative entry will change the cell color to white, giving it the appearence of "blank". -- ...

Align left and Align right in the same cell
Hello! In a new workbook, take cell A1 and widen it to 7" long. Now, is there a way I can align "To:Mom" to the left and the date to the right in the same row and column? So for A1, it would look like"To:Mom Jan 11, 2009"?? I only want to use one column.And without VBA. Maybe, if you can't do a flush left and right in the same line, is there a code for tab similar to CHAR (10)? I was thinking something like ="To:Mom"&CHAR(tab)&CHAR(tab)&NOW ?? Thanks! VR/ Lost Cell enter A1 Mom B1 the date (if today use <Ctrl>...

OnDraw only draws a blank page...
Hi, I have an application that runs fine on 99% of our users, but on some the OnDraw(...) function does not draw anything. It is a blank page. I am sure that it would throw an assertion if it was a debug version but it does nothing apart from drawing a blank page. What could be the reason for that to happen. I am assuming that the CDC is invalid, could that be the reason? Many thanks in advance. Simon Simon, I would find an invalid CDC unlikely, perhaps something like a font that doesn't exist on the user system? Or a too large bitmap, this is of course very difficult to say with...

return blank instead of 0
In my spreadsheet, I am trying to get data from another Excel file. eg. ='[2004 Distribution Survey Data_Final.xls]2. Expenses- Cost Center'!$B$87 When the input file contains a blank cell, I am getting a "0" intead of a blank. I would like to get a blank if the cell has no data. Is there an easy way to do this? I don't want to change all my formulas to =If(isblank(...),"", ...) There are too many formulas to change. Thanks a lot for any help! Wei Hi you could try tools / options / view and untick zero values Cheers JulieD "Wei" <anon...

How to diffrentiate Text & number from a single cell????
:( Hi ther guyz............i a have small problem with the execl sheets........... my problem is e.g. i have alot of cells with a number and some text in it........and they both are in one/single cell (e.g. "878 queen st" in A1)....now what i want is to remove let's say "queen st" from each cell so that there is only number left in it.....................i cannot figure out any formula........if there is any..............need help imediately -- pack_card2000 ------------------------------------------------------------------------ pack_card2000's Profile: http://...

How to keep the cell borders of the pivot table?
Is there any way to not lose border formatting after refreshing a Pivot Table? Thank you. On Aug 8, 2:26=A0am, "=C1=F7=C0=CB=B5=C4=CB=AB=D3=E3" <rover...@hotmail.com= > wrote: > Is there any way to not lose border formatting after refreshing a Pivot > Table? > Thank you. Make sure you have selected "Preserve Formatting" in the table options Yes. I am sure I have selected "Preserve Formatting" in the table options "David Heaton" <dheaton@stanleyworks.com.au> ??????:3c91c9ce-2759-4d28-a7c9-053433508082@u20g2000prg.googlegroups...

Cell Selection Bug!!
Not sure if anyone has seen this before: if a selection in BookA is copied to BookB whatever was originally selected in B stays selected aswell as what is copied in e.g say you select A16 in BookB then go into BookA and copy A1:C1, go back to BookB and paste into A1:C1 - the bug is that A16 is still selected (and stays selected whatever other cells you select) Is this an Excel;2003 bug?? Any help greatly appreciated Jason. Not a bug. You can open several books at the same time. You can Select a different cell in each book. By Activating each book in series, Selection will become the s...

Delete blank page at end of document
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I have a document that is only one page long, but there is a second, empty page, that I cannot delete. In order to make two columns in the second part of this document/page, I did make a section break. When I try to delete the blank page, the formatting reverts to one column. <br> I hope there is a solution that will allow me to keep the two-column formatting and still delete the blank page. In order to see what you're dealing with make sure you have the non-printing characters displayed in the document (click the � bu...

Cell Reference locking
I am trying to lock a cell reference from one sheet to another. i.e. Cell A1 on "sheet 1" needs to ALWAYS be referenced by cell D5 on "sheet 2." I have tried using $ signs and using the sheet protection to no avail. What happens is when ever a user moves the information via drag and drop or copy and paste (other than copying and pasting up in the tool bar) the cell reference moves with it. i.e. User highlights the cell B1 on "sheet 1" and drags it to cell A1. On my machine what happens is that Cell D5 is now referencing B1 and the cell that was refe...

Extra blank pages when printing
I have a workbook that I use daily for creating and recording works orders and printing the associated documents and labels. Whenever I print from a particular sheet, I get an extra blank page. I've made sure there's nothing in any of the surrounding cells and set the right and bottom margins to zero. The printed image is well within the printable area, but I still get the extra unwanted page so I'm stumped as to what's causing the extra page to be generated. Any ideas? -- Jonathan Finney Select the entire area of the sheet that you wish to print, then: <File&...

Outlook 2003 sends e-mail with blank message
I have recently upgraded to Outlook 2003 and since that time whenever I try to send an E-mail outlook sends the header and subject etc but then the message body is blank. this happens whethr I am editing in word or not and seems to happen at source IE when viewing the outbox the message body has already disapeared - any advice gretly appreciated. Thanks There's a guy in my office having the same problem. It seems to only happen randomly, however. He has Outlook 2K3 and we are running Exchange server 5.5. If you figure out the solution, please email me--remove the ZZZ'...

How to distribute characters in a cell?
How can I distribute in a cell text characters with even spaces? Select the cells and run this macro: Sub SpaceUm() b = " " For Each r In Selection v = r.Value l = Len(v) t = "" For i = 1 To l t = t & Mid(v, i, 1) & b Next r.Value = t Next End Sub Data like: abc12333 will become: a b c 1 2 3 -- Gary''s Student - gsnu2007g "churin" wrote: > How can I distribute in a cell text characters with even spaces? > Thank you for your response. I have never used macro so that this is a good opportunity for m...

How to one cells variables as a serie to a column
I cant find the way out. I have a daily variable data in a single cell (say in A1) ant I want to form a serie with using daily values of that cell. (Variable) Date Value 21.12.2006 400 (Series) 01.12.2006 300 02.12.2006 350 .... .... 20.12.2006 420 21.12.2006 400 "noyau" wrote > That is first step. Thnx a lot. You're welcome. Glad it helped. > Now the point is to do the same thing for a > daily variable next to the date. > The problem is that the second variable is > not increasing or decreasing. > Still working but no result. ...

Adding every third cell in a column
I need a formula for a cell that will add the value in every cell in a specified column that has the name "Plan" in the cell in the previous column. On Thu, 21 Apr 2005 14:23:02 -0700, "Jack Thiel" <JackThiel@discussions.microsoft.com> wrote: >I need a formula for a cell that will add the value in every cell in a >specified column that has the name "Plan" in the cell in the previous column. Check HELP for SUMIF. If "Plan" may or may not be in Column A; and your values to be added are in Column C, then =SUMIF(A1:A1000,"Plan",...