Combining long formulas in Excel

[tzav]

Hi,
I need to take this formula:
=IF(OR(B2="20FL", B2="20OT", B2="20OH"), MAX(0, W2*30 + X2*45 + Y2*60), IF(OR(B2="40FC", B2="40FL", B2="40OH", B2="40OT", B2="40PL"), MAX(0, W2*50 + X2*70 + Y2*95), IF(OR(B2="40RF", B2="40RH"), MAX(0, Z2*110), IF(B2="20RF", MAX(0, W2*90+X2*100), IF(LEFT(B2,2)="20", MAX(0, W2*30 + X2*35 + Y2*40), IF(LEFT(B2,2)="40", MAX(0, W2*45 + X2*55 + Y2*65)))))))

but cells w2,x2,y2 will not exist in it, instead these should be the values:
w2 as:

IF(B2="20rf",
    MAX(
        IF(L2<=M2,
            0,
            IF(M2<=3,
                MIN(IF(L2-3<0, 0, L2-3), 8),
                IF(AND(M2>3, M2<11),
                    MIN(11-M2, 8),
                    IF(M2>=11,
                        0,
                        MAX(0, L2-11)
                    )
                )
            )
        ),
        0
    ),
    MAX(
        IF(AND(L2=11, M2=10),
            1,
            IF(L2<=M2,
                0,
                IF(M2<=7,
                    MIN(IF(L2-7<0, 0, L2-7), 5),
                    IF(AND(M2>7, M2<12),
                        MIN(12-M2, 5),
                        IF(M2>=12,
                            0,
                            MAX(
                                IF(L2<=M2,
                                    0,
                                    IF(M2<=7,
                                        MIN(IF(L2-12<0, 0, L2-12), 7),
                                        IF(AND(M2>7, M2<=12),
                                            MIN(L2-12, 7),
                                            IF(AND(M2>12, M2<=19),
                                                MIN(L2-M2, 19-M2, 7),
                                                IF(M2>19, 0)
                                            )
                                        )
                                    )
                                ),
                                0
                            )
                        )
                    )
                )
            )
        ),
        0
    )
)

x2 as:

MAX(
    IF(B2="20rf",
        IF(L2<=M2,
            0,
            IF(M2>=11,
                L2-M2,
                MAX(0, L2-11)
            )
        ),
        IF(AND(L2<=M2, M2<=7),
            MIN(IF(L2-12<0, 0, L2-12), 7),
            IF(AND(M2>7, M2<=12),
                MIN(L2-12, 7),
                IF(AND(M2>12, M2<=19),
                    MIN(L2-M2, 19-M2, 7),
                    IF(M2>19,
                        0,
                        MAX(L2-19, 0)
                    )
                )
            )
        )
    ),
    0
)

y2 as :

