unmet challenge
 

Okay, I tried both of these posts, but either I've really
come up with something this forum cannot solve (and it's
never let me down before), or it was missed. So here goes
one more time (I'm obviously happy to provide any
additional information needed in order to help get help):

Problem 1:
Have the following grouping:

Reporter
Task
Time Spent

where Time Spent is a calculated field. I want to have
the data for each task to be 1) the time spent on that
task (currently showing) and 2) the percent of the
Reporter's time which that task represented.

I am running into trouble trying the different options
for the second data field I set up (just copied the
formula from Time Spent and called it "Time Spent %").
Thought that there was an easy way to turn the 2nd data
field into a "% of the total of the first grouping's
total" sort of calculation.

Any advice?

Problem 2:

Alright, I am pretty good with pivots, but I cannot
figure out what seems like the simplest thing...

Basic problem is that the total function for one of the
groupings is not giving me an average, despite the fact
that I definitely indicate that to be the summary
function desired. It just gives me a blank in the total
lines.

Basic info:

Fields - End Page (number), Start Page (number), Date,
Start time (time format), End Time (time format)

Formula for data field is "(End Page - Start Page + 1)/
((End Time - Start Time) * 24).

The table groups by person, then task (what activity they
did), then date, with the data area having the field
defined by the formula above. Each individual data point
is calculating the pages/hr (which is what the formula
represents) correctly. The summary line (which is set to
summarize with 'average' function, however, is not
getting me anything other than blank. When it is set to
automatic, it does give me something, but I don't even
know what it's calcuating (not the sum, and not really
any combination I can come up with).

Another point...some of the tasks (which group as the
second level) do not have pages associated with them, but
all have times. So I figured this would not cause error,
since the problem would be if the denominator (which is
based on time) were to be zero (DIV#0 error)

Any insight someone can share?

Boris

Hi Boris

unfortunately i can't find your previous posts so i might be suggesting
things already tried but surely the time spent % for each task would be the
task time / total reporter time as a percentage
if the task time & reporter time is repeated a number of times to get the
task time you'll need a SUMPRODUCT function to get the total reporter time a
SUMIF should do
so with your Reporter name in column A, the Task name in column B and the
Time in column C
=SUMPRODUCT(--(A2:A6="Fred"),--(B2:B6="A"),C2:C6)/SUMIF(A2:A6,"Fred",C2:C6)

not sure about problem 2

Cheers
JulieD


"boris" wrote in message
...
Okay, I tried both of these posts, but either I've really
come up with something this forum cannot solve (and it's
never let me down before), or it was missed. So here goes
one more time (I'm obviously happy to provide any
additional information needed in order to help get help):

Problem 1:
Have the following grouping:

Reporter
Task
Time Spent

where Time Spent is a calculated field. I want to have
the data for each task to be 1) the time spent on that
task (currently showing) and 2) the percent of the
Reporter's time which that task represented.

I am running into trouble trying the different options
for the second data field I set up (just copied the
formula from Time Spent and called it "Time Spent %").
Thought that there was an easy way to turn the 2nd data
field into a "% of the total of the first grouping's
total" sort of calculation.

Any advice?

Problem 2:

Alright, I am pretty good with pivots, but I cannot
figure out what seems like the simplest thing...

Basic problem is that the total function for one of the
groupings is not giving me an average, despite the fact
that I definitely indicate that to be the summary
function desired. It just gives me a blank in the total
lines.

Basic info:

Fields - End Page (number), Start Page (number), Date,
Start time (time format), End Time (time format)

Formula for data field is "(End Page - Start Page + 1)/
((End Time - Start Time) * 24).

The table groups by person, then task (what activity they
did), then date, with the data area having the field
defined by the formula above. Each individual data point
is calculating the pages/hr (which is what the formula
represents) correctly. The summary line (which is set to
summarize with 'average' function, however, is not
getting me anything other than blank. When it is set to
automatic, it does give me something, but I don't even
know what it's calcuating (not the sum, and not really
any combination I can come up with).

Another point...some of the tasks (which group as the
second level) do not have pages associated with them, but
all have times. So I figured this would not cause error,
since the problem would be if the denominator (which is
based on time) were to be zero (DIV#0 error)

Any insight someone can share?

Boris

There's no calculation to show percent of subtotal. Perhaps you could
put Reporter in the row area, and Task in the column area. Then, change
the second Time Spent to % of Row, as shown he

http://www.contextures.com/xlPivot10.html

Instead of calculating hours and pages in the pivot table, you should
calculate them in the source data, then add those fields to the pivot
table. Otherwise, calculations will be based on summaries, instead of
individual records, and the results may not be what you expected.

boris wrote:
Okay, I tried both of these posts, but either I've really
come up with something this forum cannot solve (and it's
never let me down before), or it was missed. So here goes
one more time (I'm obviously happy to provide any
additional information needed in order to help get help):

Problem 1:
Have the following grouping:

Reporter
Task
Time Spent

where Time Spent is a calculated field. I want to have
the data for each task to be 1) the time spent on that
task (currently showing) and 2) the percent of the
Reporter's time which that task represented.

I am running into trouble trying the different options
for the second data field I set up (just copied the
formula from Time Spent and called it "Time Spent %").
Thought that there was an easy way to turn the 2nd data
field into a "% of the total of the first grouping's
total" sort of calculation.

Any advice?

Problem 2:

Alright, I am pretty good with pivots, but I cannot
figure out what seems like the simplest thing...

Basic problem is that the total function for one of the
groupings is not giving me an average, despite the fact
that I definitely indicate that to be the summary
function desired. It just gives me a blank in the total
lines.

Basic info:

Fields - End Page (number), Start Page (number), Date,
Start time (time format), End Time (time format)

Formula for data field is "(End Page - Start Page + 1)/
((End Time - Start Time) * 24).

The table groups by person, then task (what activity they
did), then date, with the data area having the field
defined by the formula above. Each individual data point
is calculating the pages/hr (which is what the formula
represents) correctly. The summary line (which is set to
summarize with 'average' function, however, is not
getting me anything other than blank. When it is set to
automatic, it does give me something, but I don't even
know what it's calcuating (not the sum, and not really
any combination I can come up with).

Another point...some of the tasks (which group as the
second level) do not have pages associated with them, but
all have times. So I figured this would not cause error,
since the problem would be if the denominator (which is
based on time) were to be zero (DIV#0 error)

Any insight someone can share?

Boris


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html