1

Hi I have an Excel formula and I would like to translate it/rewrite using Python. But as you can see, it is a huge condition strcture. Each Excel cell is a column from my dataset, the definition of it does not matter here since I just to know how to translate it to Python language. So any help is really apreciated. The follow code I would like to create a condidtion sctrucure is: I would like to know how it could be created using if conditions.

#Review Reason Formula
=IF(AND(DG1=1,G1+H1>8*BV1,G1+H1>0,AC1+AR1<10),0,IF(AND(DG1=1,G1+H1>30),0,IF(DG1-J1>0,IF(K1=1,IF(CK1<3,"NLA",IF(DP1=1,"auto_URGENTE","auto")),IF(SUM(M1:N1)>0,IF(M1=5,"Quality Block","Active Replacement"),IF(DN1=1,IF(DG1>0,"Demand",0),IF(CK1>$BJ$5,(IF(AND(DD1>0,E1=1),IF(DP1=1,"auto_URGENTE","auto"),IF(I1>0,"External PO",IF(AND(DH1=0,DP1=0),0,IF(DP1=1,"auto_URGENTE","auto"))))),IF(CK1>$BJ$6,IF(DP1=1,"auto_URGENTE","auto"),"Critical"))))),IF(AND(G1+H1+I1+J1+SUM(BK1:BQ1)<0,CI1>0),"BO",IF(K1&L1&M1&N1="0000",IF(OR((AB1+AA1)*0.4<AVERAGE(O1:AB1),(AQ1+AP1)*0.4<AVERAGE(AD1:AQ1)),IF(G1+I1+H1+J1<AB1+AA1+AQ1+AP1,IF((VLOOKUP(A1,SumIfsReplacement!A:B,2,0))*1>AA1+AB1+AP1+AQ1,IF(CN1<0,"Possible BO",0),0),0),0),0)))))
Yan
  • 105
  • 7
  • Can you create a some sample input as well as expected outputs from this input. Should have enough data to capture each leg of your if then tree here. – Scott Boston Jun 08 '21 at 18:15
  • 1
    Good God, man. If a programmer came to me with that formula, I'd fire him. I'm going to post an answer that shows the nesting levels in this expression. Maybe that will help. – Tim Roberts Jun 08 '21 at 18:18
  • It's a parsing problem, so maybe you could use something like [PyParsing](https://github.com/pyparsing/pyparsing) or build on something from NLTK to get the parts of the equation, then try to refactor it into Python. Also see [this SO question](https://stackoverflow.com/questions/36953/resources-for-lexing-tokenising-and-parsing-in-python). – fsimonjetz Jun 08 '21 at 18:23
  • Maybe, does this answer your question? [parsing excel style formula](https://stackoverflow.com/questions/12929086/parsing-excel-style-formula) – fsimonjetz Jun 08 '21 at 18:27
  • VBA is the best choice for Excel sheet portability. I.e. don't use python. – jwal Jun 08 '21 at 19:13

1 Answers1

0

Here are the nesting levels in this expression. Maybe this will help you translate it, but I'm dubious.

=IF(
    AND(DG1=1,G1+H1>8*BV1,G1+H1>0,AC1+AR1<10),0,
    IF(
        AND(DG1=1,G1+H1>30),0,
        IF(DG1-J1>0,
            IF(K1=1,
                IF(CK1<3,"NLA",IF(DP1=1,"auto_URGENTE","auto")),
                IF(SUM(M1:N1)>0,
                    IF(M1=5,"Quality Block","Active Replacement"),
                    IF(DN1=1,
                        IF(DG1>0,"Demand",0),
                        IF(CK1>$BJ$5,
                            (IF(AND(DD1>0,E1=1),
                                IF(DP1=1,"auto_URGENTE","auto"),
                                IF(I1>0,"External PO",
                                    IF(AND(DH1=0,DP1=0),0,
                                        IF(DP1=1,"auto_URGENTE","auto")
                                    )
                                )
                            )),
                            IF(CK1>$BJ$6,
                                IF(DP1=1,"auto_URGENTE","auto"),"Critical"
                            )
                        )
                    )
                )
            ),
            IF(
                AND(G1+H1+I1+J1+SUM(BK1:BQ1)<0,CI1>0),"BO",
                IF(
                    K1&L1&M1&N1="0000",
                    IF(
                        OR((AB1+AA1)*0.4<AVERAGE(O1:AB1),(AQ1+AP1)*0.4<AVERAGE(AD1:AQ1)),
                        IF(G1+I1+H1+J1<AB1+AA1+AQ1+AP1,
                            IF((VLOOKUP(A1,SumIfsReplacement!A:B,2,0))*1>AA1+AB1+AP1+AQ1,IF(CN1<0,"Possible BO",0),0
                            ),0
                        ), 0
                    ), 0
                )
            )
        )
    )
)
Tim Roberts
  • 48,973
  • 4
  • 21
  • 30