MAX(
    IF(B2="20rf",
        0,
        IF(L2<=M2,
            0,
            IF(M2>=19,
                L2-M2,
                MAX(L2-19, 0)
            )
        )
    ),
    0

You should not use helper columns. all formulas in one cell.
Can someone write a combined formula that works?

Thanks

Auxiliary formula: A way to see the three results at once in the format a, b, c:

=IF(B2="20rf",
    MAX(
        IF(L2<=M2,
            0,
            IF(M2<=3,
                MIN(IF(L2-3<0, 0, L2-3), 8),
                IF(AND(M2>3, M2<11),
                    MIN(11-M2, 8),
                    IF(M2>=11,
                        0,
                        MAX(0, L2-11)
                    )
                )
            )
        ),
        0
    ),
    MAX(
        IF(AND(L2=11, M2=10),
            1,
            IF(L2<=M2,
                0,
                IF(M2<=7,
                    MIN(IF(L2-7<0, 0, L2-7), 5),
                    IF(AND(M2>7, M2<12),
                        MIN(12-M2, 5),
                        IF(M2>=12,
                            0,
                            MAX(
                                IF(L2<=M2,
                                    0,
                                    IF(M2<=7,
                                        MIN(IF(L2-12<0, 0, L2-12), 7),
                                        IF(AND(M2>7, M2<=12),
                                            MIN(L2-12, 7),
                                            IF(AND(M2>12, M2<=19),
                                                MIN(L2-M2, 19-M2, 7),
                                                IF(M2>19, 0)
                                            )
                                        )
                                    )
                                ),
                                0
                            )
                        )
                    )
                )
            )
        ),
        0
    )
)
& "," &
MAX(
    IF(B2="20rf",
        IF(L2<=M2,
            0,
            IF(M2>=11,
                L2-M2,
                MAX(0, L2-11)
            )
        ),
        IF(AND(L2<=M2, M2<=7),
            MIN(IF(L2-12<0, 0, L2-12), 7),
            IF(AND(M2>7, M2<=12),
                MIN(L2-12, 7),
                IF(AND(M2>12, M2<=19),
                    MIN(L2-M2, 19-M2, 7),
                    IF(M2>19,
                        0,
                        MAX(L2-19, 0)
                    )
                )
            )
        )
    ),
    0
)
& "," &
MAX(
    IF(B2="20rf",
        0,
        IF(L2<=M2,
            0,
            IF(M2>=19,
                L2-M2,
                MAX(L2-19, 0)
            )
        )
    ),
    0
)

 

[Joe4]

For formulas this complex, I would be included to use VBA to create my own custom User Defined Function.
It makes it much easier to edit, maintain, and follow/see what is going on.

See here for instructions on how to do this: Creating a User Defined Function (UDF) in Excel VBA [Ultimate Guide]

 

[tzav]

Thank you for your response. The thing is that the requirement is that it be done in one formula in one cell, no VBA and no auxiliary columns.

 

[Joe4]

Thank you for your response. The thing is that the requirement is that it be done in one formula in one cell, no VBA and no auxiliary columns.

That seems a bit restrictive (and arbitrary).
Is this some sort of school assignment?

 

[tzav]

My boss insists, I don't know what his reasons are.
I don't know what to tell him so he doesn't think it's because of a lack of understanding.

 

[KRice]

To confirm a few things:

1) When you say that no helper cells should be used, does that also mean that a lookup table cannot be used (for example, a table where one cell’s value could be compared to several other values, and a corresponding desired result from an adjacent column in the lookup table would be returned)?

2) Your profile indicates use of Excel 2010…is this correct?

3) What are your thoughts about declaring the w, x, and y values through named variables using Excel’s Name Manager?

 

[tzav]

1) Not using another table. One cell, one formula.
2) At work I have 2016. I saw that there is a new Let function in Excel but I think it is not in 2016.
3) The formula will be copied to different files so if it is something you do not in the formula itself I'm not sure it will work.

Thank you

 

[tzav]

1) Not using another table. One cell, one formula.
2) At work I have 2016. I saw that there is a new Let function in Excel but I think it is not in 2016.
3) The formula will be copied to different files so if it is something you do not in the formula itself I'm not sure it will work.

Thank you

Correction: I have Excel 365 at work. So Let function might work. I'm not sure how to use it here.

 

[Special-K99]

So if the w x y formulas need changing in the future...
...you're going to have to change EACH AND EVERY COPY of them wherever they occur...
...and hope that a mistake is not made!
That's ridiculous!

That type of idea nullifies things like subroutines.

It sounds like your boss is the one who lacks the understanding of spreadsheets.

Tell him you've spoken to the experts and they think it's a lousy idea.
Or give me his number, I'll tell him myself!

 

[KRice]

Thanks for the answers. As @Joe4 has pointed out, these constraints are problematic. You could merge the w,x,y formulas into the main formula, but you would have to do so in every place where one of those cell references appears...and the result would be a very long, complex formula that would be difficult to understand and maintain. I doubt anyone here would really recommend that. I asked about your Excel version because, yes, if you had Excel 365, you could use the LET function to define variables w, x, and y exactly as they are now, and then the cell references for w2, x2, and y2 would instead refer the variables w, x, and y. Alternatively, you could establish named variables in Excel's Name Manager that correspond to those same formulas, and refer to the named variables in the main formula. When the formula is copied from one sheet to some place else, the named variables should automatically go along with it.

I just saw your note about 365. I'll offer two versions: one based on LET, the other based on Named Variables.