Help with: If and if if if

[brandtberryfourie]

I have a formula that looks like this:
=IF(K185="";"";IF(AND(K185="STATION COMMANDER PERSONNEL";L185="STATION COMMANDER SECRETARY");"VISIBLE POLICING";"SUPPORT SERVICES";IF(K185="CIMAC";"SUPPORT SERVICES";IF(K185="MANAGEMENT INFORMATION CENTRE";"SUPPORT SERVICES";IF(K185="NOT PLACED";L185;IF(K185="DUTY ARRANGEMENTS - PTA NORTH MEMBERS";L185;K185))))))

The first part only should have the AND and the rest works as it worked before I realized I need the AND.

I fixed the first part and it works but the the rest doesn't work or I get an error. Or should maybe have to use OR aswell in the formula

First part: =IF(K2="";"";IF(AND(K2="STATION COMMANDER PERSONNEL";L2="STATION COMMANDER SECRETARY");"SUPPORT SERVICES";"VISIBLE POLICING"))

 

[Joe4]

Welcome to the Board!

Forget about your formula for a minute.
Can you just post the rules you are trying to program to?
What value should be returned based on what conditions?
Please list all possible combinations out with their expected outcomes.

 

[RoryA]

I suspect you need to remove that first ;"SUPPORT SERVICES" as it is terminating the IF function.

 

[brandtberryfourie]

Welcome to the Board!

Forget about your formula for a minute.
Can you just post the rules you are trying to program to?
What value should be returned based on what conditions?
Please list all possible combinations out with their expected outcomes.

Thank you will do so.

 

[Alex Blakenburg]

As Joe mentioned it would be really helpful to see the intended logic.
In your first example you used row 185 and the when you had it working your used row 2.
In row 2 you reversed the order of ;"VISIBLE POLICING";"SUPPORT SERVICES" which adds to the confusion.
On the basis that the row 2 version is working and you are then replace VISIBLE POLICING with a 2nd If statement it would look like this.

Excel lets you put in line feeds and spaces into the formula and I find it helpful when trying to follow the logic.
Hopefully it helps you see where the logic might be going off the rails.

=IF(K2="";"";
         IF(AND(K2="STATION COMMANDER PERSONNEL";L2="STATION COMMANDER SECRETARY");
               "SUPPORT SERVICES";
                IF(K2="CIMAC";
                     "SUPPORT SERVICES";
                     IF(K2="MANAGEMENT INFORMATION CENTRE";
                          "SUPPORT SERVICES";
                           IF(K2="NOT PLACED";
                                L2;
                                IF(K2="DUTY ARRANGEMENTS - PTA NORTH MEMBERS";
                                      L2;
                                      K2))))))

 

[brandtberryfourie]

I suspect you need to remove that first ;"SUPPORT SERVICES" as it is terminating the IF function.

Thank you all this solution helped a little bit I will still go through the rest and amend my my formula if necessary but the idea is working. The end results was to count the members of each section ?? VISIBLE POLICING, ?? DETECTIVES ETC. my problem was that in the pass the secretary was counted under Visible POLICING but is actually SUPPORT SERVICES the previous formula "had to" count it under visible POLICING and on other reports it must be counted as part of Station Commmander Personnel. The station commander is always counted as Visible POLICING but the secretary as support... I know it is weird but at it is doing now what it should.

 

[brandtberryfourie]

As Joe mentioned it would be really helpful to see the intended logic.
In your first example you used row 185 and the when you had it working your used row 2.
In row 2 you reversed the order of ;"VISIBLE POLICING";"SUPPORT SERVICES" which adds to the confusion.
On the basis that the row 2 version is working and you are then replace VISIBLE POLICING with a 2nd If statement it would look like this.

Excel lets you put in line feeds and spaces into the formula and I find it helpful when trying to follow the logic.
Hopefully it helps you see where the logic might be going off the rails.

=IF(K2="";"";
         IF(AND(K2="STATION COMMANDER PERSONNEL";L2="STATION COMMANDER SECRETARY");
               "SUPPORT SERVICES";
                IF(K2="CIMAC";
                     "SUPPORT SERVICES";
                     IF(K2="MANAGEMENT INFORMATION CENTRE";
                          "SUPPORT SERVICES";
                           IF(K2="NOT PLACED";
                                L2;
                                IF(K2="DUTY ARRANGEMENTS - PTA NORTH MEMBERS";
                                      L2;
                                      K2))))))

I know about the row 185 and row 2 thing. I apologize for this. Row 185 is the actual one, the reason for row 2 was that I tried figuring the math out in a new workbook before copying it to the actual workbook.

 

[brandtberryfourie]

As Joe mentioned it would be really helpful to see the intended logic.
In your first example you used row 185 and the when you had it working your used row 2.
In row 2 you reversed the order of ;"VISIBLE POLICING";"SUPPORT SERVICES" which adds to the confusion.
On the basis that the row 2 version is working and you are then replace VISIBLE POLICING with a 2nd If statement it would look like this.

Excel lets you put in line feeds and spaces into the formula and I find it helpful when trying to follow the logic.
Hopefully it helps you see where the logic might be going off the rails.

=IF(K2="";"";
         IF(AND(K2="STATION COMMANDER PERSONNEL";L2="STATION COMMANDER SECRETARY");
               "SUPPORT SERVICES";
                IF(K2="CIMAC";
                     "SUPPORT SERVICES";
                     IF(K2="MANAGEMENT INFORMATION CENTRE";
                          "SUPPORT SERVICES";
                           IF(K2="NOT PLACED";
                                L2;
                                IF(K2="DUTY ARRANGEMENTS - PTA NORTH MEMBERS";
                                      L2;
                                      K2))))))

I am going to check this one also my formula still have 1 small glitch but maybe, just maybe this one of yours will solve that glitch. Thank you for this. Appreciated.