IF Statement That Returns 100%

How to create an IF statement that returns 100% when the results are greater than 100%.

Last updated: Feb. 7, 2023

IF statements allow the formulas to make logical comparisons between a value and an expected outcome. For example, if your team has surpassed their target, you can create a formula that tells Plecto to return 100%.

An example of a table widget displaying an IF statement with conditional colors in the column "Index 100."

The image above illustrates that Plecto will return 100% in the "Index 100" column, even though the first five employees have already reached more than 100% of their target. It means that Plecto will stop at 100%, based on the following IF statement:

(
    IF(
        Sum(Sales,Amount)>=0,
            IF(
                Sum(Sales,Amount)<40000,
                    Sum(Sales,Amount)/40000*100,
                    100
            ),
            0
    )
)%

With this formula, Plecto will return the actual value if the calculated result is between 0% and 100%. If it is at 100% or above, Plecto will return 100%. Because of the percentage conversion components within the formula, it will calculate the value in percentages. Therefore, add a percentage suffix (%) after the last parenthesis and set the number formatting to "Decimal number."

For better visualizing, feel free to add conditional colors to the formula. Keep in mind that the calculated result will be a percentage value, therefore, type in the color conditions based on values between 0 and 100. In the image below, we have selected 100, 80, and 0, which means that the widget will display the following conditional colors for the results:

Green: 100% and more
Yellow: 80% - 99%
Red: 0% - 79%