|
Help
Good morning all. I'm currently working on a attendance tracker, I have a
formula that will give us either half or full occurrence. Before we didnt need to track 0 but now we have. I tried to change the formula to give us a full occurrence (1) but Im getting an error. Can anyone help me with this€¦ Thanks =(COUNTIF(L9:BF9,"0")-COUNTIF(L9:BF9,"=5"))+((COUNTIF(L9:BF9,"=5")-COUNTIF(L9:BF9,"=8"))/2) -- Regards YM |
Help
Yitzhack,
Could you give me the example data? L9:BF9 Secondly, what is it that you would like to happen? Would you just like to include 0 into the formula? You could change that with "=0" in the first COUNTIF. I could be mistaken. In all essence, what would you like the formula to return? -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Yitzhack" wrote: Good morning all. I'm currently working on a attendance tracker, I have a formula that will give us either half or full occurrence. Before we didnt need to track 0 but now we have. I tried to change the formula to give us a full occurrence (1) but Im getting an error. Can anyone help me with this€¦ Thanks =(COUNTIF(L9:BF9,"0")-COUNTIF(L9:BF9,"=5"))+((COUNTIF(L9:BF9,"=5")-COUNTIF(L9:BF9,"=8"))/2) -- Regards YM |
Help
what it needds to return is if 0 = 1 the current formula returns if then 5
= .5. BAsicaly what i need is if L9 is 0 = 1 if is than 0 but< than5 = 1 if than 5 but <than 8 = .05. If you miss up to 3 hours out of 8 = .05 if you miss full day = 1 If you miss more than 3 = 1 -- Regards YM "Thomas [PBD]" wrote: Yitzhack, Could you give me the example data? L9:BF9 Secondly, what is it that you would like to happen? Would you just like to include 0 into the formula? You could change that with "=0" in the first COUNTIF. I could be mistaken. In all essence, what would you like the formula to return? -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Yitzhack" wrote: Good morning all. I'm currently working on a attendance tracker, I have a formula that will give us either half or full occurrence. Before we didnt need to track 0 but now we have. I tried to change the formula to give us a full occurrence (1) but Im getting an error. Can anyone help me with this€¦ Thanks =(COUNTIF(L9:BF9,"0")-COUNTIF(L9:BF9,"=5"))+((COUNTIF(L9:BF9,"=5")-COUNTIF(L9:BF9,"=8"))/2) -- Regards YM |
Help
Also, I'm not sure if this is even posible but we have some "part-timers"
that work different hours, mon and wed 11 to 5 (6), and tues, thurs, and fri 8 to 5. (8) mon and wed 8 to 5 (8), tues and thurs 2 to 5 (3), and fri 1 to 5 (4) -- Regards YM "Yitzhack" wrote: what it needds to return is if 0 = 1 the current formula returns if then 5 = .5. BAsicaly what i need is if L9 is 0 = 1 if is than 0 but< than5 = 1 if than 5 but <than 8 = .05. If you miss up to 3 hours out of 8 = .05 if you miss full day = 1 If you miss more than 3 = 1 -- Regards YM "Thomas [PBD]" wrote: Yitzhack, Could you give me the example data? L9:BF9 Secondly, what is it that you would like to happen? Would you just like to include 0 into the formula? You could change that with "=0" in the first COUNTIF. I could be mistaken. In all essence, what would you like the formula to return? -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Yitzhack" wrote: Good morning all. I'm currently working on a attendance tracker, I have a formula that will give us either half or full occurrence. Before we didnt need to track 0 but now we have. I tried to change the formula to give us a full occurrence (1) but Im getting an error. Can anyone help me with this€¦ Thanks =(COUNTIF(L9:BF9,"0")-COUNTIF(L9:BF9,"=5"))+((COUNTIF(L9:BF9,"=5")-COUNTIF(L9:BF9,"=8"))/2) -- Regards YM |
Help
Mon Tue Wed Thu Fri
8 8 2 0 0 =(COUNTIF(L11:BF11,"0")-COUNTIF(L11:BF11,"=5"))+((COUNTIF(L11:BF11,"=5")-COUNTIF(L11:BF11,"=8"))/2) The formula returs 1 But now i need to return 0 Regards YM "Yitzhack" wrote: what it needds to return is if 0 = 1 the current formula returns if then 5 = .5. BAsicaly what i need is if L9 is 0 = 1 if is than 0 but< than5 = 1 if than 5 but <than 8 = .05. If you miss up to 3 hours out of 8 = .05 if you miss full day = 1 If you miss more than 3 = 1 -- Regards YM "Thomas [PBD]" wrote: Yitzhack, Could you give me the example data? L9:BF9 Secondly, what is it that you would like to happen? Would you just like to include 0 into the formula? You could change that with "=0" in the first COUNTIF. I could be mistaken. In all essence, what would you like the formula to return? -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Yitzhack" wrote: Good morning all. I'm currently working on a attendance tracker, I have a formula that will give us either half or full occurrence. Before we didnt need to track 0 but now we have. I tried to change the formula to give us a full occurrence (1) but Im getting an error. Can anyone help me with this€¦ Thanks =(COUNTIF(L9:BF9,"0")-COUNTIF(L9:BF9,"=5"))+((COUNTIF(L9:BF9,"=5")-COUNTIF(L9:BF9,"=8"))/2) -- Regards YM |
Help
Yitzhack,
Ok, seems like I have what you need. I think. Basic Parameters: <cell=0 then 1 <cell0 and <5 then 1 <cell=5 and <8 then .5 <cell=8 then 0 =SUMPRODUCT(--((L9:BF9)=0),--((L9:BF9)<""))+SUMPRODUCT(--((L9:BF9)0),--((L9:BF9<5)))+(SUMPRODUCT(--((L9:BF9)=5),--((L9:BF9)<8))*0.5) -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Yitzhack" wrote: what it needds to return is if 0 = 1 the current formula returns if then 5 = .5. BAsicaly what i need is if L9 is 0 = 1 if is than 0 but< than5 = 1 if than 5 but <than 8 = .05. If you miss up to 3 hours out of 8 = .05 if you miss full day = 1 If you miss more than 3 = 1 -- Regards YM "Thomas [PBD]" wrote: Yitzhack, Could you give me the example data? L9:BF9 Secondly, what is it that you would like to happen? Would you just like to include 0 into the formula? You could change that with "=0" in the first COUNTIF. I could be mistaken. In all essence, what would you like the formula to return? -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Yitzhack" wrote: Good morning all. I'm currently working on a attendance tracker, I have a formula that will give us either half or full occurrence. Before we didnt need to track 0 but now we have. I tried to change the formula to give us a full occurrence (1) but Im getting an error. Can anyone help me with this€¦ Thanks =(COUNTIF(L9:BF9,"0")-COUNTIF(L9:BF9,"=5"))+((COUNTIF(L9:BF9,"=5")-COUNTIF(L9:BF9,"=8"))/2) -- Regards YM |
Help
Thomas, Thank you so much. This is definetely what i needed.. Do you think
that the formula for the "part-timers" is something doable? Thanks again Regards YM "Thomas [PBD]" wrote: Yitzhack, Ok, seems like I have what you need. I think. Basic Parameters: <cell=0 then 1 <cell0 and <5 then 1 <cell=5 and <8 then .5 <cell=8 then 0 =SUMPRODUCT(--((L9:BF9)=0),--((L9:BF9)<""))+SUMPRODUCT(--((L9:BF9)0),--((L9:BF9<5)))+(SUMPRODUCT(--((L9:BF9)=5),--((L9:BF9)<8))*0.5) -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Yitzhack" wrote: what it needds to return is if 0 = 1 the current formula returns if then 5 = .5. BAsicaly what i need is if L9 is 0 = 1 if is than 0 but< than5 = 1 if than 5 but <than 8 = .05. If you miss up to 3 hours out of 8 = .05 if you miss full day = 1 If you miss more than 3 = 1 -- Regards YM "Thomas [PBD]" wrote: Yitzhack, Could you give me the example data? L9:BF9 Secondly, what is it that you would like to happen? Would you just like to include 0 into the formula? You could change that with "=0" in the first COUNTIF. I could be mistaken. In all essence, what would you like the formula to return? -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Yitzhack" wrote: Good morning all. I'm currently working on a attendance tracker, I have a formula that will give us either half or full occurrence. Before we didnt need to track 0 but now we have. I tried to change the formula to give us a full occurrence (1) but Im getting an error. Can anyone help me with this€¦ Thanks =(COUNTIF(L9:BF9,"0")-COUNTIF(L9:BF9,"=5"))+((COUNTIF(L9:BF9,"=5")-COUNTIF(L9:BF9,"=8"))/2) -- Regards YM |
Help
Yitzhack,
That would require you to know what columns the days were in. If you only had 5 columns, it could be done, but with L:BF that makes it much more difficult. Secondly, you would also have to have some logic into each person, or a variable-based function. For the variable based function, you would have to create a cell for each different criteria. For example: BG9 would have to contain Mondays normal total hours, BH9 Tuesdays normal total hours, etc... as well as say AA9 to have your criteria to display half days, say 3 hours or something. It's not IMPOSSIBLE, but much harder to do. Another possibility would be to create another table for Normal total work hours and half day hours and place a VLOOKUP against the person's name for the number that you were looking for and change the coding as such: =SUMPRODUCT(--((L9:BF9)=0),--((L9:BF9)<""))+SUMPRODUCT(--((L9:BF9)0),--((L9:BF9<VLOOKUP(name,table_array,column_number,FA LSE))))+(SUMPRODUCT(--((L9:BF9)=VLOOKUP(name,table_array,column_number, FALSE)),--((L9:BF9)<VLOOKUP(name,table_array,column_number,F ALSE)))*0.5) This is very hard to accomplish correctly, but not impossible if you spend a little time on it. -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Yitzhack" wrote: Thomas, Thank you so much. This is definetely what i needed.. Do you think that the formula for the "part-timers" is something doable? Thanks again Regards YM "Thomas [PBD]" wrote: Yitzhack, Ok, seems like I have what you need. I think. Basic Parameters: <cell=0 then 1 <cell0 and <5 then 1 <cell=5 and <8 then .5 <cell=8 then 0 =SUMPRODUCT(--((L9:BF9)=0),--((L9:BF9)<""))+SUMPRODUCT(--((L9:BF9)0),--((L9:BF9<5)))+(SUMPRODUCT(--((L9:BF9)=5),--((L9:BF9)<8))*0.5) -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Yitzhack" wrote: what it needds to return is if 0 = 1 the current formula returns if then 5 = .5. BAsicaly what i need is if L9 is 0 = 1 if is than 0 but< than5 = 1 if than 5 but <than 8 = .05. If you miss up to 3 hours out of 8 = .05 if you miss full day = 1 If you miss more than 3 = 1 -- Regards YM "Thomas [PBD]" wrote: Yitzhack, Could you give me the example data? L9:BF9 Secondly, what is it that you would like to happen? Would you just like to include 0 into the formula? You could change that with "=0" in the first COUNTIF. I could be mistaken. In all essence, what would you like the formula to return? -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Yitzhack" wrote: Good morning all. I'm currently working on a attendance tracker, I have a formula that will give us either half or full occurrence. Before we didnt need to track 0 but now we have. I tried to change the formula to give us a full occurrence (1) but Im getting an error. Can anyone help me with this€¦ Thanks =(COUNTIF(L9:BF9,"0")-COUNTIF(L9:BF9,"=5"))+((COUNTIF(L9:BF9,"=5")-COUNTIF(L9:BF9,"=8"))/2) -- Regards YM |
Help
Thomas, Thanks so much for your help.
-- Regards YM "Thomas [PBD]" wrote: Yitzhack, That would require you to know what columns the days were in. If you only had 5 columns, it could be done, but with L:BF that makes it much more difficult. Secondly, you would also have to have some logic into each person, or a variable-based function. For the variable based function, you would have to create a cell for each different criteria. For example: BG9 would have to contain Mondays normal total hours, BH9 Tuesdays normal total hours, etc... as well as say AA9 to have your criteria to display half days, say 3 hours or something. It's not IMPOSSIBLE, but much harder to do. Another possibility would be to create another table for Normal total work hours and half day hours and place a VLOOKUP against the person's name for the number that you were looking for and change the coding as such: =SUMPRODUCT(--((L9:BF9)=0),--((L9:BF9)<""))+SUMPRODUCT(--((L9:BF9)0),--((L9:BF9<VLOOKUP(name,table_array,column_number,FA LSE))))+(SUMPRODUCT(--((L9:BF9)=VLOOKUP(name,table_array,column_number, FALSE)),--((L9:BF9)<VLOOKUP(name,table_array,column_number,F ALSE)))*0.5) This is very hard to accomplish correctly, but not impossible if you spend a little time on it. -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Yitzhack" wrote: Thomas, Thank you so much. This is definetely what i needed.. Do you think that the formula for the "part-timers" is something doable? Thanks again Regards YM "Thomas [PBD]" wrote: Yitzhack, Ok, seems like I have what you need. I think. Basic Parameters: <cell=0 then 1 <cell0 and <5 then 1 <cell=5 and <8 then .5 <cell=8 then 0 =SUMPRODUCT(--((L9:BF9)=0),--((L9:BF9)<""))+SUMPRODUCT(--((L9:BF9)0),--((L9:BF9<5)))+(SUMPRODUCT(--((L9:BF9)=5),--((L9:BF9)<8))*0.5) -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Yitzhack" wrote: what it needds to return is if 0 = 1 the current formula returns if then 5 = .5. BAsicaly what i need is if L9 is 0 = 1 if is than 0 but< than5 = 1 if than 5 but <than 8 = .05. If you miss up to 3 hours out of 8 = .05 if you miss full day = 1 If you miss more than 3 = 1 -- Regards YM "Thomas [PBD]" wrote: Yitzhack, Could you give me the example data? L9:BF9 Secondly, what is it that you would like to happen? Would you just like to include 0 into the formula? You could change that with "=0" in the first COUNTIF. I could be mistaken. In all essence, what would you like the formula to return? -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Yitzhack" wrote: Good morning all. I'm currently working on a attendance tracker, I have a formula that will give us either half or full occurrence. Before we didnt need to track 0 but now we have. I tried to change the formula to give us a full occurrence (1) but Im getting an error. Can anyone help me with this€¦ Thanks =(COUNTIF(L9:BF9,"0")-COUNTIF(L9:BF9,"=5"))+((COUNTIF(L9:BF9,"=5")-COUNTIF(L9:BF9,"=8"))/2) -- Regards YM |
Help
Yitzhack,
You're welcome and thanks for the feedback. -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Yitzhack" wrote: Thomas, Thanks so much for your help. -- Regards YM "Thomas [PBD]" wrote: Yitzhack, That would require you to know what columns the days were in. If you only had 5 columns, it could be done, but with L:BF that makes it much more difficult. Secondly, you would also have to have some logic into each person, or a variable-based function. For the variable based function, you would have to create a cell for each different criteria. For example: BG9 would have to contain Mondays normal total hours, BH9 Tuesdays normal total hours, etc... as well as say AA9 to have your criteria to display half days, say 3 hours or something. It's not IMPOSSIBLE, but much harder to do. Another possibility would be to create another table for Normal total work hours and half day hours and place a VLOOKUP against the person's name for the number that you were looking for and change the coding as such: =SUMPRODUCT(--((L9:BF9)=0),--((L9:BF9)<""))+SUMPRODUCT(--((L9:BF9)0),--((L9:BF9<VLOOKUP(name,table_array,column_number,FA LSE))))+(SUMPRODUCT(--((L9:BF9)=VLOOKUP(name,table_array,column_number, FALSE)),--((L9:BF9)<VLOOKUP(name,table_array,column_number,F ALSE)))*0.5) This is very hard to accomplish correctly, but not impossible if you spend a little time on it. -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Yitzhack" wrote: Thomas, Thank you so much. This is definetely what i needed.. Do you think that the formula for the "part-timers" is something doable? Thanks again Regards YM "Thomas [PBD]" wrote: Yitzhack, Ok, seems like I have what you need. I think. Basic Parameters: <cell=0 then 1 <cell0 and <5 then 1 <cell=5 and <8 then .5 <cell=8 then 0 =SUMPRODUCT(--((L9:BF9)=0),--((L9:BF9)<""))+SUMPRODUCT(--((L9:BF9)0),--((L9:BF9<5)))+(SUMPRODUCT(--((L9:BF9)=5),--((L9:BF9)<8))*0.5) -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Yitzhack" wrote: what it needds to return is if 0 = 1 the current formula returns if then 5 = .5. BAsicaly what i need is if L9 is 0 = 1 if is than 0 but< than5 = 1 if than 5 but <than 8 = .05. If you miss up to 3 hours out of 8 = .05 if you miss full day = 1 If you miss more than 3 = 1 -- Regards YM "Thomas [PBD]" wrote: Yitzhack, Could you give me the example data? L9:BF9 Secondly, what is it that you would like to happen? Would you just like to include 0 into the formula? You could change that with "=0" in the first COUNTIF. I could be mistaken. In all essence, what would you like the formula to return? -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Yitzhack" wrote: Good morning all. I'm currently working on a attendance tracker, I have a formula that will give us either half or full occurrence. Before we didnt need to track 0 but now we have. I tried to change the formula to give us a full occurrence (1) but Im getting an error. Can anyone help me with this€¦ Thanks =(COUNTIF(L9:BF9,"0")-COUNTIF(L9:BF9,"=5"))+((COUNTIF(L9:BF9,"=5")-COUNTIF(L9:BF9,"=8"))/2) -- Regards YM |
| All times are GMT +1. The time now is 11:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com