Best Of

Hey @aneekahTAL

The syntax for your COUNTIFS is not correct. COUNTIFS have the syntax of =COUNTIFS({Range 1}, criteria 1, {Range 2}, criteria, {Range 3 etc}, criteria 3}. A COUNTIFS can also be used with only one Range-criteria pair.

=COUNTIFS({TPP Rollout - Hubs Range 2}; "A"; {State 9}; "Complete"; {TPP Rollout - Hubs Range 1}; "FY25")

Does this work for you?
Kelly

@Georgie ,

I figured it out!

I used this formula

=IFERROR(AVG(CHILDREN()),

IF(Status@row = "Complete", "1",

IF(Status@row = "In Progress", ".25",

IF(Status@row = "Blocked", ".5",

IF(Status@row = "Not Started", "0",

"")))))

Then I formatted the column with Percentage

Hi @Paul.Woodward

The third IF condition ([Month of Contract]@row > [Contract Length]@row + 12) is never reached because the second IF already catches all cases where [Month of Contract]@row > [Contract Length]@row.

For example:
If the Contract Length is 12 months and the Month of Contract is 15, it meets the condition [Month of Contract]@row > [Contract Length]@row but does not meet [Month of Contract]@row > [Contract Length]@row + 12. The formula should apply the "After Initial Contract %" rate in this case.

However, if the Month of Contract is 25, it does meet both conditions ([Month of Contract]@row > [Contract Length]@row and [Month of Contract]@row > [Contract Length]@row + 12). The formula should apply the "12+ Month Contract %" rate in this case.

So, bring the IF([Month of Contract]@row > [Contract Length]@row + 12, before, and [Month of Contract]@row > [Contract Length]@row will be treated as the "else" case if not.

=IF([Month of Contract]@row <= [Contract Length]@row, 
    [Contract Amount]@row * [Initial Contract %]@row, 
    IF([Month of Contract]@row > [Contract Length]@row + 12, 
        [Contract Amount]@row * [12+ Month Contract %]@row, 
        [Contract Amount]@row * [After Initial Contract %]@row
    )
)

In the Corrected Formula:

• If [Month of Contract]@row <= [Contract Length]@row → Apply Initial Contract %.
• Else, if [Month of Contract]@row > [Contract Length]@row + 12 → Apply 12+ Month Contract %.
  • This condition will be reached if the contract month is more than 12 months past the contract length.
• Else → Apply After Initial Contract %.
  • This part catches all cases greater than [Contract Length]@row but less than [Contract Length]@row + 12.

Hi @John Stanik

Will your text entry always have the exact same format of mm/dd/yy?

You can use the DATE function to translate numbers into Dates, with the syntax being DATE(YYYY, MM, DD).

In this instance, we'll want to use the VALUE Function around each number set we're grabbing, like so:

DATE(VALUE(20 + YY), VALUE(MM), VALUE(DD))

Then we can use the RIGHT Function, MID Function, and LEFT Function to grab different elements of your text string.

DATE(VALUE(20 + RIGHT(YY)), VALUE(LEFT(MM)), VALUE(MID(DD)))

For the MID portion, we'll need to use a FIND Function to find the starting position (a number) of whatever it is looking for, in this case a "/". The formula uses the number it finds as a starting position in the text string.

Full Formula:

=DATE(VALUE(20 + RIGHT([Text Column]@row, 2)), VALUE(LEFT([Text Column]@row, 2)), VALUE(MID([Text Column]@row, FIND("/", [Text Column]@row) + 1, 2)))

You'll need to enter the formula into a Date Column. Let me know if this works and makes sense!

Cheers,

Genevieve