Nested If statement

D

Deelof

New Member
Joined
Aug 30, 2023
Messages
46
Office Version
  1. 2019
Platform
  1. Windows
Hi,

I have the below formula and want to add another criteria but cannot get it to work, need help!

=IF(OR(ISNUMBER(SEARCH({"SV","SERV","SRV","FPKARCH","XS"},$C2))),"SERVICE",IF(OR($F2="ONTRAN",$F2="PURCH"),"ALLOCATION ONTRAN/PURCH",IF(AND($D2<TODAY(),$I2="",$A2="FALSE",$J2="",$B2<>"ON HOLD"),"UPDATE PD",IF(AND($J2="TRUE",$K2=$G5,$D2>TODAY()),"ALLOCATE TO PO",IF(AND($B2="ON HOLD",$D2<TODAY()),"ON HOLD-UPDATE SO PD",IF(AND($B2="ON HOLD",$D2>=TODAY()),"ON HOLD-SO PD OK",IF(AND($A2=TRUE,$I2="BULK ORDER"),"DELIVERY CONFIRMED - BULK ORDER",IF(AND($A2=TRUE,$D2<TODAY()),"DELIVERY CONFIRMED - UPDATE PD/REVIEW ALLOCATION",IF(AND($A2=TRUE,$D2-$H2<=TODAY()),"DELIVERY CONFIRMED - LEAVE ALLOCATED",IFS($H2=0,"NO LEADTIME",$H2="","NO LEADTIME",$E5="LOFT INTERIORS",$D2<=TODAY()+14,"LEAVE ALLOCATED","UNALLOCATE",$D2-$H2>=TODAY(),"POTENTIAL TO UNALLOCATE",$D2-$H2<=TODAY(),"LEAVE ALLOCATED")))))))))))

I want to add to the above formula: if column E matches "Interiors" & column D date is up to 14 days ahead of today, result "Leave Allocated".

IF(AND(E2="INTERIORS",D2<=TODAY()+14),"LEAVE ALLOCATED")​

1726231671479.png


Happy for suggestions to simplify the formula also. Thanks.
 

Attachments

  • 1726231459749.png
    1726231459749.png
    55.2 KB · Views: 4

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Have you tried CHOOSE or SWITCH functions? I am confident they can make your formula more simplified.
 
Upvote 0
Hello,

It is good practise when writing formulas to use indentation. You would spot your mistakes immediately.

At the moment it seems you have one parenthesis too much.

But yes, as already mentionned, you should really rethink the formula from the ground, it's a mess.

Here below your formula correctly indented, notice the "lonely" closing parenthesis at the end (to delete).

Please use SWITCH and IFS more. Also you could segment your function such as you do (for example) a test for the delivery, then a test for leadtimes etc, without nesting them. Just concatenate the results. It's way easier, convenient to maintain, and expandable.

Oh and finally another point, the ISNUMBER(SEARCH({"SV","SERV","SRV","FPKARCH","XS"},$C2)) part is certainly not returning the expected result, as it only checks first value "SV" if you check it with formula evaluation step by step.

Excel Formula: 
=
IF(
  OR(ISNUMBER(SEARCH({"SV","SERV","SRV","FPKARCH","XS"},$C2))),
  "SERVICE",
  IF(
    OR($F2="ONTRAN",$F2="PURCH"),
    "ALLOCATION ONTRAN/PURCH",
    IF(
      AND(
        $D2<TODAY(),
        $I2="",
        $A2="FALSE",
        $J2="",
        $B2<>"ON HOLD"
      ),
      "UPDATE PD",
      IF(
        AND($J2="TRUE",$K2=$G5,$D2>TODAY()),
        "ALLOCATE TO PO",
        IF(
          AND($B2="ON HOLD",$D2<TODAY()),
          "ON HOLD-UPDATE SO PD",
          IF(
            AND($B2="ON HOLD",$D2>=TODAY()),
            "ON HOLD-SO PD OK",
            IF(
              AND($A2=TRUE,$I2="BULK ORDER"),
              "DELIVERY CONFIRMED - BULK ORDER",
              IF(
                AND($A2=TRUE,$D2<TODAY()),
                "DELIVERY CONFIRMED - UPDATE PD/REVIEW ALLOCATION",
                IF(
                  AND($A2=TRUE,$D2-$H2<=TODAY()),
                  "DELIVERY CONFIRMED - LEAVE ALLOCATED",
                  IFS(
                    $H2=0,"NO LEADTIME",
                    $H2="","NO LEADTIME",
                    $E5="LOFT INTERIORS",
                    $D2<=TODAY()+14,"LEAVE ALLOCATED",
                    "UNALLOCATE",
                    $D2-$H2>=TODAY(),"POTENTIAL TO UNALLOCATE",
                    $D2-$H2<=TODAY(),"LEAVE ALLOCATED"
                  )
                )
              )
            )
          )
        )
      )
    )
  )
)
)
 
