ISBLANK Formula

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!

Find more posts tagged with

Formulas

Comments

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

maze - alice in wonderland .gif

Maze Shortcut - simpsons.gif

Sign in to join the conversation!

Quick Links

Trending discussions

Netdays Formula
I am wanting to calculate Netdays but only if there is data in 'Corrective Action 1' I am currently using this formula: =IF(ISBLANK([Date Action 1 Complete]@row ), NETDAYS([Date of Event]@row , TODAY()), NETDAYS([Date of Event]@row , [Date Action 1 Complete]@row )) How do I adjust it to only pull a date when Corrective…
Re-write a formula to make it Column Formula compatible
I have Concept phase projects that can result in multiple Phase 1 projects being generated from one Concept Review. I want to calculate the time in the Concept Phase for the first project from that Concept (the Approved Gate 0 UID), but mark it N/A for any subsequent references to that UID. I figured out the formula below…
How to split contents from a cell from a smartsheet into another smartsheet
I need to extract and split the contents of a cell in a Smartsheet into three separate columns to a new smartsheet. The cell entries are originally submitted through a form.