Empty Cells in Excel with =IF formula (8 posts)

Forums » Software and Services » Office XP »

Empty Cells in Excel with =IF formula (8 posts)

Andrew Burgess Posted 7 years, 9 months ago Joined: 12 years, 5 months ago

When I'm manipulating or testing data using the =IF formula in Excel, I would often like the formula to return an empty cell if the response to the query is false (or in some cases true.)

However, the only option available appears to be the two quotation marks (""). This gives the appearance of returning an empty cell, but it is in fact entering some sort of ASCII code which causes the program to regard the cell as non-empty.

And if one leaves the 3rd criterion of the =IF formula blank, then a negative response returns a message of FALSE. Now whilst this may seem trivial, it does in fact cause me much grief because when I write further formulae which reference these cells, then I get an #NA! or #VALUE! error message instead of just another empty cell.

So does anyone know how to write an =IF formula that will return a completely empty cell?

Chris Burrage Posted 7 years, 9 months ago Joined: 12 years, 5 months ago

Re: Empty Cells in Excel with =IF formula

Don't think it can be done. Surely 0(zero) could be adapted to do the job?

Chris B

Greg Willcocks Posted 7 years, 8 months ago Joined: 12 years, 5 months ago

Re: Empty Cells in Excel with =IF formula

The if statement is similar to one I use to visually present a blank cell.

If you make a list of numbers in b7:b11 and some empty and some numbers in the range c7:c11 try the following .....

In cell c:13
=COUNTIF(C7:C11,"") checks to see how many of the cells in the range C7:c11 have such an entry.

In cell d7
=IF(B7C7=0,"",B7C7) checks to see if any of the two cells b7 or c7 are blank, if so they produce a empty cell with a "" result. Fill down d7:d11 answers will appear IF b7 and c7 contain numbers, "" is entered in the cells where either b7 or c7 is empty with a "" result.

In cell e7
=IF(C7="","Empty","Full") and fill down to e11. This shows which ones are empty and which ones are full (containan an entry)

If you need more info send an example to this responce and we can see what it is you are trying to do with the "" entries.

Greg

Jonathan Ramsay Posted 7 years, 8 months ago Joined: 12 years, 5 months ago

Re: Empty Cells in Excel with =IF formula

Andrew, did you ever work that one out?

I am also very interested in this. I am specifically interested in how to get graphs to show nothing until a series starts. This would be where there is an underlying formula giving a value if one exists or blank if not (instead of the graph picking up "" as 0.)

Thanks in advance anyone.

Anonymous Posted 7 years, 7 months ago Joined: 12 years, 5 months ago

Re: Empty Cells in Excel with =IF formula

To illustraite this point a little better, I think he wants to do something similar to what I wish to do (but can't)

I am making a template for users to put data into with various formulas in some of the columns. However, the ammount of this data is unknown, it can vary from 5 lines to 500. Obviously I want to protect the formulas from my not very tech savvy users, so I need to put the formula in the first 500 odd row. Otherwise the users will plug in their data and no result will come out.

However, the users also want to print out this template once it has their data in it.

So I tried to use an =IF statement to set the value of the cell to "" if there was no data in its row. This works fine and no value appears in the cell.

However, here is why I want a genuinely empty cell.

I only want to print out those lines which have data in them, if the user puts in 5 lines of data they dont want it to spit out 15 blank pages because excel thinks it needs to print those lines.

So if there is anyway of making the IF statement set a cell to a value of truly nothing, then it won't print out all those extra pages.

Thanks for any help you might be able to give me or Andrew Burgess in answering this question.

JamesHV Posted 1 year ago Joined: N/A

People in the South belived that slavery was morally right and people criticized them for it, saying things like "you can't be a good Christian and believe slavery is moral." but the South believed Slavery was moral according to this.
"Servants, be obedient to them that are your masters according to the flesh, with fear and trembling, in singleness of your heart, as unto Christ." KJV
Because if you ask me ( I know I am about to get criticized to death) this to me sounds like slavery is moral.http://www.pokeronline7.com/ - Poker Online

IdotlydesVell Posted 8 months, 1 week ago Joined: N/A

I want you to bring in a good word about what I saw on this site is very well structured and information is very quality, and have gathered enough information better, I am
referring especially to the mythology, where we found almost everything
I searched And those quotes are very nice, I like cee you did here, apreciz such a work, you did a very nice job. Only
well keep it up.jocuri cu barbie

karProonyvoda Posted 8 months, 1 week ago Joined: N/A

Please observe the following rules:
1. Write short, the purpose and reasoning for any opinion. Comments unrelated to the subject of the article will not be published.
2. If you do not know the author personally, would prefer him not to Tutu. We are old, sir!
3. Civilized language, but delicious, is desirable.
4. Our intention is to create a community of people with dilemmas that will seek to dialogue. "Opinionistii professional" who have strong opinions about everything, but no argument, are asked to not insist.jocuri cu motociclete

Empty Cells in Excel with =IF formula (8 posts)

Empty Cells in Excel with =IF formula (8 posts)

Post a Reply

Most Popular

Plan Comparison

Best Deals on PCWorld

Most Commented

Product Categories

Contact Us

Twitter Feed