Formula help to select a specific code based on multiple conditions

[bh24524]

Hello, I have a spreadsheet that I use to track position codes for employees. Currently, there are only 3 position codes: 10305275 which is the code for any 10 hour shift whether daylight or nite, 10305276 which is for any 8 hour shift, whether daylight or nite, and 10305318 which is for any Janitor position which are always 8 hour shifts. My column headers are as follows with a sample shift and I will Bold the relevant headers:

BID #

DAYS OFF

HOURS

DEPT

PAY RULE

RATE

SORT CODE

ACCRUALS

CLOCK

BID #

JOB

CODE

VAC

POSITION CHANGE CODE

100​

S/S

9A-5P - (30 GROC)

C61C

DAY WHSE

25.17

C61C

8

8 OR 9

4000

LIFT

G

B

10305276​

As you see, the tab with these headers contains all the information about a specific shift. The info I am concerned with is accruals(8 or 10 hour position), Job (Janitor and Freezer Janitor are abbreviated Jan and FR Jan), and the Position change code.

In the Position Change code is where currently I have a formula that assigns a code number based on either their accruals or their Job. a 10 hour position will always be the 10305275 number. The 8 hour position gets tricky because as above, it is always 10305276 UNLESS they hold a Janitor position in which case that is the 10305318 number. So the formula to recognize this is as follows:

=IF(COUNT(SEARCH("*JAN",K4))>0,10305318,IF(H4=10,10305275,10305276))

They are now introducing a new code that they want assigned to all nite shift non-janitor employees: 20209586. The way we tell who is nite shift is by the DEPT. column where it has C61C. Those codes are what designate nite shift or day shifts. Nite shift has 3 different dept. codes: C61Q, C65S, and C67G. If it helps any, a nite shift is always an 8 hour shift, not 10 ( I don't know if that might be relevant for what's needed) Again whether they are day or nite, Janitors will always have that 10305318 code.

How can I incorporate this new code criteria into the formula so that I still have my automation in the sheet?


Sorry I was just informed that there IS a possibility they will have a 10 hour nite shift some time down the road. There aren't any yet but if possible, I would like that have that incorporated in the formula just in case. That code is 20209600.

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3	BID #	DAYS OFF	HOURS	DEPT	PAY RULE	RATE	SORT CODE	ACCRUALS	CLOCK	BID #	JOB	CODE	VAC	POSITION CHANGE CODE
4	100	S/S	9A-5P - (30 GROC)	C61C	DAY WHSE	25.17	C61C	8	8 OR 9	4000	LIFT	G	B	10305276
5	100	S/S	9A-5P - (30 GROC)	C61C	DAY WHSE	25.17	C61C	8	8 OR 9	4000	FR Jan	G	B	10305318
6	100	S/S	9A-5P - (30 GROC)	C61C	DAY WHSE	25.17	C61C	10	8 OR 9	4000	LIFT	G	B	10305275
7	100	S/S	9A-5P - (30 GROC)	C61Q	DAY WHSE	25.17	C61C	8	8 OR 9	4000	LIFT	G	B	20209586
8	100	S/S	9A-5P - (30 GROC)	C61Q	DAY WHSE	25.17	C61C	10	8 OR 9	4000	LIFT	G	B	20209600
9
Sheet21

Cell Formulas
Range	Formula
N4:N8	N4	=IF(COUNTIF(K4,"*jan*"),10305318,CHOOSE(ISNUMBER(MATCH(D4,{"C61Q","C65S","C67G"},0))*2+(H4=10)+1,10305276,10305275,20209586,20209600))

If I understand correctly, a janitor is always 10305318. If it's not a janitor, you have 2 factors, 8 or 10 hours, and day or night.

 

[bh24524]

Thanks that worked very well! Exactly what I needed. I definitely would never have been able to figure that formula out. I'm trying to even dissect it to determine how each part is working to understand it but I'm thinking that's going to take a while. If you get some time and might be able to explain it to me, that'd be awesome.

 

[Eric W]

I got a little fancy in an effort to shorten the formula, but it turns out I didn't save much.

The COUNTIF(K4,"*jan*") part checks to see if jan is in the K4 cell, using COUNTIF with wildcards. If it is, we use the janitor number and we're done.

Now we have 2 factors to look at. Is it night?
ISNUMBER(MATCH(D4,{"C61Q","C65S","C67G"},0)) sees if D4 is in that list and returns TRUE or FALSE.

And is it 10 hours?
(H4=10) checks that and returns TRUE or FALSE.

If you use a TRUE/FALSE value in an arithmetic calculation, Excel will consider it to be a 1 or 0. So let's build a table of all the different combinations we can get with those 2 factors.

Book1
	D	E	F
11	Night	10 hours	Night*2+hours+1
12	0	0	1
13	0	1	2
14	1	0	3
15	1	1	4
Sheet21

The third column is the equation I use to convert each combination into a single number. (If you understand binary numbers, it should be evident what's going on. If you don't, you should still see the pattern, and that the results are 1-4.)

Then the final step is the CHOOSE function. Based on the value of the first parameter, which is 1-4, it selects the corresponding result, and gives you the number you want.


But this formula:

=IF(COUNTIF(K4,"*jan*"),10305318,IF(ISNUMBER(MATCH(D4,{"C61Q","C65S","C67G"},0)),IF(H4=10,20209600,20209586),IF(H4=10,10305275,10305276)))

does exactly the same thing without needing binary or CHOOSE. I had to use the H4=10 comparison twice, but even so the formula is only 4 characters longer, and it's easier to follow.

Hope this helps!

 

[bh24524]

I want to color code this to ask questions/explain about each portion of the formula:

=IF(COUNTIF(K4,"*jan*"),10305318,IF(ISNUMBER(MATCH(D4,{"C61Q","C65S","C67G"},0)),IF(H4=10,20209600,20209586),IF(H4=10,10305275,10305276)))

So let me see if I understand this second one correctly by putting it in my own words,
1. =IF(COUNTIF(K4,"*jan*"),10305318, stops the entire formula if the Jan or FR Jan is appearing in column K and gives it the 10305318 number.
2. IF(ISNUMBER(MATCH(D4,{"C61Q","C65S","C67G"},0)),IF(H4=10,20209600,20209586), is first looking in the D column to see if there are any of those 3 codes, if it is, it is then looking at column H to see whether it is 10. If is not a 10 but DOES have one of the three codes from D, it gives it the 20209586 number.
3. IF(H4=10,10305275,10305276))) If D does NOT have one of those codes, it skips the codes in the IF(ISNUMBER(MATCH(D4,{"C61Q","C65S","C67G"},0)),IF(H4=10,20209600,20209586), portion of the formula and looks in H to see whether there is a 10. If there isn't, it assigns 10305276 because there is no Jan position in K, none of the codes in D and column H is not a 10 hour shift.

Would that be correct?

 

[Eric W]

Yep! You got it. You can use the "Evaluate Formula" tool on the Formula tab if you want to see how it works. Try it with some different inputs to exercise the different parts of the formula.