Results 1 to 4 of 4

Complicated TRUE/FALSE Formula

Threaded View

  1. #1
    Registered User
    Join Date
    01-02-2013
    Location
    Toronto, ON, Canada
    MS-Off Ver
    Excel 2010
    Posts
    21

    Complicated TRUE/FALSE Formula

    I'm working with a monthly schedule for a team of people that work different shifts and a certain number of each shift needs to be met. So I'm trying to make a bunch of different formulas to say TRUE or FALSE based on if the shift criteria is met for that day. Each formula calculates for a different criteria so I know what shifts i need more of. Having more than the minimum number of shifts is fine.

    One of the criteria is that there needs to be at least one person working from 9-5 or 9:30-5:30 or 10-6 and least one other person working one of the shifts the first person is not and that would return TRUE. If there are two people working the same shift but no one else working either of the other shifts and that would return FALSE. It is fine if there are more than 1 people working any of those three shifts as long as one of the other shifts is also being worked.

    FYI: the different people working are categorized into different groups based on their strengths and there is a blank line between each of the groups.

    Example of Data:
    A B
    1 Employee Name: Aug 1
    2 Alex 8-4
    3 Bobby 9:30-5:30
    4
    5 Chris 11-7
    6 Dakota 10-6
    7 Ellis 12-8
    8
    9 List of TRUE/FALSE statements

    So this example would have TRUE for the formula but there are many more people and looking at 20+ cells for each day is very time consuming. What would a formula like that be though?

    I've figured out a formula that will tell me how many of each of those three shifts are being worked but it is long and messy:
    =IF(COUNTIF(B$3:B$26,"9-5")>=1,"9","")&IF(COUNTIF(B$3:B$26,"9-5")>=2,"x"&COUNTIF(B$3:B$26,"9-5"),"")&IF(AND(COUNTIF(B$3:B$26,"9-5")>=1,OR(COUNTIF(B$3:B$26,"9:30-5:30")>=1,COUNTIF(B$3:B$26,"10-6")>=1)),", ","")&IF(COUNTIF(B$3:B$26,"9:30-5:30")>=1,"9:30","")&IF(COUNTIF(B$3:B$26,"9:30-5:30")>=2,"x"&COUNTIF(B$3:B$26,"9:30-5:30"),"")&IF(AND(COUNTIF(B$3:B$26,"9-5")>=1,COUNTIF(B$3:B$26,"9:30-5:30")>=1,COUNTIF(B$3:B$26,"10-6")>=1),", ","")&IF(COUNTIF(B$3:B$26,"10-6")>=1,"10","")&IF(COUNTIF(B$3:B$26,"10-6")>=2,"x"&COUNTIF(B$3:B$26,"10-6"),"")
    Last edited by bpiereder; 07-23-2013 at 01:13 PM. Reason: Solved

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Display true false with IF formula
    By pcoutlaw in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-22-2012, 09:48 AM
  2. Formula to Show True False
    By Ortz in forum Excel General
    Replies: 2
    Last Post: 02-16-2012, 12:02 PM
  3. Excel 2007 : If formula using True and False
    By casdaq in forum Excel General
    Replies: 2
    Last Post: 05-06-2011, 03:59 PM
  4. Formula for returning True (1) or False(0)
    By wish2excel in forum Excel General
    Replies: 5
    Last Post: 10-11-2010, 04:14 AM
  5. Replies: 3
    Last Post: 03-15-2006, 11:00 AM

Tags for this Thread

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