Append extra conditions to formula

Platform discussions Special workflows & use cases
1

Hi,

I hope all is well and I was wondering if anyone can help me with my formula.

I have a Monday board with two columns (Hrs Completed So Far) and (Total Contract Hrs). Both of these boards has an integer value in them. The below formula adds text into a third column to say if the hours so far are over the total contract hours then show “Over Delivering”, but if the hours so far are below the total contract hrs then to show “Under Delivering”.

The formula:
IF({Hrs Completed So Far}>{Total Contract Hrs}, “Over Delivering”, “Under Delivering”)

However, I would like to add more conditions to this such as:

If the Hrs Completed So Far is equal to Total Contract Hrs to then show “Delivered”
and/or
If the Hrs Completed So Far is 5 less than the Total Contract Hrs to then show “Nearing Contract Limit”

I have no idea how to append these two statements onto the existing Formula I already have.

I hope this makes sense, if not, please let me know and I will try my best to clarify it a bit more for you.

Kind regards

2

It’s a little confusing at first. The simplest way to manage this is to nest some if statements. Formatting the formula makes this easier to understand visually. Can’t be sure there’s not syntax error somewhere here, but you’re aiming for something like this:

IF(
    {Hrs Completed So Far}>{Total Contract Hrs}, 
    “Over Delivering”,
    IF(
        {Hrs Completed So Far}={Total Contract Hrs},
        "Delivered",
        IF(
            {Total Contract Hrs} - {Hrs Completed So Far}>5,
            "Nearing Contract Limit",
            "Under Delivering"
        )
    )     
)

Essentially you’re putting a new IF statement inside the ELSE statement of the previous IF.

When this gets a little unwieldy, you can experiment with using the CONCATENATE function. You can essentially mash together the results of multiple IF statements in that way. Make sure you set the ELSE value as “” and that all your IF statements are mutually exclusive, so that the IF statements don’t append together.

2 Likes
3

Hi @FrancisElliott,

Thank you so much for your reply to my formula issue.

This makes perfect sense and I have used your solution which has worked out of the box.

Your wisdom is greatly appreciated :slight_smile:

Kindest regards,
Anthony

1 Like