Formula help to select a specific code based on multiple conditions

bh24524

Board Regular
Joined
Dec 11, 2008
Messages
224
Office Version
  1. 2013
  2. 2007
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 OFFHOURSDEPTPAY RULERATESORT CODEACCRUALSCLOCKBID #JOBCODEVACPOSITION CHANGE CODE
100​
S/S9A-5P - (30 GROC)C61CDAY WHSE25.17C61C88 OR 94000LIFTGB
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:

Excel Formula:
=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.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,748
Try:

Book1
ABCDEFGHIJKLMN
1
2
3BID #DAYS OFFHOURSDEPTPAY RULERATESORT CODEACCRUALSCLOCKBID #JOBCODEVACPOSITION CHANGE CODE
4100S/S9A-5P - (30 GROC)C61CDAY WHSE25.17C61C88 OR 94000LIFTGB10305276
5100S/S9A-5P - (30 GROC)C61CDAY WHSE25.17C61C88 OR 94000FR JanGB10305318
6100S/S9A-5P - (30 GROC)C61CDAY WHSE25.17C61C108 OR 94000LIFTGB10305275
7100S/S9A-5P - (30 GROC)C61QDAY WHSE25.17C61C88 OR 94000LIFTGB20209586
8100S/S9A-5P - (30 GROC)C61QDAY WHSE25.17C61C108 OR 94000LIFTGB20209600
9
Sheet21
Cell Formulas
RangeFormula
N4:N8N4=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

Board Regular
Joined
Dec 11, 2008
Messages
224
Office Version
  1. 2013
  2. 2007
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,748
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
DEF
11Night10 hoursNight*2+hours+1
12001
13012
14103
15114
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! :biggrin:
 

bh24524

Board Regular
Joined
Dec 11, 2008
Messages
224
Office Version
  1. 2013
  2. 2007
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,748
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,205
Messages
5,623,363
Members
415,969
Latest member
Rey99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top