Upvote 0
I agree in principle with @saboh12617, you have built a lot of logic into a single formula and the chances of it being 100% correct is slim.
eg the formula references G5 and E5 which seems unlikely.
Given the size of it I find it easier with a bit less indentation. Below I have changed the 2 references to row 5 to row 2 and added in your additional criteria (in blue)
You will need to test if it does what its is supposed to do.
Rich (BB code): 
=
IF(OR(ISNUMBER(SEARCH({"SV","SERV","SRV","FPKARCH","XS"},$C2))),"SERVICE",
IF(OR($F2="ONTRAN",$F2="PURCH"),"ALLOCATION ONTRAN/PURCH",
IF(AND($D2<TODAY(),$I2="",$A2="FALSE",$J2="",$B2<>"ON HOLD"),"UPDATE PD",
IF(AND($J2="TRUE",$K2=$G2,$D2>TODAY()),"ALLOCATE TO PO",
IF(AND($B2="ON HOLD",$D2<TODAY()),"ON HOLD-UPDATE SO PD",
IF(AND($B2="ON HOLD",$D2>=TODAY()),"ON HOLD-SO PD OK",
IF(AND($A2=TRUE,$I2="BULK ORDER"),"DELIVERY CONFIRMED - BULK ORDER",
IF(AND($A2=TRUE,$D2<TODAY()),"DELIVERY CONFIRMED - UPDATE PD/REVIEW ALLOCATION",
IF(AND($A2=TRUE,$D2-$H2<=TODAY()),"DELIVERY CONFIRMED - LEAVE ALLOCATED",
IF(AND($E2="INTERIORS",$D2<=TODAY()+14),"LEAVE ALLOCATED",
IFS($H2=0,"NO LEADTIME",
    $H2="","NO LEADTIME",
    $E2="LOFT INTERIORS",
    $D2<=TODAY()+14,"LEAVE ALLOCATED","UNALLOCATE",
    $D2-$H2>=TODAY(),"POTENTIAL TO UNALLOCATE",
    $D2-$H2<=TODAY(),"LEAVE ALLOCATED")))))))))))
 
Upvote 0
There is also something wrong in your IFS formula, correcting the arrangement above it would look like this:
What is the E2 condition and result meant to be ?
Rich (BB code): 
IFS($H2=0, "NO LEADTIME",
    $H2="", "NO LEADTIME",
    $E2="LOFT INTERIORS", $D2<=TODAY()+14,
    "LEAVE ALLOCATED", "UNALLOCATE",
    $D2-$H2>=TODAY(), "POTENTIAL TO UNALLOCATE",
    $D2-$H2<=TODAY(), "LEAVE ALLOCATED")
 
Upvote 0
Looking at the formula I can see a few (potencial) problems.

