Help with formula that has negative numbers

Cathy Fraser

Hi, I am trying to work out a formula that would return the below # value so I can then change status in a different column

0% to 25% = "1"

26% to 100% = "2"

-1% to -25% = "3"

-26% to -50% = "4"

-51% to -100% = 5

this is the formula that I am trying to get to work, it only gets so far and then stops.

'=IF([% Completed Vs Time Lapsed% (H)]@row < -0.51, "5", IF([% Completed Vs Time Lapsed% (H)]@row > -0.51, "4", IF([% Completed Vs Time Lapsed% (H)]@row > -0.26, "3", IF([% Completed Vs Time Lapsed% (H)]@row < 0.99, "2", IF([% Completed Vs Time Lapsed% (H)]@row < 0.26, "1")))))

this is the column that is being used

The last column is the one I am trying to build the # values off %Completed-Time Lapsed% then they want to outline the below

= Result

0 to 25 On Track (Light Green) =1

26 to 100 Ahead of schedule (Richer Green) = 2

- 1 to - 25 Mildly Behind schedule (Yellow) = 3

-26 to -50 Considerably Behind Schedule (Orange) = 4

- 51 to -100 Schedule at Risk (Red) = 5

So I thought I would give each bracket a number value in a helper column and then drive the change in status from the number value

I hope that all make sense.

Thanks for your help

Cathy

Cathy Fraser

Hi Andree,

Finally got the formula to work, so I thought I would just let you know what it ended up being.

=IFERROR(IF([% Completed Vs Time Lapsed% (H)]@row > 0.25, 2, IF([% Completed Vs Time Lapsed% (H)]@row > 0.01, 1, IF([% Completed Vs Time Lapsed% (H)]@row > -0.25, 3, IF([% Completed Vs Time Lapsed% (H)]@row > -0.51, 4, IF([% Completed Vs Time Lapsed% (H)]@row < -0.52, 5))))), "6")

Thanks so much for your help, with out I would not have gotten to the final solution.

Regards

Cathy