Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Divide Ranks into two teams (mathematical guru challenge) | Excel Discussion (Misc queries) | |||
Data Validaion Challenge | Excel Worksheet Functions | |||
CHALLENGE! - USING IF MAX MIN AND LOOKUP TOGETHER | Excel Worksheet Functions |