Rich (BB code): 
=IF(
    OR(ISNUMBER(SEARCH({"SV", "SERV", "SRV", "FPKARCH", "XS"}, $C2))),
    "SERVICE",
    IF(
        OR($F2 = "ONTRAN", $F2 = "PURCH"),
        "ALLOCATION ONTRAN/PURCH",
        IF(
            AND($D2 < TODAY(), $I2 = "", $A2 = "FALSE", $J2 = "", $B2 <> "ON HOLD"),
            "UPDATE PD",
            IF(
                AND($J2 = "TRUE", $K2 = $G2, $D2 > TODAY()),
                "ALLOCATE TO PO",
                IF(
                    AND($B2 = "ON HOLD", $D2 < TODAY()),
                    "ON HOLD-UPDATE SO PD",
                    IF(
                        AND($B2 = "ON HOLD", $D2 >= TODAY()),
                        "ON HOLD-SO PD OK",
                        IF(
                            AND($A2 = TRUE, $I2 = "BULK ORDER"),
                            "DELIVERY CONFIRMED - BULK ORDER",
                            IF(
                                AND($A2 = TRUE, $D2 < TODAY()),
                                "DELIVERY CONFIRMED - UPDATE PD/REVIEW ALLOCATION",
                                IF(
                                    AND($A2 = TRUE, $D2 - $H2 <= TODAY()),
                                    "DELIVERY CONFIRMED - LEAVE ALLOCATED",
                                    IFS(
                                        $H2 = 0, "NO LEADTIME",
                                        $H2 = "", "NO LEADTIME",
                                        $E2 = "LOFT INTERIORS", <value missing>,
                                        $D2 <= TODAY() + 14, "LEAVE ALLOCATED",
                                        <condition missing>, "UNALLOCATE",
                                        $D2 - $H2 >= TODAY(), "POTENTIAL TO UNALLOCATE",
                                        $D2 - $H2 <= TODAY(), "LEAVE ALLOCATED"
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
)

You have some FALSE and TRUE wrapped in quotes, which is not going to work (just delete the quotes).

Then in this line
AND($J2 = "TRUE", $K2 = $G2, $D2 > TODAY()),
you compare Column G with column K, which seem to never be equal, because in column G you have the full name ("Northern Warehouse") and in column K just an abbreviation ("NTH"). But that might just look that way from the limited example data we have.

Then as @Alex Blakenburg pointed out, the IFS part of the formula seems incomplete. I complete with some blue text where i suppose that are missing condition and value.
 
Upvote 0
saboh12617 said:
Hello,

It is good practise when writing formulas to use indentation. You would spot your mistakes immediately.

At the moment it seems you have one parenthesis too much.

But yes, as already mentionned, you should really rethink the formula from the ground, it's a mess.

Here below your formula correctly indented, notice the "lonely" closing parenthesis at the end (to delete).

Please use SWITCH and IFS more. Also you could segment your function such as you do (for example) a test for the delivery, then a test for leadtimes etc, without nesting them. Just concatenate the results. It's way easier, convenient to maintain, and expandable.

Oh and finally another point, the ISNUMBER(SEARCH({"SV","SERV","SRV","FPKARCH","XS"},$C2)) part is certainly not returning the expected result, as it only checks first value "SV" if you check it with formula evaluation step by step.

Excel Formula: 
=
IF(
  OR(ISNUMBER(SEARCH({"SV","SERV","SRV","FPKARCH","XS"},$C2))),
  "SERVICE",
  IF(
    OR($F2="ONTRAN",$F2="PURCH"),
    "ALLOCATION ONTRAN/PURCH",
    IF(
      AND(
        $D2<TODAY(),
        $I2="",
        $A2="FALSE",
        $J2="",
        $B2<>"ON HOLD"
      ),
      "UPDATE PD",
      IF(
        AND($J2="TRUE",$K2=$G5,$D2>TODAY()),
        "ALLOCATE TO PO",
        IF(
          AND($B2="ON HOLD",$D2<TODAY()),
          "ON HOLD-UPDATE SO PD",
          IF(
            AND($B2="ON HOLD",$D2>=TODAY()),
            "ON HOLD-SO PD OK",
            IF(
              AND($A2=TRUE,$I2="BULK ORDER"),
              "DELIVERY CONFIRMED - BULK ORDER",
              IF(
                AND($A2=TRUE,$D2<TODAY()),
                "DELIVERY CONFIRMED - UPDATE PD/REVIEW ALLOCATION",
                IF(
                  AND($A2=TRUE,$D2-$H2<=TODAY()),
                  "DELIVERY CONFIRMED - LEAVE ALLOCATED",
                  IFS(
                    $H2=0,"NO LEADTIME",
                    $H2="","NO LEADTIME",
                    $E5="LOFT INTERIORS",
                    $D2<=TODAY()+14,"LEAVE ALLOCATED",
                    "UNALLOCATE",
                    $D2-$H2>=TODAY(),"POTENTIAL TO UNALLOCATE",
                    $D2-$H2<=TODAY(),"LEAVE ALLOCATED"
                  )
                )
              )
            )
          )
        )
      )
    )
  )
)
)
Click to expand...
Thank
saboh12617 said:
Hello,

It is good practise when writing formulas to use indentation. You would spot your mistakes immediately.

At the moment it seems you have one parenthesis too much.

But yes, as already mentionned, you should really rethink the formula from the ground, it's a mess.

Here below your formula correctly indented, notice the "lonely" closing parenthesis at the end (to delete).

Please use SWITCH and IFS more. Also you could segment your function such as you do (for example) a test for the delivery, then a test for leadtimes etc, without nesting them. Just concatenate the results. It's way easier, convenient to maintain, and expandable.

Oh and finally another point, the ISNUMBER(SEARCH({"SV","SERV","SRV","FPKARCH","XS"},$C2)) part is certainly not returning the expected result, as it only checks first value "SV" if you check it with formula evaluation step by step.

Excel Formula: 
=
IF(
  OR(ISNUMBER(SEARCH({"SV","SERV","SRV","FPKARCH","XS"},$C2))),
  "SERVICE",
  IF(
    OR($F2="ONTRAN",$F2="PURCH"),
    "ALLOCATION ONTRAN/PURCH",
    IF(
      AND(
        $D2<TODAY(),
        $I2="",
        $A2="FALSE",
        $J2="",
        $B2<>"ON HOLD"
      ),
      "UPDATE PD",
      IF(
        AND($J2="TRUE",$K2=$G5,$D2>TODAY()),
        "ALLOCATE TO PO",
        IF(
          AND($B2="ON HOLD",$D2<TODAY()),
          "ON HOLD-UPDATE SO PD",
          IF(
            AND($B2="ON HOLD",$D2>=TODAY()),
            "ON HOLD-SO PD OK",
            IF(
              AND($A2=TRUE,$I2="BULK ORDER"),
              "DELIVERY CONFIRMED - BULK ORDER",
              IF(
                AND($A2=TRUE,$D2<TODAY()),
                "DELIVERY CONFIRMED - UPDATE PD/REVIEW ALLOCATION",
                IF(
                  AND($A2=TRUE,$D2-$H2<=TODAY()),
                  "DELIVERY CONFIRMED - LEAVE ALLOCATED",
                  IFS(
                    $H2=0,"NO LEADTIME",
                    $H2="","NO LEADTIME",
                    $E5="LOFT INTERIORS",
                    $D2<=TODAY()+14,"LEAVE ALLOCATED",
                    "UNALLOCATE",
                    $D2-$H2>=TODAY(),"POTENTIAL TO UNALLOCATE",
                    $D2-$H2<=TODAY(),"LEAVE ALLOCATED"
                  )
                )
              )
            )
          )
        )
      )
    )
  )
)
)
Click to expand...

Thank you for the feedback.
How do you do the indentation?

Where would you suggest I use SWITCH and ISF?

"Please use SWITCH and IFS more. Also you could segment your function such as you do (for example) a test for the delivery, then a test for leadtimes etc, without nesting them. Just concatenate the results. It's way easier, convenient to maintain, and expandable."

I have tested the below and it appears to work for me.
"Oh and finally another point, the ISNUMBER(SEARCH({"SV","SERV","SRV","FPKARCH","XS"},$C2)) part is certainly not returning the expected result, as it only checks first value "SV" if you check it with formula evaluation step by step."
KL
SERV=IF(OR(ISNUMBER(SEARCH({"SV","SERV","SRV","FPKARCH","XS"},$K12))),"SERVICE")
SRV=IF(OR(ISNUMBER(SEARCH({"SV","SERV","SRV","FPKARCH","XS"},$K13))),"SERVICE")
SV=IF(OR(ISNUMBER(SEARCH({"SV","SERV","SRV","FPKARCH","XS"},$K14))),"SERVICE")
FPKARCH=IF(OR(ISNUMBER(SEARCH({"SV","SERV","SRV","FPKARCH","XS"},$K15))),"SERVICE")
XS=IF(OR(ISNUMBER(SEARCH({"SV","SERV","SRV","FPKARCH","XS"},$K16))),"SERVICE")
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Send bulk email attachment error
Replies
3
Views
117
JOHATUP
J
A
  • Question Question
Find tool is very slow
Replies
2
Views
42
alex_345
A
SaraWitch
  • Solved
Count number of cells over 18 but discount dates
Replies
3
Views
343
Fluff
Fluff
C
  • Solved
Formula assistance - identifying full or part counts
Replies
5
Views
291
RobP
R
F
  • Solved
Put a mark on rows that have the same 10 digits but different last one digit
Replies
2
Views
195
feni1388
F

Forum statistics

Threads
1,224,884
Messages
6,181,552
Members
453,052
Latest member
ezzat

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