Combining long formulas in Excel

T

tzav

New Member
Joined
Sep 5, 2022
Messages
16
Office Version
  1. 2010
Platform
  1. Windows
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:
Excel Formula: 
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:
Excel Formula: 
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 :
Excel Formula: 
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:
Excel Formula: 
=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
)
 

Attachments

  • Capture2.PNG
    Capture2.PNG
    12.2 KB · Views: 15
Last edited by a moderator:
Sort by date Sort by votes
Just another note. One big advantage to using a User Defined Function is that if you ever have to change the logic of the formula, you only need to change it in one place (in the VBA code of the function), instead of having to change every instance of the formula in your workbook.

BTW, your boss sounds like a horrible person to work for! In business, most people I know just want you to get the correct answer, and don't really care the method you use to get it. The point is to get it right in a timely manner (that is what successful businesses do). You don't get any extra points for doing it a "certain arbitrary way", without good reason to do it that way.

I could understand maybe they don't want VBA, because maybe their company has Security guidelines around using VBA code. But not allowing helper columns seems a bit ridiculous, especially when they can just be hidden so they are not even visible.

That all being said, I have had a few unreasonable bosses in my day, so I feel your pain!
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
How would this formula be used? Is it to be copied down a column so that it uses values in columns B, L, and M on the same row?
 
Upvote 0
🤣He is actually a nice person, but he kind of believes that the longer the formula, the better. By the way, he doesn't use pivot tables at all and doesn't allow me to use them at work. I suppose there are both pros and cons in every workplace. And, I'm not perfect either...😇
 
Upvote 0
KRice said:
How would this formula be used? Is it to be copied down a column so that it uses values in columns B, L, and M on the same row?
Click to expand...
Yes. It checks for every row those cells.
 
Upvote 0
Here is one approach that does not use Excel 365 functions, but it does require setting up several named variables using the Name Manager. This could be considered a work-around of sorts due to the constraints you've described. Briefly, the named variables are "be", "el", and "em" for the values in column B, L, and M on that row. You can see the results when each of these is referenced in the yellow cells...simply a repeat of what appears in columns B, L, and M on that row.
MrExcel_20240131.xlsx
DEF
220OT662
Sheet5
Cell Formulas
RangeFormula
D2D2=be
E2E2=el
F2F2=em
Named Ranges
NameRefers ToCells
be=Sheet5!$B2D2
el=Sheet5!$L2E2
em=Sheet5!$M2F2

Then the more complicated formulas that you currently have in columns W, X, and Y are used to create named variables "dub", "ex", and "wye", and those formulas refer to "be", "el", and "em". You can see the results generated by dub, ex, and wye in the orange cells.
I used this for defining "dub":
Excel Formula: 
=IF(be="20rf",
      MAX( IF(el<=em, 0,
                                           IF(em<=3,                            MIN(MAX(el-3, 0), 8),
                                           IF(AND(em>3, em<11), MIN(11-em, 8),
                                           IF(em>=11,                         0,                                     MAX(0, el-11) ) ) ) ), 0 ),
     MAX( IF(AND(el=11, em=10),  1,
                                                                IF(el<=em,                       0,
                                                                IF(em<=7,                         MIN(MAX(el-7, 0), 5),
                                                                IF(AND(em>7, em<12), MIN(12-em, 5),
                                                                IF(em>=12,                       0,
                                                                                                                MAX( IF(el<=em, 0,
                                                                                                                                                     IF(em<=7,                             MIN(MAX(el-12, 0), 7),
                                                                                                                                                     IF(AND(em>7, em<=12),   MIN(el-12, 7),
                                                                                                                                                     IF(AND(em>12, em<=19), MIN(el-em, 19-em, 7),
                                                                                                                                                     IF(em>19,                             0 ) ) ) ) ), 0 )
                                                                    ) ) ) )
                   ), 0 ) )

