|
sumif and translate annual hours into weekly quarter hours
i have a 2 part question:
i need a formula that will look for a value in a particular cell then based on which column it is found in multiply by the corresponding value to return the correct product i.e., look across columns a, b & c...if it finds a number in a multiply by 260 if it finds a number in column b multiply by 52 if it finds a number in column c multiply by 12 there will never be a number in more than one column 2nd question...how can i translate a number into hours broken down by quarters 18 hours*52 weeks = .346...i need .346 converted to hours or rounded up to the nearest quarter hour |
sumif and translate annual hours into weekly quarter hours
Format cell as 1)
=if(MATCH(value,2:2,0)=1,value*260,If(MATCH(value, 2:2,0)=2,value*52,If(MATCH(value,2:2,0)=2,value*12 ) 2) Trying to figure out how 18 hours times 52 weeks equals .346??? Well, either way, try =ROUND(Time*96,0)/96 "se7098" wrote: i have a 2 part question: i need a formula that will look for a value in a particular cell then based on which column it is found in multiply by the corresponding value to return the correct product i.e., look across columns a, b & c...if it finds a number in a multiply by 260 if it finds a number in column b multiply by 52 if it finds a number in column c multiply by 12 there will never be a number in more than one column 2nd question...how can i translate a number into hours broken down by quarters 18 hours*52 weeks = .346...i need .346 converted to hours or rounded up to the nearest quarter hour |
sumif and translate annual hours into weekly quarter hours
Question 1:
Assuming the cells will only contain numbers... Assuming the value is 10... =10*IF(A1,260,IF(B1,52,IF(C1,12,0))) Question 2: not following you on this one! -- Biff Microsoft Excel MVP "se7098" wrote in message ... i have a 2 part question: i need a formula that will look for a value in a particular cell then based on which column it is found in multiply by the corresponding value to return the correct product i.e., look across columns a, b & c...if it finds a number in a multiply by 260 if it finds a number in column b multiply by 52 if it finds a number in column c multiply by 12 there will never be a number in more than one column 2nd question...how can i translate a number into hours broken down by quarters 18 hours*52 weeks = .346...i need .346 converted to hours or rounded up to the nearest quarter hour |
sumif and translate annual hours into weekly quarter hours
My guess is that he has a typo, maybe it should be 18/52
converted to hours is 00:20:00 -- Regards, Peo Sjoblom "T. Valko" wrote in message ... Question 1: Assuming the cells will only contain numbers... Assuming the value is 10... =10*IF(A1,260,IF(B1,52,IF(C1,12,0))) Question 2: not following you on this one! -- Biff Microsoft Excel MVP "se7098" wrote in message ... i have a 2 part question: i need a formula that will look for a value in a particular cell then based on which column it is found in multiply by the corresponding value to return the correct product i.e., look across columns a, b & c...if it finds a number in a multiply by 260 if it finds a number in column b multiply by 52 if it finds a number in column c multiply by 12 there will never be a number in more than one column 2nd question...how can i translate a number into hours broken down by quarters 18 hours*52 weeks = .346...i need .346 converted to hours or rounded up to the nearest quarter hour |
sumif and translate annual hours into weekly quarter hours
not sure what you mean by value.
what i am attempting to do is determine how many annual hours are spent on given tasks...some are performed daily, some weekly, some monthly column a represents daily, column b - weekly, column c - monthly so if a task is performed 3 times daily a "3" would be in column a...if it is performed 1 weekly a "1" would be in column b etc it is only counted as either daily, weekly or monthly...never all 3 i want the result returned in column d which is my annual hours spent on this task column so if column a has a 3 in it then column b and c would both be blank i want the formula in column d to look at columns a b & c find which one has a value then take that value and multiply it times 260 if found in column a, 52 if found in column b or 12 if found in column c...am i asking for the impossible? then once i have the annual number of hours, i need to translate that into weekly hours and monthly hours...kind of calculated backwards if that makes and sense so if column d = 18.00 hours...divide that by 52 for the weekly time spent on the task in column e and divide it by 12 for the monthly time spent in column f or if there is an easier way to get this all done i am very open to suggestions. thanks! "T. Valko" wrote: Question 1: Assuming the cells will only contain numbers... Assuming the value is 10... =10*IF(A1,260,IF(B1,52,IF(C1,12,0))) Question 2: not following you on this one! -- Biff Microsoft Excel MVP "se7098" wrote in message ... i have a 2 part question: i need a formula that will look for a value in a particular cell then based on which column it is found in multiply by the corresponding value to return the correct product i.e., look across columns a, b & c...if it finds a number in a multiply by 260 if it finds a number in column b multiply by 52 if it finds a number in column c multiply by 12 there will never be a number in more than one column 2nd question...how can i translate a number into hours broken down by quarters 18 hours*52 weeks = .346...i need .346 converted to hours or rounded up to the nearest quarter hour |
sumif and translate annual hours into weekly quarter hours
Ans #1:
D1 =(A1*260)+(B1*52)+(C1*12) "se7098" wrote: not sure what you mean by value. what i am attempting to do is determine how many annual hours are spent on given tasks...some are performed daily, some weekly, some monthly column a represents daily, column b - weekly, column c - monthly so if a task is performed 3 times daily a "3" would be in column a...if it is performed 1 weekly a "1" would be in column b etc it is only counted as either daily, weekly or monthly...never all 3 i want the result returned in column d which is my annual hours spent on this task column so if column a has a 3 in it then column b and c would both be blank i want the formula in column d to look at columns a b & c find which one has a value then take that value and multiply it times 260 if found in column a, 52 if found in column b or 12 if found in column c...am i asking for the impossible? then once i have the annual number of hours, i need to translate that into weekly hours and monthly hours...kind of calculated backwards if that makes and sense so if column d = 18.00 hours...divide that by 52 for the weekly time spent on the task in column e and divide it by 12 for the monthly time spent in column f or if there is an easier way to get this all done i am very open to suggestions. thanks! "T. Valko" wrote: Question 1: Assuming the cells will only contain numbers... Assuming the value is 10... =10*IF(A1,260,IF(B1,52,IF(C1,12,0))) Question 2: not following you on this one! -- Biff Microsoft Excel MVP "se7098" wrote in message ... i have a 2 part question: i need a formula that will look for a value in a particular cell then based on which column it is found in multiply by the corresponding value to return the correct product i.e., look across columns a, b & c...if it finds a number in a multiply by 260 if it finds a number in column b multiply by 52 if it finds a number in column c multiply by 12 there will never be a number in more than one column 2nd question...how can i translate a number into hours broken down by quarters 18 hours*52 weeks = .346...i need .346 converted to hours or rounded up to the nearest quarter hour |
sumif and translate annual hours into weekly quarter hours
ans 2
E1=D1/52 F1=D1/12 We were off the tracks there "Teethless mama" wrote: Ans #1: D1 =(A1*260)+(B1*52)+(C1*12) "se7098" wrote: not sure what you mean by value. what i am attempting to do is determine how many annual hours are spent on given tasks...some are performed daily, some weekly, some monthly column a represents daily, column b - weekly, column c - monthly so if a task is performed 3 times daily a "3" would be in column a...if it is performed 1 weekly a "1" would be in column b etc it is only counted as either daily, weekly or monthly...never all 3 i want the result returned in column d which is my annual hours spent on this task column so if column a has a 3 in it then column b and c would both be blank i want the formula in column d to look at columns a b & c find which one has a value then take that value and multiply it times 260 if found in column a, 52 if found in column b or 12 if found in column c...am i asking for the impossible? then once i have the annual number of hours, i need to translate that into weekly hours and monthly hours...kind of calculated backwards if that makes and sense so if column d = 18.00 hours...divide that by 52 for the weekly time spent on the task in column e and divide it by 12 for the monthly time spent in column f or if there is an easier way to get this all done i am very open to suggestions. thanks! "T. Valko" wrote: Question 1: Assuming the cells will only contain numbers... Assuming the value is 10... =10*IF(A1,260,IF(B1,52,IF(C1,12,0))) Question 2: not following you on this one! -- Biff Microsoft Excel MVP "se7098" wrote in message ... i have a 2 part question: i need a formula that will look for a value in a particular cell then based on which column it is found in multiply by the corresponding value to return the correct product i.e., look across columns a, b & c...if it finds a number in a multiply by 260 if it finds a number in column b multiply by 52 if it finds a number in column c multiply by 12 there will never be a number in more than one column 2nd question...how can i translate a number into hours broken down by quarters 18 hours*52 weeks = .346...i need .346 converted to hours or rounded up to the nearest quarter hour |
sumif and translate annual hours into weekly quarter hours
this worked for #1...thanks!
"Teethless mama" wrote: Ans #1: D1 =(A1*260)+(B1*52)+(C1*12) "se7098" wrote: not sure what you mean by value. what i am attempting to do is determine how many annual hours are spent on given tasks...some are performed daily, some weekly, some monthly column a represents daily, column b - weekly, column c - monthly so if a task is performed 3 times daily a "3" would be in column a...if it is performed 1 weekly a "1" would be in column b etc it is only counted as either daily, weekly or monthly...never all 3 i want the result returned in column d which is my annual hours spent on this task column so if column a has a 3 in it then column b and c would both be blank i want the formula in column d to look at columns a b & c find which one has a value then take that value and multiply it times 260 if found in column a, 52 if found in column b or 12 if found in column c...am i asking for the impossible? then once i have the annual number of hours, i need to translate that into weekly hours and monthly hours...kind of calculated backwards if that makes and sense so if column d = 18.00 hours...divide that by 52 for the weekly time spent on the task in column e and divide it by 12 for the monthly time spent in column f or if there is an easier way to get this all done i am very open to suggestions. thanks! "T. Valko" wrote: Question 1: Assuming the cells will only contain numbers... Assuming the value is 10... =10*IF(A1,260,IF(B1,52,IF(C1,12,0))) Question 2: not following you on this one! -- Biff Microsoft Excel MVP "se7098" wrote in message ... i have a 2 part question: i need a formula that will look for a value in a particular cell then based on which column it is found in multiply by the corresponding value to return the correct product i.e., look across columns a, b & c...if it finds a number in a multiply by 260 if it finds a number in column b multiply by 52 if it finds a number in column c multiply by 12 there will never be a number in more than one column 2nd question...how can i translate a number into hours broken down by quarters 18 hours*52 weeks = .346...i need .346 converted to hours or rounded up to the nearest quarter hour |
sumif and translate annual hours into weekly quarter hours
the problem with that is i am being asked to provide the number rounded to
the nearest quarter hour so when my annual time is 36 and i divide that by 52 my answer is 0.692...how can i make that auto round to .75? "Sean Timmons" wrote: ans 2 E1=D1/52 F1=D1/12 We were off the tracks there "Teethless mama" wrote: Ans #1: D1 =(A1*260)+(B1*52)+(C1*12) "se7098" wrote: not sure what you mean by value. what i am attempting to do is determine how many annual hours are spent on given tasks...some are performed daily, some weekly, some monthly column a represents daily, column b - weekly, column c - monthly so if a task is performed 3 times daily a "3" would be in column a...if it is performed 1 weekly a "1" would be in column b etc it is only counted as either daily, weekly or monthly...never all 3 i want the result returned in column d which is my annual hours spent on this task column so if column a has a 3 in it then column b and c would both be blank i want the formula in column d to look at columns a b & c find which one has a value then take that value and multiply it times 260 if found in column a, 52 if found in column b or 12 if found in column c...am i asking for the impossible? then once i have the annual number of hours, i need to translate that into weekly hours and monthly hours...kind of calculated backwards if that makes and sense so if column d = 18.00 hours...divide that by 52 for the weekly time spent on the task in column e and divide it by 12 for the monthly time spent in column f or if there is an easier way to get this all done i am very open to suggestions. thanks! "T. Valko" wrote: Question 1: Assuming the cells will only contain numbers... Assuming the value is 10... =10*IF(A1,260,IF(B1,52,IF(C1,12,0))) Question 2: not following you on this one! -- Biff Microsoft Excel MVP "se7098" wrote in message ... i have a 2 part question: i need a formula that will look for a value in a particular cell then based on which column it is found in multiply by the corresponding value to return the correct product i.e., look across columns a, b & c...if it finds a number in a multiply by 260 if it finds a number in column b multiply by 52 if it finds a number in column c multiply by 12 there will never be a number in more than one column 2nd question...how can i translate a number into hours broken down by quarters 18 hours*52 weeks = .346...i need .346 converted to hours or rounded up to the nearest quarter hour |
sumif and translate annual hours into weekly quarter hours
Ok, now I get it! Try this:
I'm assuming you're entering decimal numbers in A1:C1 Formula in D1: =IF(A1,A1*260,IF(B1,B1*52,IF(C1,C1*12,0))) Formula in E1: =CEILING((MAX(A1:C1)/24)/52,TIME(0,15,0)) Formula in F1: =CEILING((MAX(A1:C1)/24)/12,TIME(0,15,0)) Format E1:F1 as [h]:mm -- Biff Microsoft Excel MVP "se7098" wrote in message ... not sure what you mean by value. what i am attempting to do is determine how many annual hours are spent on given tasks...some are performed daily, some weekly, some monthly column a represents daily, column b - weekly, column c - monthly so if a task is performed 3 times daily a "3" would be in column a...if it is performed 1 weekly a "1" would be in column b etc it is only counted as either daily, weekly or monthly...never all 3 i want the result returned in column d which is my annual hours spent on this task column so if column a has a 3 in it then column b and c would both be blank i want the formula in column d to look at columns a b & c find which one has a value then take that value and multiply it times 260 if found in column a, 52 if found in column b or 12 if found in column c...am i asking for the impossible? then once i have the annual number of hours, i need to translate that into weekly hours and monthly hours...kind of calculated backwards if that makes and sense so if column d = 18.00 hours...divide that by 52 for the weekly time spent on the task in column e and divide it by 12 for the monthly time spent in column f or if there is an easier way to get this all done i am very open to suggestions. thanks! "T. Valko" wrote: Question 1: Assuming the cells will only contain numbers... Assuming the value is 10... =10*IF(A1,260,IF(B1,52,IF(C1,12,0))) Question 2: not following you on this one! -- Biff Microsoft Excel MVP "se7098" wrote in message ... i have a 2 part question: i need a formula that will look for a value in a particular cell then based on which column it is found in multiply by the corresponding value to return the correct product i.e., look across columns a, b & c...if it finds a number in a multiply by 260 if it finds a number in column b multiply by 52 if it finds a number in column c multiply by 12 there will never be a number in more than one column 2nd question...how can i translate a number into hours broken down by quarters 18 hours*52 weeks = .346...i need .346 converted to hours or rounded up to the nearest quarter hour |
sumif and translate annual hours into weekly quarter hours
MROUND
Look it up in Excel help. -- David Biddulph "se7098" wrote in message ... the problem with that is i am being asked to provide the number rounded to the nearest quarter hour so when my annual time is 36 and i divide that by 52 my answer is 0.692...how can i make that auto round to .75? "Sean Timmons" wrote: ans 2 E1=D1/52 F1=D1/12 We were off the tracks there "Teethless mama" wrote: Ans #1: D1 =(A1*260)+(B1*52)+(C1*12) "se7098" wrote: not sure what you mean by value. what i am attempting to do is determine how many annual hours are spent on given tasks...some are performed daily, some weekly, some monthly column a represents daily, column b - weekly, column c - monthly so if a task is performed 3 times daily a "3" would be in column a...if it is performed 1 weekly a "1" would be in column b etc it is only counted as either daily, weekly or monthly...never all 3 i want the result returned in column d which is my annual hours spent on this task column so if column a has a 3 in it then column b and c would both be blank i want the formula in column d to look at columns a b & c find which one has a value then take that value and multiply it times 260 if found in column a, 52 if found in column b or 12 if found in column c...am i asking for the impossible? then once i have the annual number of hours, i need to translate that into weekly hours and monthly hours...kind of calculated backwards if that makes and sense so if column d = 18.00 hours...divide that by 52 for the weekly time spent on the task in column e and divide it by 12 for the monthly time spent in column f or if there is an easier way to get this all done i am very open to suggestions. thanks! "T. Valko" wrote: Question 1: Assuming the cells will only contain numbers... Assuming the value is 10... =10*IF(A1,260,IF(B1,52,IF(C1,12,0))) Question 2: not following you on this one! -- Biff Microsoft Excel MVP "se7098" wrote in message ... i have a 2 part question: i need a formula that will look for a value in a particular cell then based on which column it is found in multiply by the corresponding value to return the correct product i.e., look across columns a, b & c...if it finds a number in a multiply by 260 if it finds a number in column b multiply by 52 if it finds a number in column c multiply by 12 there will never be a number in more than one column 2nd question...how can i translate a number into hours broken down by quarters 18 hours*52 weeks = .346...i need .346 converted to hours or rounded up to the nearest quarter hour |
sumif and translate annual hours into weekly quarter hours
Ooops!
Corrections: Formula in E1: =CEILING(D1/52,TIME(0,15,0)) Formula in F1: =CEILING(D1/12,TIME(0,15,0)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Ok, now I get it! Try this: I'm assuming you're entering decimal numbers in A1:C1 Formula in D1: =IF(A1,A1*260,IF(B1,B1*52,IF(C1,C1*12,0))) Formula in E1: =CEILING((MAX(A1:C1)/24)/52,TIME(0,15,0)) Formula in F1: =CEILING((MAX(A1:C1)/24)/12,TIME(0,15,0)) Format E1:F1 as [h]:mm -- Biff Microsoft Excel MVP "se7098" wrote in message ... not sure what you mean by value. what i am attempting to do is determine how many annual hours are spent on given tasks...some are performed daily, some weekly, some monthly column a represents daily, column b - weekly, column c - monthly so if a task is performed 3 times daily a "3" would be in column a...if it is performed 1 weekly a "1" would be in column b etc it is only counted as either daily, weekly or monthly...never all 3 i want the result returned in column d which is my annual hours spent on this task column so if column a has a 3 in it then column b and c would both be blank i want the formula in column d to look at columns a b & c find which one has a value then take that value and multiply it times 260 if found in column a, 52 if found in column b or 12 if found in column c...am i asking for the impossible? then once i have the annual number of hours, i need to translate that into weekly hours and monthly hours...kind of calculated backwards if that makes and sense so if column d = 18.00 hours...divide that by 52 for the weekly time spent on the task in column e and divide it by 12 for the monthly time spent in column f or if there is an easier way to get this all done i am very open to suggestions. thanks! "T. Valko" wrote: Question 1: Assuming the cells will only contain numbers... Assuming the value is 10... =10*IF(A1,260,IF(B1,52,IF(C1,12,0))) Question 2: not following you on this one! -- Biff Microsoft Excel MVP "se7098" wrote in message ... i have a 2 part question: i need a formula that will look for a value in a particular cell then based on which column it is found in multiply by the corresponding value to return the correct product i.e., look across columns a, b & c...if it finds a number in a multiply by 260 if it finds a number in column b multiply by 52 if it finds a number in column c multiply by 12 there will never be a number in more than one column 2nd question...how can i translate a number into hours broken down by quarters 18 hours*52 weeks = .346...i need .346 converted to hours or rounded up to the nearest quarter hour |
sumif and translate annual hours into weekly quarter hours
Just to clarify, it would be
=MROUND(D1/52,.25) and =MROUND(D1/12,.25) "David Biddulph" wrote: MROUND Look it up in Excel help. -- David Biddulph "se7098" wrote in message ... the problem with that is i am being asked to provide the number rounded to the nearest quarter hour so when my annual time is 36 and i divide that by 52 my answer is 0.692...how can i make that auto round to .75? "Sean Timmons" wrote: ans 2 E1=D1/52 F1=D1/12 We were off the tracks there "Teethless mama" wrote: Ans #1: D1 =(A1*260)+(B1*52)+(C1*12) "se7098" wrote: not sure what you mean by value. what i am attempting to do is determine how many annual hours are spent on given tasks...some are performed daily, some weekly, some monthly column a represents daily, column b - weekly, column c - monthly so if a task is performed 3 times daily a "3" would be in column a...if it is performed 1 weekly a "1" would be in column b etc it is only counted as either daily, weekly or monthly...never all 3 i want the result returned in column d which is my annual hours spent on this task column so if column a has a 3 in it then column b and c would both be blank i want the formula in column d to look at columns a b & c find which one has a value then take that value and multiply it times 260 if found in column a, 52 if found in column b or 12 if found in column c...am i asking for the impossible? then once i have the annual number of hours, i need to translate that into weekly hours and monthly hours...kind of calculated backwards if that makes and sense so if column d = 18.00 hours...divide that by 52 for the weekly time spent on the task in column e and divide it by 12 for the monthly time spent in column f or if there is an easier way to get this all done i am very open to suggestions. thanks! "T. Valko" wrote: Question 1: Assuming the cells will only contain numbers... Assuming the value is 10... =10*IF(A1,260,IF(B1,52,IF(C1,12,0))) Question 2: not following you on this one! -- Biff Microsoft Excel MVP "se7098" wrote in message ... i have a 2 part question: i need a formula that will look for a value in a particular cell then based on which column it is found in multiply by the corresponding value to return the correct product i.e., look across columns a, b & c...if it finds a number in a multiply by 260 if it finds a number in column b multiply by 52 if it finds a number in column c multiply by 12 there will never be a number in more than one column 2nd question...how can i translate a number into hours broken down by quarters 18 hours*52 weeks = .346...i need .346 converted to hours or rounded up to the nearest quarter hour |
sumif and translate annual hours into weekly quarter hours
closer :) but is rounding up to .70...would like to round to .75...possible?
what do i need to change? "T. Valko" wrote: Ooops! Corrections: Formula in E1: =CEILING(D1/52,TIME(0,15,0)) Formula in F1: =CEILING(D1/12,TIME(0,15,0)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Ok, now I get it! Try this: I'm assuming you're entering decimal numbers in A1:C1 Formula in D1: =IF(A1,A1*260,IF(B1,B1*52,IF(C1,C1*12,0))) Formula in E1: =CEILING((MAX(A1:C1)/24)/52,TIME(0,15,0)) Formula in F1: =CEILING((MAX(A1:C1)/24)/12,TIME(0,15,0)) Format E1:F1 as [h]:mm -- Biff Microsoft Excel MVP "se7098" wrote in message ... not sure what you mean by value. what i am attempting to do is determine how many annual hours are spent on given tasks...some are performed daily, some weekly, some monthly column a represents daily, column b - weekly, column c - monthly so if a task is performed 3 times daily a "3" would be in column a...if it is performed 1 weekly a "1" would be in column b etc it is only counted as either daily, weekly or monthly...never all 3 i want the result returned in column d which is my annual hours spent on this task column so if column a has a 3 in it then column b and c would both be blank i want the formula in column d to look at columns a b & c find which one has a value then take that value and multiply it times 260 if found in column a, 52 if found in column b or 12 if found in column c...am i asking for the impossible? then once i have the annual number of hours, i need to translate that into weekly hours and monthly hours...kind of calculated backwards if that makes and sense so if column d = 18.00 hours...divide that by 52 for the weekly time spent on the task in column e and divide it by 12 for the monthly time spent in column f or if there is an easier way to get this all done i am very open to suggestions. thanks! "T. Valko" wrote: Question 1: Assuming the cells will only contain numbers... Assuming the value is 10... =10*IF(A1,260,IF(B1,52,IF(C1,12,0))) Question 2: not following you on this one! -- Biff Microsoft Excel MVP "se7098" wrote in message ... i have a 2 part question: i need a formula that will look for a value in a particular cell then based on which column it is found in multiply by the corresponding value to return the correct product i.e., look across columns a, b & c...if it finds a number in a multiply by 260 if it finds a number in column b multiply by 52 if it finds a number in column c multiply by 12 there will never be a number in more than one column 2nd question...how can i translate a number into hours broken down by quarters 18 hours*52 weeks = .346...i need .346 converted to hours or rounded up to the nearest quarter hour |
sumif and translate annual hours into weekly quarter hours
this is returning an error: #NAME?
"Sean Timmons" wrote: Just to clarify, it would be =MROUND(D1/52,.25) and =MROUND(D1/12,.25) "David Biddulph" wrote: MROUND Look it up in Excel help. -- David Biddulph "se7098" wrote in message ... the problem with that is i am being asked to provide the number rounded to the nearest quarter hour so when my annual time is 36 and i divide that by 52 my answer is 0.692...how can i make that auto round to .75? "Sean Timmons" wrote: ans 2 E1=D1/52 F1=D1/12 We were off the tracks there "Teethless mama" wrote: Ans #1: D1 =(A1*260)+(B1*52)+(C1*12) "se7098" wrote: not sure what you mean by value. what i am attempting to do is determine how many annual hours are spent on given tasks...some are performed daily, some weekly, some monthly column a represents daily, column b - weekly, column c - monthly so if a task is performed 3 times daily a "3" would be in column a...if it is performed 1 weekly a "1" would be in column b etc it is only counted as either daily, weekly or monthly...never all 3 i want the result returned in column d which is my annual hours spent on this task column so if column a has a 3 in it then column b and c would both be blank i want the formula in column d to look at columns a b & c find which one has a value then take that value and multiply it times 260 if found in column a, 52 if found in column b or 12 if found in column c...am i asking for the impossible? then once i have the annual number of hours, i need to translate that into weekly hours and monthly hours...kind of calculated backwards if that makes and sense so if column d = 18.00 hours...divide that by 52 for the weekly time spent on the task in column e and divide it by 12 for the monthly time spent in column f or if there is an easier way to get this all done i am very open to suggestions. thanks! "T. Valko" wrote: Question 1: Assuming the cells will only contain numbers... Assuming the value is 10... =10*IF(A1,260,IF(B1,52,IF(C1,12,0))) Question 2: not following you on this one! -- Biff Microsoft Excel MVP "se7098" wrote in message ... i have a 2 part question: i need a formula that will look for a value in a particular cell then based on which column it is found in multiply by the corresponding value to return the correct product i.e., look across columns a, b & c...if it finds a number in a multiply by 260 if it finds a number in column b multiply by 52 if it finds a number in column c multiply by 12 there will never be a number in more than one column 2nd question...how can i translate a number into hours broken down by quarters 18 hours*52 weeks = .346...i need .346 converted to hours or rounded up to the nearest quarter hour |
sumif and translate annual hours into weekly quarter hours
It's part of the 211014.66, it comes with Excel but does not install
by default. Keep a CD handy of your Office and do toolsoptionsadd-ins and select Analysis Toolpak then follow the instructions -- Regards, Peo Sjoblom "se7098" wrote in message ... this is returning an error: #NAME? "Sean Timmons" wrote: Just to clarify, it would be =MROUND(D1/52,.25) and =MROUND(D1/12,.25) "David Biddulph" wrote: MROUND Look it up in Excel help. -- David Biddulph "se7098" wrote in message ... the problem with that is i am being asked to provide the number rounded to the nearest quarter hour so when my annual time is 36 and i divide that by 52 my answer is 0.692...how can i make that auto round to .75? "Sean Timmons" wrote: ans 2 E1=D1/52 F1=D1/12 We were off the tracks there "Teethless mama" wrote: Ans #1: D1 =(A1*260)+(B1*52)+(C1*12) "se7098" wrote: not sure what you mean by value. what i am attempting to do is determine how many annual hours are spent on given tasks...some are performed daily, some weekly, some monthly column a represents daily, column b - weekly, column c - monthly so if a task is performed 3 times daily a "3" would be in column a...if it is performed 1 weekly a "1" would be in column b etc it is only counted as either daily, weekly or monthly...never all 3 i want the result returned in column d which is my annual hours spent on this task column so if column a has a 3 in it then column b and c would both be blank i want the formula in column d to look at columns a b & c find which one has a value then take that value and multiply it times 260 if found in column a, 52 if found in column b or 12 if found in column c...am i asking for the impossible? then once i have the annual number of hours, i need to translate that into weekly hours and monthly hours...kind of calculated backwards if that makes and sense so if column d = 18.00 hours...divide that by 52 for the weekly time spent on the task in column e and divide it by 12 for the monthly time spent in column f or if there is an easier way to get this all done i am very open to suggestions. thanks! "T. Valko" wrote: Question 1: Assuming the cells will only contain numbers... Assuming the value is 10... =10*IF(A1,260,IF(B1,52,IF(C1,12,0))) Question 2: not following you on this one! -- Biff Microsoft Excel MVP "se7098" wrote in message ... i have a 2 part question: i need a formula that will look for a value in a particular cell then based on which column it is found in multiply by the corresponding value to return the correct product i.e., look across columns a, b & c...if it finds a number in a multiply by 260 if it finds a number in column b multiply by 52 if it finds a number in column c multiply by 12 there will never be a number in more than one column 2nd question...how can i translate a number into hours broken down by quarters 18 hours*52 weeks = .346...i need .346 converted to hours or rounded up to the nearest quarter hour |
sumif and translate annual hours into weekly quarter hours
Go to Tools - Addins...
Add Analasis Toolpak "se7098" wrote: this is returning an error: #NAME? "Sean Timmons" wrote: Just to clarify, it would be =MROUND(D1/52,.25) and =MROUND(D1/12,.25) "David Biddulph" wrote: MROUND Look it up in Excel help. -- David Biddulph "se7098" wrote in message ... the problem with that is i am being asked to provide the number rounded to the nearest quarter hour so when my annual time is 36 and i divide that by 52 my answer is 0.692...how can i make that auto round to .75? "Sean Timmons" wrote: ans 2 E1=D1/52 F1=D1/12 We were off the tracks there "Teethless mama" wrote: Ans #1: D1 =(A1*260)+(B1*52)+(C1*12) "se7098" wrote: not sure what you mean by value. what i am attempting to do is determine how many annual hours are spent on given tasks...some are performed daily, some weekly, some monthly column a represents daily, column b - weekly, column c - monthly so if a task is performed 3 times daily a "3" would be in column a...if it is performed 1 weekly a "1" would be in column b etc it is only counted as either daily, weekly or monthly...never all 3 i want the result returned in column d which is my annual hours spent on this task column so if column a has a 3 in it then column b and c would both be blank i want the formula in column d to look at columns a b & c find which one has a value then take that value and multiply it times 260 if found in column a, 52 if found in column b or 12 if found in column c...am i asking for the impossible? then once i have the annual number of hours, i need to translate that into weekly hours and monthly hours...kind of calculated backwards if that makes and sense so if column d = 18.00 hours...divide that by 52 for the weekly time spent on the task in column e and divide it by 12 for the monthly time spent in column f or if there is an easier way to get this all done i am very open to suggestions. thanks! "T. Valko" wrote: Question 1: Assuming the cells will only contain numbers... Assuming the value is 10... =10*IF(A1,260,IF(B1,52,IF(C1,12,0))) Question 2: not following you on this one! -- Biff Microsoft Excel MVP "se7098" wrote in message ... i have a 2 part question: i need a formula that will look for a value in a particular cell then based on which column it is found in multiply by the corresponding value to return the correct product i.e., look across columns a, b & c...if it finds a number in a multiply by 260 if it finds a number in column b multiply by 52 if it finds a number in column c multiply by 12 there will never be a number in more than one column 2nd question...how can i translate a number into hours broken down by quarters 18 hours*52 weeks = .346...i need .346 converted to hours or rounded up to the nearest quarter hour |
sumif and translate annual hours into weekly quarter hours
Wow.. spelling.. Analysis Toolpak.
"se7098" wrote: this is returning an error: #NAME? "Sean Timmons" wrote: Just to clarify, it would be =MROUND(D1/52,.25) and =MROUND(D1/12,.25) "David Biddulph" wrote: MROUND Look it up in Excel help. -- David Biddulph "se7098" wrote in message ... the problem with that is i am being asked to provide the number rounded to the nearest quarter hour so when my annual time is 36 and i divide that by 52 my answer is 0.692...how can i make that auto round to .75? "Sean Timmons" wrote: ans 2 E1=D1/52 F1=D1/12 We were off the tracks there "Teethless mama" wrote: Ans #1: D1 =(A1*260)+(B1*52)+(C1*12) "se7098" wrote: not sure what you mean by value. what i am attempting to do is determine how many annual hours are spent on given tasks...some are performed daily, some weekly, some monthly column a represents daily, column b - weekly, column c - monthly so if a task is performed 3 times daily a "3" would be in column a...if it is performed 1 weekly a "1" would be in column b etc it is only counted as either daily, weekly or monthly...never all 3 i want the result returned in column d which is my annual hours spent on this task column so if column a has a 3 in it then column b and c would both be blank i want the formula in column d to look at columns a b & c find which one has a value then take that value and multiply it times 260 if found in column a, 52 if found in column b or 12 if found in column c...am i asking for the impossible? then once i have the annual number of hours, i need to translate that into weekly hours and monthly hours...kind of calculated backwards if that makes and sense so if column d = 18.00 hours...divide that by 52 for the weekly time spent on the task in column e and divide it by 12 for the monthly time spent in column f or if there is an easier way to get this all done i am very open to suggestions. thanks! "T. Valko" wrote: Question 1: Assuming the cells will only contain numbers... Assuming the value is 10... =10*IF(A1,260,IF(B1,52,IF(C1,12,0))) Question 2: not following you on this one! -- Biff Microsoft Excel MVP "se7098" wrote in message ... i have a 2 part question: i need a formula that will look for a value in a particular cell then based on which column it is found in multiply by the corresponding value to return the correct product i.e., look across columns a, b & c...if it finds a number in a multiply by 260 if it finds a number in column b multiply by 52 if it finds a number in column c multiply by 12 there will never be a number in more than one column 2nd question...how can i translate a number into hours broken down by quarters 18 hours*52 weeks = .346...i need .346 converted to hours or rounded up to the nearest quarter hour |
sumif and translate annual hours into weekly quarter hours
great...perfect...awesome!!! thank you SO MUCH!
"Sean Timmons" wrote: Wow.. spelling.. Analysis Toolpak. "se7098" wrote: this is returning an error: #NAME? "Sean Timmons" wrote: Just to clarify, it would be =MROUND(D1/52,.25) and =MROUND(D1/12,.25) "David Biddulph" wrote: MROUND Look it up in Excel help. -- David Biddulph "se7098" wrote in message ... the problem with that is i am being asked to provide the number rounded to the nearest quarter hour so when my annual time is 36 and i divide that by 52 my answer is 0.692...how can i make that auto round to .75? "Sean Timmons" wrote: ans 2 E1=D1/52 F1=D1/12 We were off the tracks there "Teethless mama" wrote: Ans #1: D1 =(A1*260)+(B1*52)+(C1*12) "se7098" wrote: not sure what you mean by value. what i am attempting to do is determine how many annual hours are spent on given tasks...some are performed daily, some weekly, some monthly column a represents daily, column b - weekly, column c - monthly so if a task is performed 3 times daily a "3" would be in column a...if it is performed 1 weekly a "1" would be in column b etc it is only counted as either daily, weekly or monthly...never all 3 i want the result returned in column d which is my annual hours spent on this task column so if column a has a 3 in it then column b and c would both be blank i want the formula in column d to look at columns a b & c find which one has a value then take that value and multiply it times 260 if found in column a, 52 if found in column b or 12 if found in column c...am i asking for the impossible? then once i have the annual number of hours, i need to translate that into weekly hours and monthly hours...kind of calculated backwards if that makes and sense so if column d = 18.00 hours...divide that by 52 for the weekly time spent on the task in column e and divide it by 12 for the monthly time spent in column f or if there is an easier way to get this all done i am very open to suggestions. thanks! "T. Valko" wrote: Question 1: Assuming the cells will only contain numbers... Assuming the value is 10... =10*IF(A1,260,IF(B1,52,IF(C1,12,0))) Question 2: not following you on this one! -- Biff Microsoft Excel MVP "se7098" wrote in message ... i have a 2 part question: i need a formula that will look for a value in a particular cell then based on which column it is found in multiply by the corresponding value to return the correct product i.e., look across columns a, b & c...if it finds a number in a multiply by 260 if it finds a number in column b multiply by 52 if it finds a number in column c multiply by 12 there will never be a number in more than one column 2nd question...how can i translate a number into hours broken down by quarters 18 hours*52 weeks = .346...i need .346 converted to hours or rounded up to the nearest quarter hour |
sumif and translate annual hours into weekly quarter hours
It's better than mine 211014.66 <g
-- Regards, Peo Sjoblom "Sean Timmons" wrote in message ... Wow.. spelling.. Analysis Toolpak. "se7098" wrote: this is returning an error: #NAME? "Sean Timmons" wrote: Just to clarify, it would be =MROUND(D1/52,.25) and =MROUND(D1/12,.25) "David Biddulph" wrote: MROUND Look it up in Excel help. -- David Biddulph "se7098" wrote in message ... the problem with that is i am being asked to provide the number rounded to the nearest quarter hour so when my annual time is 36 and i divide that by 52 my answer is 0.692...how can i make that auto round to .75? "Sean Timmons" wrote: ans 2 E1=D1/52 F1=D1/12 We were off the tracks there "Teethless mama" wrote: Ans #1: D1 =(A1*260)+(B1*52)+(C1*12) "se7098" wrote: not sure what you mean by value. what i am attempting to do is determine how many annual hours are spent on given tasks...some are performed daily, some weekly, some monthly column a represents daily, column b - weekly, column c - monthly so if a task is performed 3 times daily a "3" would be in column a...if it is performed 1 weekly a "1" would be in column b etc it is only counted as either daily, weekly or monthly...never all 3 i want the result returned in column d which is my annual hours spent on this task column so if column a has a 3 in it then column b and c would both be blank i want the formula in column d to look at columns a b & c find which one has a value then take that value and multiply it times 260 if found in column a, 52 if found in column b or 12 if found in column c...am i asking for the impossible? then once i have the annual number of hours, i need to translate that into weekly hours and monthly hours...kind of calculated backwards if that makes and sense so if column d = 18.00 hours...divide that by 52 for the weekly time spent on the task in column e and divide it by 12 for the monthly time spent in column f or if there is an easier way to get this all done i am very open to suggestions. thanks! "T. Valko" wrote: Question 1: Assuming the cells will only contain numbers... Assuming the value is 10... =10*IF(A1,260,IF(B1,52,IF(C1,12,0))) Question 2: not following you on this one! -- Biff Microsoft Excel MVP "se7098" wrote in message ... i have a 2 part question: i need a formula that will look for a value in a particular cell then based on which column it is found in multiply by the corresponding value to return the correct product i.e., look across columns a, b & c...if it finds a number in a multiply by 260 if it finds a number in column b multiply by 52 if it finds a number in column c multiply by 12 there will never be a number in more than one column 2nd question...how can i translate a number into hours broken down by quarters 18 hours*52 weeks = .346...i need .346 converted to hours or rounded up to the nearest quarter hour |
sumif and translate annual hours into weekly quarter hours
Perhaps you missed the bit where I said:
"MROUND Look it up in Excel help." ? -- David Biddulph "se7098" wrote in message ... this is returning an error: #NAME? "Sean Timmons" wrote: Just to clarify, it would be =MROUND(D1/52,.25) and =MROUND(D1/12,.25) "David Biddulph" wrote: MROUND Look it up in Excel help. .... |
sumif and translate annual hours into weekly quarter hours
Now THAT was a thread! Thank you for keeping us informed!
"se7098" wrote: great...perfect...awesome!!! thank you SO MUCH! "Sean Timmons" wrote: Wow.. spelling.. Analysis Toolpak. "se7098" wrote: this is returning an error: #NAME? "Sean Timmons" wrote: Just to clarify, it would be =MROUND(D1/52,.25) and =MROUND(D1/12,.25) "David Biddulph" wrote: MROUND Look it up in Excel help. -- David Biddulph "se7098" wrote in message ... the problem with that is i am being asked to provide the number rounded to the nearest quarter hour so when my annual time is 36 and i divide that by 52 my answer is 0.692...how can i make that auto round to .75? "Sean Timmons" wrote: ans 2 E1=D1/52 F1=D1/12 We were off the tracks there "Teethless mama" wrote: Ans #1: D1 =(A1*260)+(B1*52)+(C1*12) "se7098" wrote: not sure what you mean by value. what i am attempting to do is determine how many annual hours are spent on given tasks...some are performed daily, some weekly, some monthly column a represents daily, column b - weekly, column c - monthly so if a task is performed 3 times daily a "3" would be in column a...if it is performed 1 weekly a "1" would be in column b etc it is only counted as either daily, weekly or monthly...never all 3 i want the result returned in column d which is my annual hours spent on this task column so if column a has a 3 in it then column b and c would both be blank i want the formula in column d to look at columns a b & c find which one has a value then take that value and multiply it times 260 if found in column a, 52 if found in column b or 12 if found in column c...am i asking for the impossible? then once i have the annual number of hours, i need to translate that into weekly hours and monthly hours...kind of calculated backwards if that makes and sense so if column d = 18.00 hours...divide that by 52 for the weekly time spent on the task in column e and divide it by 12 for the monthly time spent in column f or if there is an easier way to get this all done i am very open to suggestions. thanks! "T. Valko" wrote: Question 1: Assuming the cells will only contain numbers... Assuming the value is 10... =10*IF(A1,260,IF(B1,52,IF(C1,12,0))) Question 2: not following you on this one! -- Biff Microsoft Excel MVP "se7098" wrote in message ... i have a 2 part question: i need a formula that will look for a value in a particular cell then based on which column it is found in multiply by the corresponding value to return the correct product i.e., look across columns a, b & c...if it finds a number in a multiply by 260 if it finds a number in column b multiply by 52 if it finds a number in column c multiply by 12 there will never be a number in more than one column 2nd question...how can i translate a number into hours broken down by quarters 18 hours*52 weeks = .346...i need .346 converted to hours or rounded up to the nearest quarter hour |
sumif and translate annual hours into weekly quarter hours
thought you were talking in hex.
"Peo Sjoblom" wrote: It's better than mine 211014.66 <g -- Regards, Peo Sjoblom "Sean Timmons" wrote in message ... Wow.. spelling.. Analysis Toolpak. "se7098" wrote: this is returning an error: #NAME? "Sean Timmons" wrote: Just to clarify, it would be =MROUND(D1/52,.25) and =MROUND(D1/12,.25) "David Biddulph" wrote: MROUND Look it up in Excel help. -- David Biddulph "se7098" wrote in message ... the problem with that is i am being asked to provide the number rounded to the nearest quarter hour so when my annual time is 36 and i divide that by 52 my answer is 0.692...how can i make that auto round to .75? "Sean Timmons" wrote: ans 2 E1=D1/52 F1=D1/12 We were off the tracks there "Teethless mama" wrote: Ans #1: D1 =(A1*260)+(B1*52)+(C1*12) "se7098" wrote: not sure what you mean by value. what i am attempting to do is determine how many annual hours are spent on given tasks...some are performed daily, some weekly, some monthly column a represents daily, column b - weekly, column c - monthly so if a task is performed 3 times daily a "3" would be in column a...if it is performed 1 weekly a "1" would be in column b etc it is only counted as either daily, weekly or monthly...never all 3 i want the result returned in column d which is my annual hours spent on this task column so if column a has a 3 in it then column b and c would both be blank i want the formula in column d to look at columns a b & c find which one has a value then take that value and multiply it times 260 if found in column a, 52 if found in column b or 12 if found in column c...am i asking for the impossible? then once i have the annual number of hours, i need to translate that into weekly hours and monthly hours...kind of calculated backwards if that makes and sense so if column d = 18.00 hours...divide that by 52 for the weekly time spent on the task in column e and divide it by 12 for the monthly time spent in column f or if there is an easier way to get this all done i am very open to suggestions. thanks! "T. Valko" wrote: Question 1: Assuming the cells will only contain numbers... Assuming the value is 10... =10*IF(A1,260,IF(B1,52,IF(C1,12,0))) Question 2: not following you on this one! -- Biff Microsoft Excel MVP "se7098" wrote in message ... i have a 2 part question: i need a formula that will look for a value in a particular cell then based on which column it is found in multiply by the corresponding value to return the correct product i.e., look across columns a, b & c...if it finds a number in a multiply by 260 if it finds a number in column b multiply by 52 if it finds a number in column c multiply by 12 there will never be a number in more than one column 2nd question...how can i translate a number into hours broken down by quarters 18 hours*52 weeks = .346...i need .346 converted to hours or rounded up to the nearest quarter hour |
sumif and translate annual hours into weekly quarter hours
I seem to be massively confused on this so disregard any of my replies.
-- Biff Microsoft Excel MVP "se7098" wrote in message ... closer :) but is rounding up to .70...would like to round to .75...possible? what do i need to change? "T. Valko" wrote: Ooops! Corrections: Formula in E1: =CEILING(D1/52,TIME(0,15,0)) Formula in F1: =CEILING(D1/12,TIME(0,15,0)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Ok, now I get it! Try this: I'm assuming you're entering decimal numbers in A1:C1 Formula in D1: =IF(A1,A1*260,IF(B1,B1*52,IF(C1,C1*12,0))) Formula in E1: =CEILING((MAX(A1:C1)/24)/52,TIME(0,15,0)) Formula in F1: =CEILING((MAX(A1:C1)/24)/12,TIME(0,15,0)) Format E1:F1 as [h]:mm -- Biff Microsoft Excel MVP "se7098" wrote in message ... not sure what you mean by value. what i am attempting to do is determine how many annual hours are spent on given tasks...some are performed daily, some weekly, some monthly column a represents daily, column b - weekly, column c - monthly so if a task is performed 3 times daily a "3" would be in column a...if it is performed 1 weekly a "1" would be in column b etc it is only counted as either daily, weekly or monthly...never all 3 i want the result returned in column d which is my annual hours spent on this task column so if column a has a 3 in it then column b and c would both be blank i want the formula in column d to look at columns a b & c find which one has a value then take that value and multiply it times 260 if found in column a, 52 if found in column b or 12 if found in column c...am i asking for the impossible? then once i have the annual number of hours, i need to translate that into weekly hours and monthly hours...kind of calculated backwards if that makes and sense so if column d = 18.00 hours...divide that by 52 for the weekly time spent on the task in column e and divide it by 12 for the monthly time spent in column f or if there is an easier way to get this all done i am very open to suggestions. thanks! "T. Valko" wrote: Question 1: Assuming the cells will only contain numbers... Assuming the value is 10... =10*IF(A1,260,IF(B1,52,IF(C1,12,0))) Question 2: not following you on this one! -- Biff Microsoft Excel MVP "se7098" wrote in message ... i have a 2 part question: i need a formula that will look for a value in a particular cell then based on which column it is found in multiply by the corresponding value to return the correct product i.e., look across columns a, b & c...if it finds a number in a multiply by 260 if it finds a number in column b multiply by 52 if it finds a number in column c multiply by 12 there will never be a number in more than one column 2nd question...how can i translate a number into hours broken down by quarters 18 hours*52 weeks = .346...i need .346 converted to hours or rounded up to the nearest quarter hour |
| All times are GMT +1. The time now is 04:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com