How to get blank cell from IF formula

johns99

Board Regular
Joined
Jun 11, 2013
Messages
223
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have the following formula in a cell:

=IF(H2>O2,"x","")

My understanding was that the "" would make the cell blank because the logic is FALSE, but that is not the case. When I test the formula with ISBLANK it results in FALSE when I'd expect it to be TRUE.

This is causing issues because I'm building a pivot table off of the information and it is counting the blank cells.

Thanks in advance.

John
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi,

This is a true problem and most experts will agree "excel can't solve this"
Why?

Anything you see on the excel sheet you've created is a formatted view of something you've entered.
So a cell with a formula in it, by definition, cannot be blank. Why? Because it has a formula in it, to create the blank view
A truly blank cell have nothing in it.

Now we've got that sorted, perhaps we're able to help you get out around the problem by telling us why you're counting blank cells and let us help you determine if there's an option other than counting blank cells.

Hope this helps.
 
Last edited:
Upvote 0
Hi, thank you for your response and explanation. I’m actually not trying to count the blank rows, I’m trying to count the rows with an “x”. After I input the formula I create a pivot to count the column with “x” and it counts the blank cells as well. My work around is to paste special values over the column, filter on blank rows, then delete. This solved the problem, but I usually like keeping the formula in the cells so if they’re any changes they’re captured immediately.

Hope this clarifies things, thanks.
 
Upvote 0
Rather than returning an "x", why not return a 1, which you can then sum in the pivot table. No need for an IF statement, just use:
--(H2>O2)
 
Upvote 0
Hi Neil, good recommendation, and I’ll use this approach instead, thanks for you assistance!
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top