+ Reply to Thread
Results 1 to 9 of 9

Wildcard problem in IF function

  1. #1
    Registered User
    Join Date
    04-17-2014
    Location
    Bristol
    MS-Off Ver
    Excel 2007
    Posts
    3

    Question Wildcard problem in IF function

    Hello everyone.

    I’m having a problem using a wildcard in an IF formula. On the face of it should be quite simple but for the life of me I can’t get it to work and I don’t know why. I’ve searched but can find nothing that seems to answer my question so I’m throwing myself at the mercy of the collective wisdom of this forum.

    I’m using a workbook in Excel 2007 to produce duty rotas. The shifts are entered into the sheet, "Display" and are all formatted as text. In the sheet “CalcSheet”, the ranges F3:L15 & F20:L35 contain nested IF functions using logical tests based on those shifts in the corresponding cells in “Display”. I wrote the functions in the first cell of each range and auto filled the remainder. Mostly the functions behave as expected and return the duration of each shift but when I try to use an asterisk as a wildcard (where an entry may end with a variable text string) it returns False when it shouldn’t.

    RotaTmpltDev.xlsx

    In the example I've uploaded, I have included some sample shift data in "Display". H11 has the text 9-5 (T am). The corresponding IF function nested in 6th place, IF(Display!H11="9-5*",7.5,etc) should return a value of 7.5 but instead returns False.

    I’m hoping this problem is fairly simple to resolve and those who may be good enough to help will already have sufficient information. However, for the sake of clarity and if required, I have described the work book in detail below:

    There are 3 sheets named “Display”, “CalcKeys” and “CalcSheet”.

    “CalcKeys” contains values that are potentially variable for the same data type ie shift hours.

    “CalcSheet” is used to make calculations based on shift data entered into Display.

    “Display” contains two tables, the first having certain fixed data: days & dates across the “header” rows, then names, grade and contracted hours in the first few columns. Shifts to be worked are entered into the blank cells on each row. A second table gives details of numbers of Bank Staff required for each shift with space to write on as this sheet is also used for a 2 page print out.

    The first table in “Display” is split into two staff groups. The numbers of each staff group working each shift are returned below their respective groups using COUNTIF. Below that, those subtotals are then totalled using simple cell additions eg. =F16+F36. Each Early, Late & Night shift has a desired staff mix and minimum complement, that complement currently being 4, 4 & 3 respectively. Where extra cover is required, these are returned by staff group for each shift using IF functions together with conditional formatting to highlight deficits. These IF functions may appear more complicated because some of the values used in some of the logical tests are on the sheet CalcKeys.

    There are three main shifts, Early, Late, Night & 9-5. Convention dictates these shifts are entered as “E”, “L”, “N” & “9-5” respectively (obvious to read, if somewhat unoriginal). Other codes such as “A”, “T”, “S” etc are used for Annual Leave, Training and Sick, for example. The senior person on each shift is indicated with an asterisk and occasionally there is a combination of codes such as “9-5 (T am)” or “9-5 (T pm)” which would mean 9-5 with Training in the morning or afternoon respectively. These cells are all formatted as text so I assume whatever is entered is treated as a text string.

    At the end of each row, the difference between each person’s contracted weekly hours and their actual hours is returned, formatted as hours and minutes. These values are returned from a calculation made in “CalcSheet” as follows. Each cell corresponding to a shift (ranges F3:L15 & F20:L35) contains a nested IF function which returns a value of “False” (for a day off) or the hours to be worked (accounting for compulsory breaks). SUM is used to give a weekly total and the contracted hours (column D) is subtracted.

    If anyone can help with this I would really be grateful. Thanks

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,701

    Re: Wildcard problem in IF function

    you cannot use * within an IF statement

    try changing to

    =IF(COUNT(SEARCH("9-5*",Display!H11))>0,7.5,etc)
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,499

    Re: Wildcard problem in IF function

    Building on etaf's solution and adding a final FALSE condition, you get:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    But, you can make it simpler if you use OR

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,499

    Re: Wildcard problem in IF function

    Actually, thinking about it, checking for "E" and "E*" is redundant, you just need to check for "E*".

    Which makes the formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Wildcard problem in IF function

    The specific issue with wildcards is that they are not recognised when doing a comparison with =

    If you use a formula like

    =A1="x*"

    that is only TRUE if A1 contains literally x*

    When using SEARCH function you don't normally need to include asterisks because SEARCH always looks for the substring within other text, so these two formulas should always give you the same result

    =SEARCH("L*",Display!F3)

    =SEARCH("L",Display!F3)

    ....although if you want to determine whether the cell value starts with "L", neither will do that, they will simply return numbers if "L" is contained in the referenced cell.

    For "starts with" using LEFT function would be more accurate, e.g.

    =IF(LEFT(Display!F3,1)="L",...........)
    Audere est facere

  6. #6
    Registered User
    Join Date
    04-17-2014
    Location
    Bristol
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Wildcard problem in IF function

    Thank for your help everyone.

    I am working close to the limits of my competence here and I think I need to experiment a little to get my head around how some of these functions apply to what I'm doing. What I have done is copied TMS's simplified formula and I now have exactly the result I was looking for. So another big thank you.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,499

    Re: Wildcard problem in IF function

    You're welcome. Thanks for the rep.


    While I was able to simplify the formula for you, I probably couldn't have helped without etaf's function ... and it is helpful to have the clarification from DLL which means the formula could actually be a little shorter.


    Regards, TMS

  8. #8
    Registered User
    Join Date
    04-17-2014
    Location
    Bristol
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Wildcard problem in IF function

    Quote Originally Posted by TMS View Post
    While I was able to simplify the formula for you, I probably couldn't have helped without etaf's function ... and it is helpful to have the clarification from DLL which means the formula could actually be a little shorter.
    Indeed. As I said, I am close to the limit of my excel competence here, in as much as I only really have a basic understanding of some of the more common functions (and evidently that knowledge has holes in it ). I'm a reasonably logical thinker and I've got how the SEARCH function works but I would never have thought of nesting it with SEARCH a la etaf and I still havent quite got my head around how DLL's LEFT function works.

    My gratitude extends to all three of you.

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,499

    Re: Wildcard problem in IF function

    The LEFT function simply returns a number of characters from the left ( ) of a string. So, LEFT(Display!F3,1) simply gives you a single character, that is, the first character in the range Display!F3. Actually, a much simpler way of determining if your string starts with whatever character you are looking for.

    Sometimes, we have to move from the "simple not working" through the "reasonably complex working" before arriving at the "simple working".

    A bit like going from "unconscious incompetence", to "conscious incompetence", to "conscious competence" and finally arriving at "unconscious competence". In other words, from you're rubbish but you don't realise it through to becoming an expert and taking it for granted.


    Regards, TMS

+ 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. Wildcard filter problem
    By love0126 in forum Excel General
    Replies: 3
    Last Post: 06-06-2013, 07:57 PM
  2. [SOLVED] Asterisk Wildcard in IF statement problem
    By shaft in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-18-2012, 08:52 PM
  3. Wildcard COUNTIF problem.
    By shawnstorm in forum Excel General
    Replies: 6
    Last Post: 01-21-2010, 04:56 AM
  4. string parsing w/wildcard problem
    By neverends in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-08-2006, 01:09 PM
  5. Wildcard Problem....
    By JackH1976 in forum Excel General
    Replies: 5
    Last Post: 12-27-2005, 11:45 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