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 time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 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

SaraWitch
  • Solved
Count number of cells over 18 but discount dates
Replies
3
Views
336
Fluff
Fluff
C
  • Solved
Formula assistance - identifying full or part counts
Replies
5
Views
266
RobP
R
F
  • Solved
Put a mark on rows that have the same 10 digits but different last one digit
Replies
2
Views
188
feni1388
F
T
  • Solved
VBA generates word docs, request to amend code to include description of deadlines/tasks due
Replies
4
Views
527
ttp921
T
SFCChase
  • Question Question
Faulty TIME calculation???
Replies
3
Views
624
etaf
etaf

Forum statistics

Threads
1,223,948
Messages
6,175,571
Members
452,652
Latest member
eduedu

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