...and this for defining "ex""
Excel Formula: 
=MAX( IF(be="20rf",
                                      IF(el<=em,   0,
                                      IF(em>=11,   el-em,
                                      MAX(0, el-11) ) ),
                                                                           IF(AND(el<=em, em<=7), MIN(MAX(el-12, 0), 7), IF(AND(em>7, em<=12),   MIN(el-12, 7),
                                                                                                                                                                    IF(AND(em>12, em<=19), MIN(el-em, 19-em, 7),
                                                                                                                                                                    IF(em>19, 0,                         MAX(el-19, 0)  ) ) )
                                                                               )
                 ), 0 )

...and this for defining "wye":
Excel Formula: 
=MAX(IF(be="20rf", 0, IF(el<=em, 0, IF(em>=19, el-em, MAX(el-19, 0) ) ) ), 0 )

I thought it might be convenient to rework your main formula to use the SUMPRODUCT function, but that requires getting dub, ex, and wye into a single array that can be used in the SUMPRODUCT function...actually two different arrays are needed: one is essentially {dub, ex, wye} and the other is {dub, ex}. So two more named variable are created. One is called "wxy", and it combines dub, ex, and wye values into a single 3-element array. The other is called wx, and it combines only dub and ex into a single 2-element array. Results obtained for both of these are shown "spilled" in the pink cells. If you try this in pre-Excel 365, you won't see the spilled arrays.
This is used for defining the "wxy" array:
Excel Formula: 
=dub*{1,0,0}+ex*{0,1,0}+wye*{0,0,1}
...and this for the "wx" array"
Excel Formula: 
=dub*{1,0}+ex*{0,1}

Finally, the single formula appearing in your spreadsheet is found in the Results column, shown in red. It has been reworked as shown below. The formulas in W, X, and Y can be deleted, and none of the yellow, orange, and pink cells are necessary---they are shown only for clarity.
Excel Formula: 
=IF( OR(B2={"20FL","20OT","20OH"}),
                           MAX(0, SUMPRODUCT(wxy,{30,45,60})),
  IF( OR(B2={"40FC","40FL","40OH","40OT","40PL"}),
                           MAX(0, SUMPRODUCT(wxy,{50,70,95})),
  IF( OR(B2={"40RF","40RH"}),
                           MAX(0, AL2*110),
  IF( B2="20RF",
                           MAX(0, SUMPRODUCT(wx,{90,100})),
  IF( LEFT(B2,2)="20",
                           MAX(0, SUMPRODUCT(wxy,{30,35,40})),
  IF( LEFT(B2,2)="40",
                           MAX(0, SUMPRODUCT(wxy,{45,55,65}))      ))))))
MrExcel_20240131.xlsx
BCDEFGHIJKLMNOPQRSTUV
1beelemdubexwyewxywxResult
220OT20OT66254747662547475475085
340OH131250
Sheet5
Cell Formulas
RangeFormula
D2D2=be
E2E2=el
F2F2=em
H2H2=dub
I2I2=ex
J2J2=wye
O2:Q2O2=wxy
S2:T2S2=wx
V2:V3V2=IF( OR(B2={"20FL","20OT","20OH"}), MAX(0, SUMPRODUCT(wxy,{30,45,60})), IF( OR(B2={"40FC","40FL","40OH","40OT","40PL"}), MAX(0, SUMPRODUCT(wxy,{50,70,95})), IF( OR(B2={"40RF","40RH"}), MAX(0, AF2*110), IF( B2="20RF", MAX(0, SUMPRODUCT(wx,{90,100})), IF( LEFT(B2,2)="20", MAX(0, SUMPRODUCT(wxy,{30,35,40})), IF( LEFT(B2,2)="40", MAX(0, SUMPRODUCT(wxy,{45,55,65})) ))))))
Dynamic array formulas.
 
Upvote 0
I noticed I had an error...I forgot to define the column Z input. I've named it "zed". For the formula on row 2, it would refer to Z2 on that same worksheet. Then the final results formula would refer to zed rather than the call address...shown as AF2 in the example I posted...here...MAX(0, AF2*110), Should be MAX(0, zed*110),

Also, missed something else...references to cell B2 or B3 are better changed to the named variable "be" in the main formula.
 
