Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple function is it even possible
I'm working on a schedule that needs to count N or D and assign it a value of
12 the chart i'm working with looks something like this sun mon tues wed thurs fri sat mary vac vac vac vac vac va d va-n tyler off off n n d d off john d d d d off off d joe n n off off n n n what function can i use to count how many times a person works "n" or "va d" or "va-n" or "d" and assign a value of 12 for each of those characters but a value of ZERO for off or vac? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple function is it even possible
How about this in I2 and drag down
=(COUNTIF(B2:H2,"D")+COUNTIF(B2:H2,"N"))*12 You didn't say what was to happen for - va d or va-n Mike "Kris M" wrote: I'm working on a schedule that needs to count N or D and assign it a value of 12 the chart i'm working with looks something like this sun mon tues wed thurs fri sat mary vac vac vac vac vac va d va-n tyler off off n n d d off john d d d d off off d joe n n off off n n n what function can i use to count how many times a person works "n" or "va d" or "va-n" or "d" and assign a value of 12 for each of those characters but a value of ZERO for off or vac? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple function is it even possible
actually i need "va-d" and "va-n" to also have a value of 12 -- "vac" and
"off" obviously have a zero value. "Mike H" wrote: How about this in I2 and drag down =(COUNTIF(B2:H2,"D")+COUNTIF(B2:H2,"N"))*12 You didn't say what was to happen for - va d or va-n Mike "Kris M" wrote: I'm working on a schedule that needs to count N or D and assign it a value of 12 the chart i'm working with looks something like this sun mon tues wed thurs fri sat mary vac vac vac vac vac va d va-n tyler off off n n d d off john d d d d off off d joe n n off off n n n what function can i use to count how many times a person works "n" or "va d" or "va-n" or "d" and assign a value of 12 for each of those characters but a value of ZERO for off or vac? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple function is it even possible
I GOT IT TO WORK THANKS SO MUCH -- I JUST EXPANDED ON WHAT YOU SHOWED ME BUT
NOW ANOTHER FUNCTION QUESTION PLEASE? if we work a standard 48 hour work week and work overtime by 24 hours, what kind of function can you toss my way to show that? what I mean is in a table i need the first column to read the total hours worked (example is 12, 72,60,72) in the second column i need to show the hours that went OVER the standard hours of 48 (doing the math the hard way -- 0,24,12,24) i just confused myself so no biggie if i lost you somewhere. "Kris M" wrote: actually i need "va-d" and "va-n" to also have a value of 12 -- "vac" and "off" obviously have a zero value. "Mike H" wrote: How about this in I2 and drag down =(COUNTIF(B2:H2,"D")+COUNTIF(B2:H2,"N"))*12 You didn't say what was to happen for - va d or va-n Mike "Kris M" wrote: I'm working on a schedule that needs to count N or D and assign it a value of 12 the chart i'm working with looks something like this sun mon tues wed thurs fri sat mary vac vac vac vac vac va d va-n tyler off off n n d d off john d d d d off off d joe n n off off n n n what function can i use to count how many times a person works "n" or "va d" or "va-n" or "d" and assign a value of 12 for each of those characters but a value of ZERO for off or vac? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple function is it even possible
Hi,
It's only 'obvious' to you what you want not us. Try this =(COUNTIF(B2:H2,"D")+COUNTIF(B2:H2,"N")+COUNTIF(B2 :H2,"VA D")+COUNTIF(B2:H2,"VA-N"))*12 Mind the line wrap it's all on one line. Note that in your codes va d & va-n the latter has a hyphen and this is replicated in the formula. Mike "Kris M" wrote: actually i need "va-d" and "va-n" to also have a value of 12 -- "vac" and "off" obviously have a zero value. "Mike H" wrote: How about this in I2 and drag down =(COUNTIF(B2:H2,"D")+COUNTIF(B2:H2,"N"))*12 You didn't say what was to happen for - va d or va-n Mike "Kris M" wrote: I'm working on a schedule that needs to count N or D and assign it a value of 12 the chart i'm working with looks something like this sun mon tues wed thurs fri sat mary vac vac vac vac vac va d va-n tyler off off n n d d off john d d d d off off d joe n n off off n n n what function can i use to count how many times a person works "n" or "va d" or "va-n" or "d" and assign a value of 12 for each of those characters but a value of ZERO for off or vac? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple function is it even possible
Hi,
If I understand correctly you want to calculate basic hours up to 48 and overtime hours for each person. Basic hours in I2 and drag down =MIN(48,(COUNTIF(B2:H2,"D")+COUNTIF(B2:H2,"N")+COU NTIF(B2:H2,"VA D")+COUNTIF(B2:H2,"VA-N"))*12) Overtime hours in J2 and drag down =MAX(0,((COUNTIF(B2:H2,"D")+COUNTIF(B2:H2,"N")+COU NTIF(B2:H2,"VA D")+COUNTIF(B2:H2,"VA-N"))*12)-48) Mike "Kris M" wrote: I GOT IT TO WORK THANKS SO MUCH -- I JUST EXPANDED ON WHAT YOU SHOWED ME BUT NOW ANOTHER FUNCTION QUESTION PLEASE? if we work a standard 48 hour work week and work overtime by 24 hours, what kind of function can you toss my way to show that? what I mean is in a table i need the first column to read the total hours worked (example is 12, 72,60,72) in the second column i need to show the hours that went OVER the standard hours of 48 (doing the math the hard way -- 0,24,12,24) i just confused myself so no biggie if i lost you somewhere. "Kris M" wrote: actually i need "va-d" and "va-n" to also have a value of 12 -- "vac" and "off" obviously have a zero value. "Mike H" wrote: How about this in I2 and drag down =(COUNTIF(B2:H2,"D")+COUNTIF(B2:H2,"N"))*12 You didn't say what was to happen for - va d or va-n Mike "Kris M" wrote: I'm working on a schedule that needs to count N or D and assign it a value of 12 the chart i'm working with looks something like this sun mon tues wed thurs fri sat mary vac vac vac vac vac va d va-n tyler off off n n d d off john d d d d off off d joe n n off off n n n what function can i use to count how many times a person works "n" or "va d" or "va-n" or "d" and assign a value of 12 for each of those characters but a value of ZERO for off or vac? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple function is it even possible
Simplify version:
=SUM(COUNTIF(B2:H2,{"va d","va-n","d","n"}))*12 "Mike H" wrote: Hi, It's only 'obvious' to you what you want not us. Try this =(COUNTIF(B2:H2,"D")+COUNTIF(B2:H2,"N")+COUNTIF(B2 :H2,"VA D")+COUNTIF(B2:H2,"VA-N"))*12 Mind the line wrap it's all on one line. Note that in your codes va d & va-n the latter has a hyphen and this is replicated in the formula. Mike "Kris M" wrote: actually i need "va-d" and "va-n" to also have a value of 12 -- "vac" and "off" obviously have a zero value. "Mike H" wrote: How about this in I2 and drag down =(COUNTIF(B2:H2,"D")+COUNTIF(B2:H2,"N"))*12 You didn't say what was to happen for - va d or va-n Mike "Kris M" wrote: I'm working on a schedule that needs to count N or D and assign it a value of 12 the chart i'm working with looks something like this sun mon tues wed thurs fri sat mary vac vac vac vac vac va d va-n tyler off off n n d d off john d d d d off off d joe n n off off n n n what function can i use to count how many times a person works "n" or "va d" or "va-n" or "d" and assign a value of 12 for each of those characters but a value of ZERO for off or vac? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple function is it even possible
Hi,
Here is a shorter version: =SUM(COUNTIF(B2:H2,{"Va??";"d";"n"})*12) If you already have the above calculation in cell I2, then Regular hours in J2 would be =MIN(I2,48) and Overtime hours in cell K2 would be =MAX(I2-48,0) If this helps please click the Yes button Cheers, Shane Devenshire "Kris M" wrote: I GOT IT TO WORK THANKS SO MUCH -- I JUST EXPANDED ON WHAT YOU SHOWED ME BUT NOW ANOTHER FUNCTION QUESTION PLEASE? if we work a standard 48 hour work week and work overtime by 24 hours, what kind of function can you toss my way to show that? what I mean is in a table i need the first column to read the total hours worked (example is 12, 72,60,72) in the second column i need to show the hours that went OVER the standard hours of 48 (doing the math the hard way -- 0,24,12,24) i just confused myself so no biggie if i lost you somewhere. "Kris M" wrote: actually i need "va-d" and "va-n" to also have a value of 12 -- "vac" and "off" obviously have a zero value. "Mike H" wrote: How about this in I2 and drag down =(COUNTIF(B2:H2,"D")+COUNTIF(B2:H2,"N"))*12 You didn't say what was to happen for - va d or va-n Mike "Kris M" wrote: I'm working on a schedule that needs to count N or D and assign it a value of 12 the chart i'm working with looks something like this sun mon tues wed thurs fri sat mary vac vac vac vac vac va d va-n tyler off off n n d d off john d d d d off off d joe n n off off n n n what function can i use to count how many times a person works "n" or "va d" or "va-n" or "d" and assign a value of 12 for each of those characters but a value of ZERO for off or vac? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple IF Function | Excel Discussion (Misc queries) | |||
Multiple IF Function | Excel Worksheet Functions | |||
Multiple results from multiple criteria using IF function | Excel Discussion (Misc queries) | |||
Multiple Sheet, Multiple Criteria Look-Up Function Help | Excel Worksheet Functions | |||
Multiple OR function | Excel Worksheet Functions |