How can I specify a range in one column and return a value in another column?

Michael Figone

I want something like this (that works:-))

=IF([Estimated Days to Completion]<10, "Red", IF [Estimated Days to Completion]>10, "Green")

So, if I specify a value less than 10 in one column, a red dot appears in the other column. Same idea for green.

Thanks.

Cheryl Crouse

You need another IF statement in there, and I'm assuming if the value IS 10 you want it to be green as well...?

Try this:

=IF([Estimated Days to Completion]@row < 10, "Red", IF([Estimated Days to Completion]@row >= 10, "Green", ""))

What if the cell is blank? Do you need to address that as well? Using the above, it will give you a red dot. If you want it to be blank (no dot) then use this:

=IF(ISBLANK([Estimated Days to Completion]@row), "", IF([Estimated Days to Completion]@row < 10, "Red", IF([Estimated Days to Completion]@row >= 10, "Green", "")))

Jayesh Nathani

Hi Michael,

Use this in your other column:

=IF([Estimated Days to Completion]@row < 10, "Red", [Estimated Days to Completion]@row > 10, "Green")

but do make sure that this column is of "Symbol" type and select the correct option having Red, Yellow, Green.

Hope it helps!

Thanks,

Jayesh

Cheryl Crouse

You need another IF statement in there, and I'm assuming if the value IS 10 you want it to be green as well...?

Try this:

=IF([Estimated Days to Completion]@row < 10, "Red", IF([Estimated Days to Completion]@row >= 10, "Green", ""))

What if the cell is blank? Do you need to address that as well? Using the above, it will give you a red dot. If you want it to be blank (no dot) then use this:

=IF(ISBLANK([Estimated Days to Completion]@row), "", IF([Estimated Days to Completion]@row < 10, "Red", IF([Estimated Days to Completion]@row >= 10, "Green", "")))

Michael Figone

Cool! Thanks. It worked. What if I wanted to throw an AND statement in there somewhere?