Last edited:
Upvote 0
And here is a single formula version in Excel 365 using only the inputs in B, L, M, and Z:
Book3
BCLMNVYZ
1Result
220OT66250851
340OH1312501
440something83451
Sheet5
Cell Formulas
RangeFormula
V2:V4V2=LET(be, B2, el, L2, em, M2, zed, Z2, w, IF(be="20rf", MAX( IF(el<=em, 0, IF(em<=3, MIN(MAX(el-3, 0), 8), IF(AND(em>3, em<11), MIN(11-em, 8), IF(em>=11, 0, MAX(0, el-11) ) ) ) ), 0 ), MAX( IF(AND(el=11, em=10), 1, IF(el<=em, 0, IF(em<=7, MIN(MAX(el-7, 0), 5), IF(AND(em>7, em<12), MIN(12-em, 5), IF(em>=12, 0, MAX( IF(el<=em, 0, IF(em<=7, MIN(MAX(el-12, 0), 7), IF(AND(em>7, em<=12), MIN(el-12, 7), IF(AND(em>12, em<=19), MIN(el-em, 19-em, 7), IF(em>19, 0 ) ) ) ) ), 0 ) ) ) ) ) ), 0 ) ), x, MAX( IF(be="20rf", IF(el<=em, 0, IF(em>=11, el-em, MAX(0, el-11) ) ), IF(AND(el<=em, em<=7), MIN(MAX(el-12, 0), 7), IF(AND(em>7, em<=12), MIN(el-12, 7), IF(AND(em>12, em<=19), MIN(el-em, 19-em, 7), IF(em>19, 0, MAX(el-19, 0) ) ) ) ) ), 0 ), y, MAX(IF(be="20rf", 0, IF(el<=em, 0, IF(em>=19, el-em, MAX(el-19, 0) ) ) ), 0 ), wxy, HSTACK(w,x,y), wx, HSTACK(w,x), res, IF( OR(be={"20FL","20OT","20OH"}), MAX(0, SUMPRODUCT(wxy,{30,45,60})), IF( OR(be={"40FC","40FL","40OH","40OT","40PL"}), MAX(0, SUMPRODUCT(wxy,{50,70,95})), IF( OR(be={"40RF","40RH"}), MAX(0, zed*110), IF( be="20RF", MAX(0, SUMPRODUCT(wx,{90,100})), IF( LEFT(be,2)="20", MAX(0, SUMPRODUCT(wxy,{30,35,40})), IF( LEFT(be,2)="40", MAX(0, SUMPRODUCT(wxy,{45,55,65})) )))))), res)
 
