How to get blank cell from IF formula

[johns99]

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

 

[jorismoerings]

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.

 

[johns99]

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.

 

[njimack]

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)

 

[johns99]

Hi Neil, good recommendation, and I’ll use this approach instead, thanks for you assistance!