# Wildcard problem in IF function

1. ## 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. ## 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)

3. ## Re: Wildcard problem in IF function

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

Formula:
`Please Login or Register  to view this content.`

But, you can make it simpler if you use OR

Formula:
`Please Login or Register  to view this content.`

Regards, TMS

4. ## 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:
`Please Login or Register  to view this content.`

Regards, TMS

5. ## 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",...........)

6. ## 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. ## 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. ## Re: Wildcard problem in IF function

Originally Posted by TMS
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. ## 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

#### Thread Information

##### Users Browsing this Thread

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

#### 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