Upvote 0
KRice said:
And here is a single formula version in Excel 365 using only the inputs in B, L, M, and Z:
Book3
BCLMNVYZ
1Result
220OT66250851
340OH1312501
440something83451
Sheet5
Cell Formulas
RangeFormula
V2:V4V2=LET(be, B2, el, L2, em, M2, zed, Z2, w, IF(be="20rf", MAX( IF(el<=em, 0, IF(em<=3, MIN(MAX(el-3, 0), 8), IF(AND(em>3, em<11), MIN(11-em, 8), IF(em>=11, 0, MAX(0, el-11) ) ) ) ), 0 ), MAX( IF(AND(el=11, em=10), 1, IF(el<=em, 0, IF(em<=7, MIN(MAX(el-7, 0), 5), IF(AND(em>7, em<12), MIN(12-em, 5), IF(em>=12, 0, MAX( IF(el<=em, 0, IF(em<=7, MIN(MAX(el-12, 0), 7), IF(AND(em>7, em<=12), MIN(el-12, 7), IF(AND(em>12, em<=19), MIN(el-em, 19-em, 7), IF(em>19, 0 ) ) ) ) ), 0 ) ) ) ) ) ), 0 ) ), x, MAX( IF(be="20rf", IF(el<=em, 0, IF(em>=11, el-em, MAX(0, el-11) ) ), IF(AND(el<=em, em<=7), MIN(MAX(el-12, 0), 7), IF(AND(em>7, em<=12), MIN(el-12, 7), IF(AND(em>12, em<=19), MIN(el-em, 19-em, 7), IF(em>19, 0, MAX(el-19, 0) ) ) ) ) ), 0 ), y, MAX(IF(be="20rf", 0, IF(el<=em, 0, IF(em>=19, el-em, MAX(el-19, 0) ) ) ), 0 ), wxy, HSTACK(w,x,y), wx, HSTACK(w,x), res, IF( OR(be={"20FL","20OT","20OH"}), MAX(0, SUMPRODUCT(wxy,{30,45,60})), IF( OR(be={"40FC","40FL","40OH","40OT","40PL"}), MAX(0, SUMPRODUCT(wxy,{50,70,95})), IF( OR(be={"40RF","40RH"}), MAX(0, zed*110), IF( be="20RF", MAX(0, SUMPRODUCT(wx,{90,100})), IF( LEFT(be,2)="20", MAX(0, SUMPRODUCT(wxy,{30,35,40})), IF( LEFT(be,2)="40", MAX(0, SUMPRODUCT(wxy,{45,55,65})) )))))), res)
[/RA
Click to expand...
Cell Formulas
RangeFormula

KRice said:
And here is a single formula version in Excel 365 using only the inputs in B, L, M, and Z:
Book3
BCLMNVYZ
1Result
220OT66250851
340OH1312501
440something83451
Sheet5
Cell Formulas
RangeFormula
V2:V4V2=LET(be, B2, el, L2, em, M2, zed, Z2, w, IF(be="20rf", MAX( IF(el<=em, 0, IF(em<=3, MIN(MAX(el-3, 0), 8), IF(AND(em>3, em<11), MIN(11-em, 8), IF(em>=11, 0, MAX(0, el-11) ) ) ) ), 0 ), MAX( IF(AND(el=11, em=10), 1, IF(el<=em, 0, IF(em<=7, MIN(MAX(el-7, 0), 5), IF(AND(em>7, em<12), MIN(12-em, 5), IF(em>=12, 0, MAX( IF(el<=em, 0, IF(em<=7, MIN(MAX(el-12, 0), 7), IF(AND(em>7, em<=12), MIN(el-12, 7), IF(AND(em>12, em<=19), MIN(el-em, 19-em, 7), IF(em>19, 0 ) ) ) ) ), 0 ) ) ) ) ) ), 0 ) ), x, MAX( IF(be="20rf", IF(el<=em, 0, IF(em>=11, el-em, MAX(0, el-11) ) ), IF(AND(el<=em, em<=7), MIN(MAX(el-12, 0), 7), IF(AND(em>7, em<=12), MIN(el-12, 7), IF(AND(em>12, em<=19), MIN(el-em, 19-em, 7), IF(em>19, 0, MAX(el-19, 0) ) ) ) ) ), 0 ), y, MAX(IF(be="20rf", 0, IF(el<=em, 0, IF(em>=19, el-em, MAX(el-19, 0) ) ) ), 0 ), wxy, HSTACK(w,x,y), wx, HSTACK(w,x), res, IF( OR(be={"20FL","20OT","20OH"}), MAX(0, SUMPRODUCT(wxy,{30,45,60})), IF( OR(be={"40FC","40FL","40OH","40OT","40PL"}), MAX(0, SUMPRODUCT(wxy,{50,70,95})), IF( OR(be={"40RF","40RH"}), MAX(0, zed*110), IF( be="20RF", MAX(0, SUMPRODUCT(wx,{90,100})), IF( LEFT(be,2)="20", MAX(0, SUMPRODUCT(wxy,{30,35,40})), IF( LEFT(be,2)="40", MAX(0, SUMPRODUCT(wxy,{45,55,65})) )))))), res)
Click to expand...
Thank you so much for the detailed answer, I really appreciate the time and effort🙏 I will try to use it.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
find the top 3 and lowest number using the condition formatting
2 3
Replies
24
Views
1K
Peter_SSs
Peter_SSs
T
  • Solved
Scaling a Data Set
Replies
6
Views
292
felixstraube
F
J
  • Question Question
Move more blocks (sort by POBxx)
Replies
2
Views
185
jerry00769
J
A
  • Question Question
Loan Tracker with prior/missed payments
Replies
0
Views
60
aurelius33
A
A
  • Solved
add formulas in last column after sorting for each duplicates ID
Replies
9
Views
126
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top