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

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.

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.