Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Pivot Table Calculated Item
I am querying an external database to retrun a pivot table, then i am adding
a calculated field that calculates Regular hours * Bill rate. to come up with total revenue. When it runs, the calculated amounts are 2, 3, 4 times more that what it should be. any ideas how i can get this calculation to return the right amounts. thanks Jerry |
#2
|
|||
|
|||
Pivot Table Calculated Item
H5 erry
Excel stores hours as fractions of a day, so multiply Hours * 24 * Bill Rate Regards Roger Govier jerry wrote: I am querying an external database to retrun a pivot table, then i am adding a calculated field that calculates Regular hours * Bill rate. to come up with total revenue. When it runs, the calculated amounts are 2, 3, 4 times more that what it should be. any ideas how i can get this calculation to return the right amounts. thanks Jerry |
#3
|
|||
|
|||
Pivot Table Calculated Item
Hi Terry
Excel stores hours as fractions of a day, so multiply Hours * 24 * Bill Rate Regards Roger Govier jerry wrote: I am querying an external database to retrun a pivot table, then i am adding a calculated field that calculates Regular hours * Bill rate. to come up with total revenue. When it runs, the calculated amounts are 2, 3, 4 times more that what it should be. any ideas how i can get this calculation to return the right amounts. thanks Jerry |
#4
|
|||
|
|||
Pivot Table Calculated Item
Roger, That did not seem to work. Maybe a little more information would help.
I believe the challenge is the in the way pivot tables summarizes the multiplies. For example 10/1/05 task 1 = 2.00 hours 10/1/05 task 2 = 3.00 hours 10/3/05t task 3 = .50 rate 50.00/hour thus,(2.00+3.00+.50=5.5 * 50.00) * 3 Instead what i am looking for in the above example is 5.5 hours *50.00/hr. Any idea, how i can accomplish this? thanks Jerry "Roger Govier" wrote: Hi Terry Excel stores hours as fractions of a day, so multiply Hours * 24 * Bill Rate Regards Roger Govier jerry wrote: I am querying an external database to retrun a pivot table, then i am adding a calculated field that calculates Regular hours * Bill rate. to come up with total revenue. When it runs, the calculated amounts are 2, 3, 4 times more that what it should be. any ideas how i can get this calculation to return the right amounts. thanks Jerry |
#5
|
|||
|
|||
Pivot Table Calculated Item
Hi Jerry
As I said originally, if the times are 2:00, 3:00 etc., whilst this is the displayed value (either in the source data or the PT summary), Excel stores all time values internally as fractions of a day. To convert to decimal hours, you need to multiply by 24 =(2:00+3:00+0:50)*24*50 = 291.6667 You need to format the cell with the formula as General. In your example, you show one time as 0.50. Do you mean 0:50 or 50 minutes as opposed to half an hour? If so then the above applies. If you truly mean .50 and your source times are decimal hours then the total would not need to be multiplied by 24. Regards Roger Govier jerry wrote: Roger, That did not seem to work. Maybe a little more information would help. I believe the challenge is the in the way pivot tables summarizes the multiplies. For example 10/1/05 task 1 = 2.00 hours 10/1/05 task 2 = 3.00 hours 10/3/05t task 3 = .50 rate 50.00/hour thus,(2.00+3.00+.50=5.5 * 50.00) * 3 Instead what i am looking for in the above example is 5.5 hours *50.00/hr. Any idea, how i can accomplish this? thanks Jerry "Roger Govier" wrote: Hi Terry Excel stores hours as fractions of a day, so multiply Hours * 24 * Bill Rate Regards Roger Govier jerry wrote: I am querying an external database to retrun a pivot table, then i am adding a calculated field that calculates Regular hours * Bill rate. to come up with total revenue. When it runs, the calculated amounts are 2, 3, 4 times more that what it should be. any ideas how i can get this calculation to return the right amounts. thanks Jerry |
#6
|
|||
|
|||
Pivot Table Calculated Item
Roger,
thanks for your responses thus far. However, I guess i am not getting the right info to you. First to answer your question. The source time is in decimals(.50 = 1/2 hour). Next, I have sumbitted time by various resources. John smith Task 1(i.e.documenting) - 10/1/05 3 hours 50.00/hr Task 2 (i.e. development) - 10/2/05 2 hours 50.00/hr Task 3(i.e. development) - 10/3/05 .5 hours 50.00/hr in the above scenario if i do not include the task name and the date in my pivot table,the hours are summarized by resource(john smith) multiplied by the rate then multiplied again by the number of tasks for the week. thus the above would calculate as follows (5.5 * 50) *3 = 825.00 vs what i am hoping the result would be is that no task and date beign required in the pivot table and the result would be = 275.00 hope this add some more clarity. Sorry about the confusion. thanks Jerry "Roger Govier" wrote: Hi Jerry As I said originally, if the times are 2:00, 3:00 etc., whilst this is the displayed value (either in the source data or the PT summary), Excel stores all time values internally as fractions of a day. To convert to decimal hours, you need to multiply by 24 =(2:00+3:00+0:50)*24*50 = 291.6667 You need to format the cell with the formula as General. In your example, you show one time as 0.50. Do you mean 0:50 or 50 minutes as opposed to half an hour? If so then the above applies. If you truly mean .50 and your source times are decimal hours then the total would not need to be multiplied by 24. Regards Roger Govier jerry wrote: Roger, That did not seem to work. Maybe a little more information would help. I believe the challenge is the in the way pivot tables summarizes the multiplies. For example 10/1/05 task 1 = 2.00 hours 10/1/05 task 2 = 3.00 hours 10/3/05t task 3 = .50 rate 50.00/hour thus,(2.00+3.00+.50=5.5 * 50.00) * 3 Instead what i am looking for in the above example is 5.5 hours *50.00/hr. Any idea, how i can accomplish this? thanks Jerry "Roger Govier" wrote: Hi Terry Excel stores hours as fractions of a day, so multiply Hours * 24 * Bill Rate Regards Roger Govier jerry wrote: I am querying an external database to retrun a pivot table, then i am adding a calculated field that calculates Regular hours * Bill rate. to come up with total revenue. When it runs, the calculated amounts are 2, 3, 4 times more that what it should be. any ideas how i can get this calculation to return the right amounts. thanks Jerry |
#7
|
|||
|
|||
Pivot Table Calculated Item
Hi Jerry
Email me directly with a copy of your file and I will see if I can sort it out. Remove NOSPAM from my email address to sen direct. Regards Roger Govier jerry wrote: Roger, thanks for your responses thus far. However, I guess i am not getting the right info to you. First to answer your question. The source time is in decimals(.50 = 1/2 hour). Next, I have sumbitted time by various resources. John smith Task 1(i.e.documenting) - 10/1/05 3 hours 50.00/hr Task 2 (i.e. development) - 10/2/05 2 hours 50.00/hr Task 3(i.e. development) - 10/3/05 .5 hours 50.00/hr in the above scenario if i do not include the task name and the date in my pivot table,the hours are summarized by resource(john smith) multiplied by the rate then multiplied again by the number of tasks for the week. thus the above would calculate as follows (5.5 * 50) *3 = 825.00 vs what i am hoping the result would be is that no task and date beign required in the pivot table and the result would be = 275.00 hope this add some more clarity. Sorry about the confusion. thanks Jerry "Roger Govier" wrote: Hi Jerry As I said originally, if the times are 2:00, 3:00 etc., whilst this is the displayed value (either in the source data or the PT summary), Excel stores all time values internally as fractions of a day. To convert to decimal hours, you need to multiply by 24 =(2:00+3:00+0:50)*24*50 = 291.6667 You need to format the cell with the formula as General. In your example, you show one time as 0.50. Do you mean 0:50 or 50 minutes as opposed to half an hour? If so then the above applies. If you truly mean .50 and your source times are decimal hours then the total would not need to be multiplied by 24. Regards Roger Govier jerry wrote: Roger, That did not seem to work. Maybe a little more information would help. I believe the challenge is the in the way pivot tables summarizes the multiplies. For example 10/1/05 task 1 = 2.00 hours 10/1/05 task 2 = 3.00 hours 10/3/05t task 3 = .50 rate 50.00/hour thus,(2.00+3.00+.50=5.5 * 50.00) * 3 Instead what i am looking for in the above example is 5.5 hours *50.00/hr. Any idea, how i can accomplish this? thanks Jerry "Roger Govier" wrote: Hi Terry Excel stores hours as fractions of a day, so multiply Hours * 24 * Bill Rate Regards Roger Govier jerry wrote: I am querying an external database to retrun a pivot table, then i am adding a calculated field that calculates Regular hours * Bill rate. to come up with total revenue. When it runs, the calculated amounts are 2, 3, 4 times more that what it should be. any ideas how i can get this calculation to return the right amounts. thanks Jerry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a Pivot Table Calculated Field to get a Unique Count | Excel Worksheet Functions | |||
Pivot Table Calculated field item | Excel Discussion (Misc queries) | |||
Pivot Table Calculated Item | Excel Discussion (Misc queries) | |||
Pivot Table Calculated Item | Excel Discussion (Misc queries) | |||
How to create a calculated field formula based on Pivot Table resu | Excel Discussion (Misc queries) |