SharePoint Calculated Formula

Question

I have 3 columns...

"CEO" + "President" + "Vice President"

The 3 columns are using Choice field YES or NO (I tried and could also use drop down column Yes/No). The CEO or President or VP will go to their respective column, and select Yes or No when they want to approve a document.

I have a fourth is a calculated column which is Status, and I would like when the 3 columns all chose Yes, that the 4th column "Status"

Right now, I used the following formula

=IF([Yes/No - D]="Yes","Approve","Pending")

This allows me to choose Yes or No and it change to Pending or Approved. It is working. I would like to see if any one can help with 2 things, I have checked dozens of sites and forums.

I'd like to first see if it's possible to add a 3rd category such as Not Approved, so the column will switch to Not Approved if they click No, Approved it they click Yes, and Nothing will have Pending...

The second thing I would like is to have all 3 columns (CEO, P, VP) use the same formula but add them together, so only if all 3 columns are YES, then the calculated column I set up Status, will show Approved, otherwise, it will show pending, or if they all choose NO, it will show Not Approved.

I know this is possible, just having trouble combining multiple IF columns. I can clarify if needed

Answer 1

You can thinks of something in this line:

IF CEO IS NOT NULL
    IF CEO = Yes
        IF VP IS NOT NULL
            IF VP = Yes
                IF President IS NOT NULL
                    If President = Yes
                        Return 'Approved'
                    ELSE
                        -- President = NO
                        Return 'Not Approved'
                ELSE
                    -- President IS NULL
                    Return 'Pending'
            ELSE
                -- VP = NO
                Return 'Not Approved'
        ELSE
            -- VP NULL
            Return 'Pending'
    ELSE
      -- CEO = No
      Return 'Not Approved'
ELSE
    -- CEO NULL
    Return 'Pending'

Then, build you IF statement accordingly. Check the IF function details in this link.

Basically, it is, IF(logical_test,value_if_true,value_if_false) and for value_if_true you will need insert the inner IF statement as per the logic above.

=IF(CEO !="", IF([CEO] = "Yes", IF([VP] !="", IF([VP] = "Yes", IF([President] !="", ..., "Pending"), "Not Approved"), "Pending"), "Not Approved"),"Pending")