ISBLANK Formula

pixel_

Hello,

I have a formula for a Health/RYG column:

=IF([Working Days Remaining]32 < 0, "Red", IF([Working Days Remaining]32 < 2, "Yellow", "Green"))

It works well except if Working Days Remaining is blank, I would like the cell to stay blank. I think this is where ISBLANK comes in but I am struggling to use it. Can I get some help with this?

 

 

Many thx!

Mike Wilday

Try this:

=IF(Isblank([Working Days Remaining]@row), "", IF([Working Days Remaining]@row< 0, "Red", IF([Working Days Remaining]@row< 2, "Yellow", "Green")))

I switched the indicated from 32 to @row so you can copy and paste the formula into any row and ensure that the formula will work with the current row's data. 

pixel_

Eureka! It worked.

I was missing the first ) after the ISBLANK function. Thanks, Mike!

 

Mike Wilday

You're welcome. I'm glad I could help you get it working. Yes. Every formula you open in your IF statements needs to be closed before you continue your IF statements except nested if Statements themselves. You can kind of see this in the helper dropdown of the formula as you're typing it. See my screenshot as I type so you can see where you are in the current formula. 

Paul Newcome

An additional note to Mike's tip on closing out statements...

 

I build out each statement individually and use cell references. It helps with trouble shooting and making sure you have everything you need. I then replace the cell references with the formulas that are in those particular cells, and it's done.

 

A short example using an INDEX/MATCH........

 

=INDEX(range_to_pull_data_from, row_number, [column_number])

=MATCH(search_value, search_range, match_type)

.

I want to use the MATCH function to populate a row number for my INDEX function.

 

So I will use the cell [Column1]1 for my INDEX and put my MATCH in [Column1]2.

 

In [Column1]1 I would have:

 

=INDEX([Column Name]1:[Column Name]30, [Column1]2)

.

Then in [Column1]2, I would build out my MATCH statement.

 

Once I have both working individually, I just copy/paste the MATCH formula from [Column1]2 into the INDEX formula where is says "[Column1]2".

 

This ensures that both functions are working as needed, and my MATCH statement is properly closed.

.

It may not seem like much in this example, but some of the crazier messes where you have nested SUBSTITUTES using @cell references within a COUNTIFS statement that is part of a nested IF statement or some crazy mess like that... It definitely makes life much easier.

Mike Wilday

Sometimes formulas are giant mazes. 

If only there was a hedgetrimmer. #bartSimpson