Office & Productivity Software forum

General discussion

Ignore empty cells in Excel

Hi All,
I have a set of data, with a different number of data points in each column. I want to transform each data point by dividing the data by the background levels - easy enough, i'm using =(B6/$C$3), where C3 contains the background.

I've then dragged the selection to encompass all possible cells, but as there are different numbers of data points in each column, many of the cells return 0.00 with an error "refers to empty cell." This will then heavily impact on further analysis (averages etc), and i can't simply use an if function as 0 is a valid data point.

Basically, is there a way of getting excel to return an empty cell if the formula inputted refers to an empty cell?

Cheers
J

Discussion is locked
You are posting a reply to: Ignore empty cells in Excel
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Ignore empty cells in Excel
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
No.

In reply to: Ignore empty cells in Excel

You cant't write a formula that returns an empty value. It would erase itself, so to say.

But you can write a formula that return some fixed value (like "empty") if another cell is empty (=if(isempty(a1),"empty",a1+1) and you can write a macro that loops trough all cells and clears all that contain the value "empty". But it clears the formula also, so the macro should start with making a copy of the sheet and continue working in that copy.

Kees

Kees

Collapse -
Does this work?

In reply to: Ignore empty cells in Excel

Firstly, that formula, "=(B6/$C$3), where C3 contains the background". Is that in a different column, like D?

If so, then does this work?

=IF(ISERROR(B6/$C$3),B6,B6/$C$3)

That would skip the division if it would produce any of the error values (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!). Then it would just fill Dx with the value in Bx

Mark

Collapse -
Sorted!

In reply to: Does this work?

Brilliant!They both work great.
I think rather than writing a macro to clear the cells, i'll just paste the values into the next sheet.
Thanks for all the help.

Collapse -
Another way.

In reply to: Sorted!

Quite a lot of numerical Excel functions are programmed (by Microsoft) to ignore cells containing non-numeric values.

So, average (1,2,3) = 2, average (1,0,3)=1.333 and average (1,"",3) = 2. If the second value is based on an empty cell, and becomes 0 (disturbing the average function), you could replace the calculation by something like =if(isempty(a1),"",a1+1). The empty string ("") isn't empty, but it LOOKS like empty, and if the functions you need ignore it, it might be what you need.
But I can't check, because you didn't post an exhaustive list of the functions you want to use on the calculated fields.

Kees

Popular Forums

icon
Computer Newbies 10,686 discussions
icon
Computer Help 54,365 discussions
icon
Laptops 21,181 discussions
icon
Networking & Wireless 16,313 discussions
icon
Phones 17,137 discussions
icon
Security 31,287 discussions
icon
TVs & Home Theaters 22,101 discussions
icon
Windows 7 8,164 discussions
icon
Windows 10 2,657 discussions

SMART HOME

This one tip will help you sleep better tonight

A few seconds are all you need to get a better night's rest.