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
|