+ Reply to Thread
Results 1 to 5 of 5

Multiple IF statements in a cell

  1. #1
    Registered User
    Join Date
    01-30-2015
    Location
    Manila, Philippines
    MS-Off Ver
    MS Office 2013
    Posts
    5

    Multiple IF statements in a cell

    Hi there ! I'm trying to create a template wherein the employees attendance can be monitored.

    What I want to do is to have a formula in column S so that if there is a time value in columns M and N, and it equals the value on columns K and L, respectively, then value on column S, shows as "Present".

    Then if there is a time value in columns K and L but there are no entries in columns M and N, then column S shows as "Absent".

    If columns K and L shows as "VL" and columns M and N are blank, then column S shows as "VL".

    If (N:N-M:M)<(L:L-K:K), then column S shows as "Late".

    I hope someone can understand what I am trying to do.

    I have attached the file so that you can check it.
    Attached Files Attached Files
    Last edited by IceAngel; 02-02-2015 at 01:16 PM.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,065

    Re: Multiple IF statements in a cell

    Try

    =IF(AND(K1<>0,L1<>0,LEN(M1&N1)=0),"Absent",IF(AND(K1&L1="VLVL",LEN(M1&N1)=0),"VL",IF(N1-M1<L1-K1,"Late","OTHER")))
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,481

    Re: Multiple IF statements in a cell

    Try:

    =IFERROR(IF(L2-K2-(N2-M2)=0,"Present",IF(N2-M2=0,"Absent",IF(L2-K2-(N2-M2)>0,"Late",""))),"VL")
    Quang PT

  4. #4
    Registered User
    Join Date
    01-30-2015
    Location
    Manila, Philippines
    MS-Off Ver
    MS Office 2013
    Posts
    5

    Re: Multiple IF statements in a cell

    Thanks for your response. I tried it, but when the value on K and L shows as "VL" and M and N as blank, S still shows absent, which is my major problem.

  5. #5
    Registered User
    Join Date
    01-30-2015
    Location
    Manila, Philippines
    MS-Off Ver
    MS Office 2013
    Posts
    5

    Re: Multiple IF statements in a cell

    Thanks for your help Bebo021999. It works fine with the template. Just a little fix on the data that I have. Thanks again for the help. Issue has been answered satisfactorily and thread will be closed.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Multiple IF statements within one cell
    By Talamon in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-13-2014, 06:45 PM
  2. [SOLVED] Multiple If's and And statements in one cell?
    By Dagoom in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-11-2013, 05:14 AM
  3. Multiple if statements in one cell
    By JSpeedy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-15-2013, 04:18 AM
  4. [SOLVED] Excel 2007 : Multiple IF statements in one cell
    By StephenNesbit in forum Excel General
    Replies: 9
    Last Post: 06-06-2012, 04:52 PM
  5. Using Multiple If statements in a cell
    By Guygettinby in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-14-2011, 08:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1