CODEWITHSUNDEEP
excelif-statement
Helpplease
Helpplease

Reputation: 37

How could this IF formula me simplified? If possible

I have this formula:

=IF(C7="","",IF(C6>C5,IF(C7<=C5,0,(C7-C5)/(C6-C5)),IF(C6<C5,IF(C7>=C5,0,ABS(C7-C5)/(C5-C6)))))

referencing these cells:

enter image description here

Formula is a little confusing to the average person, however it gets me what I'm looking for. Is there any way to simplify this?

Upvotes: 0

Views: 45

Answers (1)

Dave Thunes
Dave Thunes

Reputation: 280

Wow, this is fun.

You have an implicit 0 in your first IF statement that I think should be a "". That would make the final undefined if the minimum achievement equals the max achievement.

If I'm correct, you could simplify to =IFERROR(MAX((C7-C5)/(C6-C5),0),""). To replicate your formula, you could simplify to IF(C5<>"",IFERROR(MAX((C7-C5)/(C6-C5),0),0),"").

If you have Office 365, I recommend you use a LET function to further describe you code. The LET function allows you to define local variables in a formula. You can also add white space to better organize the formula.

Here is your formula using the let function. Its longer, but easier to tell what's going on:

=LET(
    Achiev0, C5,
    Achiev100, C6,
    Actual, C7,

    IF(
        Actual="",
        "",
        IF(Achiev100>Achiev0,
            IF(Actual<=Achiev0,
                0,
                (Actual-Achiev0)/(Achiev100-Achiev0)),
            IF(Achiev100<Achiev0,
                IF(Actual>=Achiev0,
                    0,
                    ABS(Actual-Achiev0)/(Achiev0-Achiev100)),
                0
    )))
)

If you define a few more variable, you can get something like this:

=LET(
    Achiev0, C5,
    Achiev100, C6,
    Actual, C7,

    NoValue, "",
    OutOfBounds, 0,
    Final, ABS((Actual-Achiev0)/(Achiev100-Achiev0)),

    IF(Actual = "", NoValue,
        IF(AND(Achiev0 <= Achiev100, Actual <= Achiev0), OutOfBounds,
            IF(AND(Achiev0 >= Achiev100, Achiev0 <= Actual), OutOfBounds,
                IFERROR(Final,OutOfBounds)
    )))
)

My simplification above would look like this:

=LET(
    Achiev0, C5,
    Achiev100, C6,
    Actual, C7,

    Final, (Actual-Achiev0)/(Achiev100-Achiev0),
    IFERROR(MAX(Final,0),"")
)

Upvotes: 1

Related Questions