Free Microsoft Excel 2013 Quick Reference

Pivot Table will not sum filtered positive and negative time values together

Dear Friends

I'm in desperate need for help , as I have to deliver a report in the coming two days

I work in healthcare management and I prepared an excel sheet for the time of attendance of physicians compared to the scheduled clinic start time , then I used pivot table to segregate each type of scheduled time alone ( clinics have 4 scheduling times 9, 9:30, 17:00, 16:30) ... to calculate the average delay time compared to each type of scheduled starting time, I reviewed the formatting of the cells and everything , I also used the 1904 timing option so I can calculate negative values too , as sometime the physician will start earlier than the scheduled time, so here is the thing ... when pivot table extracts the data , they are accurate , but when it adds them together it comes up with a larger number as shown in the pivot table page in the attached excel sheet , the cells shows accurate numbers but the subtotals are extremely large numbers compared to the cells ...... and the weird thing is ..... this happen for certain clinic starting time , while the other clinic times appear accurate and calculate accurately as subtotals too ... Help plz

I also noticed that the problem is when there are both positive and negative values ......... pivot table has a problem calculating averages from positive and negative time values .... and when I manipulated the data so that no result will be negative , the calculation turns out fine...

Post your answer or comment

comments powered by Disqus
In Excel 2007, I have a workbook set up that updates the report filters of multiple pivot tables based on a drop down selection (created from a Data Validation list). I use the following to change the filter on certain triggers:

Where SheetVar, PivotVar, and MSOVar are set previously.  SheetVar and PivotVar are based on a table I've made which contains
names of the sheets and pivots in the workbook and I use some logic to determine when I want certain sheets and pivots
updated.  MSOVar is set equal to the value in the data validation list I mentioned.

The problem I am having is that every once in a while, the code will run to change the MSO_NAME filter, everything will seem to go fine, the filter will change, but the data in the actual data portion of the pivot table will not change. I've verified that there is data for both the MSO_NAME that I'm changing from, and the one I'm changing to, so there should be data populated.

I have also tried refreshing the pivot, changing the filter manually, and changing the data source to the same data source to see if it would update the data and none of those worked. The only thing I could get to work was to create a new pivot table and recreate exactly what I had before, when I did that it would display the proper data.

Has anyone ever run into a similar issue? Does anyone have any suggestions as to what I might be able to do to prevent this problem, or even reproduce it? I can't seem to figure out what makes it happen as I have multiple pivots in the same workbook and it only happens to some of them some of the time.

Thanks a ton!

I am trying to make a pivot table from an array of information that was the
result of formulas on another worksheet page. I used a macro to copy these
formulas on the other worksheet page, then paste their values in the array
that I need a pivot table from. The pivot table will not sum any of the
values in the array and it does not combine cells in one column that are
alike if their corresponding cells in the adjacent column are different.
Everything works if I retype the data instead of copying and pasting. This
is not an option becuase there are several thousand lines of data. Thanks in
advance for any help.

I have a file having SLoc,Part Code and amount ( both positive and negative ).I want the sum of storage location's amount separately from an excel formula.Means,positive value of all location should come in a column and negative value should come in another column.
Please refer the attached file for more clarification.



Hello all,

I've done a search for my specific problem but couldn't find it so i thought i'd start a new thread.

I'm creating a spreadsheet to monitor the hours of part time workers in my office. I'm using the 1904 date system to allow negative time values and have formatted my time cells with "[h]:mm:ss".

I'm wanting to be able to total time values, even when some of them are negative, e.g. -1.5 hours + 3 hours = 1.5 hours. I have it working correctly for adding two positive values.

Some of the workers are allowed to work extra one week, and then less the next, or visa versa, therefore i want to be able to track this and total it both weekly, and at the end of the month.

I also have to factor in whether they have taken any holiday time that week, therefore it might be that a worker is due to work 20 hours next week, but has one day's holiday (4 hours) and therefore works 16 hours + 4 hours holiday.

I have a forumula that is working for this:

=(TEXT(G2-(M11-N11),"[H]:MM:SS")) (where G2 contains the normal working week hours, M11 contains hours owed that week and N11 contains the holiday hours taken)

However, if M11 is negative (e.g. a 20 hour a week person works 25 hours one week, so m11 (time owed) is -5 hours) then the formula errors with #VALUE!

Any help would be greatly appreciated,



i though it'd attach a copy to clearly illustrate the problem. If you change cell i11 from 4(hours) to 5(hours) you'll see the issue described above.


I have 2 cells called overtime and undertime. Overtime is of the format hh:mm.

Undertime is of the format -hh:mm.

I want to display a running total from week to week of the positive and negative numbers added together.

For example if the overtime for one week was 4 hours and for week 2 I was undertime by 2 (so its minus 2) the running total at the end of week 2 should be +2.

How can I do this?


Heya there boys and girls.

Im hoping someone can help me out, this has me pulling my hair out and no one in my office can help me.

A2:A300 - sales numbers ranging from $1,700,000 to $18,000,000
B2:B300 - sales managers
C2:C300 - customers
D2:D300 - states/prov

I have a pivot table in that place shows:

The sales manager, then the states, the customers within the states and the sales figures.
Simple, and easy enough.

All this sits within row labels, except sales numbers, they sit in both row labels and values because if it is not, when i ask excel to show me the top10 it produces an error. Within values the sales number are told to display the sum not the count.

When i click on the sort button of the sales numbers under the row labels, click Value Filters, Click Top 10, tell it to sort top 10 based on SUM and click OK, nothing happens.

However if i click value filters and select something like less then and say less then 5,000,000 everything over 5,000,000 just dissapears from the list! It makes no sence from what i can see!

ANY HELP AT ALL! Greatly apprecaited!


I have a pivot table that will not calculate the grand totals of rows or columns. The appropriate boxes are selected under the Table Options and Field Options to show the grand totals.

Example data below. I would like the GT to show for each inv, as well as an overall grand total for the complete sheet. For this one the GT should be 960, I on get zero.

Cur SupplierSupplier Inv.DocumentDoc. DateAmount Balance CAD 9MA72496API 2124354124-Jul-074800API 21243541 Total0072496 Total0073362API 2124611320-Aug-074800API 21246113 Total0073362 Total009MA Total00CAD Total00Grand Total00

I am coding in excel 2007 vba to create a pivot table based on data on the tab. Although my code is more complicated than the code below, I believe I have narrowed my problem down to the fact that whenever I attempt to filter a single character value I encounter a Run-time error '1004': Unable to get the PivotItems property of the PivotField class.

As an example I ran the following code which worked:
= False 
= False
'this works

However, when filtering for "J" I encounter the run-time error '1004':
'this does not work

Hi guys,

Newbie to the forums and also fairly new to lookup tables.

I have a sheet of data related to an audit - it is related to meds and weight loss, but there are a lot of other columns of related data. There are a number of drugs, and the weight loss is either a positive or negative number depending if it was lost or gained. I would like to average the positive and negative numbers seperately so I can see what the average loss was if people lost, or the average gain if gained. I was able to do this on the original sheet fairly straightforwardly with the AVERAGEIF function with the criteria ">0" for positive values and "<0" for negative values.

I am more interested in seeing the average weight loss/gain for each of the drugs. For other parameters a straightforward pivot table worked but I cannot see a way to use a pivot table to average only the positive or negative values.

Is there any way of incorporating the AVERAGEIF, or a similar function, into a pivot table, or is there some other way of working around this?

I hope that this is clear and I hope that someone can help - trawling through hundreds of entries manually would not be pleasant!


P.S. can't upload original file but can make a dummy one if it will help.

Not sure what is happening. When I cick "Refresh Data" on my pivot table, the window pops up that says Excel has encountered a problem and must close, and whether I want to report the problem to Microsoft.

Is this a technical issue? Something wrong with the Pivot table, or something with my software? Has anyone ever encountered this?

Thanks in advance.

I use Excel 2003 and work with pivot tables on a daily basis. I did not
realize they were supposed to be able to refresh until a co-worker told me
about this. I am not able to make this work. She is not able to make this
work at my computer. Is there something obvious that I am not doing right???

I am using Excel 2007.

I have a table in one field that is the source for my Pivot Table.

First under Pivot Table Options on the Totals and Filters Tab I have
the Use Custom Lists when sorting disabled.

The Pivot table has three columns:

Customer, Customer Num, Gross Sales.

Customer and Customer Num are in the row labels portion of the pivot
field list and Gross Sales is in the Value portion.

Under the Layout & Print tab of the Field Setting for Customer and
Customer Num are set to show item labels in tabular form.

I click on PivotTable tools on the ribbon then on the Sort Icon. The
Sort By Value window shows Largest to Smallest under sort options and
Sort Customer Num by Gross Sales in descending order.

Despite all of this my pivot table will not sort by the Gross Sales in
either Largest to Smallest or Smallest to largest.

When I select a cell in the Gross Sales column and click on the sort
buttons (A -> Z and Z -> A) in the Pivot Table Tools ribbon, the
screen blinks as if the pivot table is being recalculated but the data
is not sorted. As a result I am having to copy the pivot table data
and paste elsewhere to sort it.. what a pain!!



I am using a an excel pivot table to summarize some data and just found a problem. The problem deals with how aggregate values are calculated. Let's say I have a table of data with three columns: Name, Date, Value. If I create a table where Name and then Date are used as Row Labels and Value is the aggregate value, ie Average. The pivot table will look something like this:

HTML Code: 
+John              .3450 
   5/14/2010       1.234 
   5/15/2010       3.450 
   5/16/2010       -3.25

What I think should be happening here is that the values for each date are averaged and then those values are averaged to come up with the value in the same row as the Name, John. But that is not what it does. It takes the average for each date, which it shows across from the date, but then instead of taking the average of those numbers, it actually uses the raw data and computes the average for all of John's values. It should show the average of the daily averages to correspond with the tree hierarchy, but instead just shows me the average for all of John's values. It essential will only aggregate at one level, but visually creates sub levels that it is not using. Does anyone know how to change this or understand by what logic this makes sense? Why would I create any sub groupings if I cannot compute aggregates on them?

I have been working on a budget spreadsheet for a while now and I have run in to a problem that I do not know how to solve.
It would be too difficult for me to explain what I need without referencing the file so please see the attachment for clarification.

I have a macro named "Analyze" which puts all of my data in to a pivot table so I can see where I'm at with my money.
You can activate the macro by clicking the "Analyze" button on the "Budget" sheet.

There are three things that I want to accomplish;

A: There is a hidden row that i use as a placeholder for formatting that I do not want to include in the data for the pivot table. How do I exclude this row (the position of this row is constant)

B: I want to calculate and display the monthly totals (all deposits - all expense for the given month)

C: I want to calculate the average monthly total

Is there a way to reference data in a pivot table so I can do the necessary calculations?
or do I need to do this outside of the pivot table? If so any thoughts on how to do this?

Keep in mind the number of transactions and the number of months are variable so my pivot table will not remain the same size.

I found what I needed here:

I am using Excel 2007.

I have a table of data which sets up the framework for including the weighted average in a pivot table. I have a column called wocc where the data is NULL, or blank in Excel, if occ * principal = 0, otherwise the value in the field is occ * principal.

In the pivot table I have a calculated field called socc with a formula of =IF(curr_principal=0,0,wocc/curr_principal)/100

The problem is, the resulting pivot table returns 0% in those instances where wocc was blank in the source data. As a result, the subtotal and grand total weighted average life is incorrect because those values are including the 0%. I need the pivot table to not change the blank source data to 0% so that the subtotal and grand total will show the correct amounts.

I have a colum of positive and negative numbers I want to sum all the numbers regardless of the sign. Example
I can not work in another colum or covert the negative numbers in another colum then add them up.
I need last cell to read 16000. What formula do I need?

Data Restructuring: Using Pivot Table wo the SUM, COUNT Function?

Hello to all,

I'm just wondering if someone could help me with this issue.

I have the following:

 code     Area code 
1                     A             709 
2                     C             709 
3                     D             709 
1                     B             645 
2                     C             645 
3                     A             645 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I want to Transpose the above table to look like the following:

Area Code 
MemberID      709             645 
1                   A                B 
2                   C                C 
3                   D                A 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I've tried using a pivot table but I cannot get around the issue whereby when I drag over the Call code column into the Data space it automatically wants to carry out a Function on my text data (call code column).

What I hope to do is use the pivot table technique to just click & drag my text data over as is, without having the pivot table try and calculate some function.

Is this possible within Excel? Or should I be using another procedure within Excel to accomplish this?

Thanks very much for any help I can get!

I have a table that contains the following headers:

Type of Discrepancy, State, Month and Amount

The Amount column contains positive and negative numbers to indicate overages and shortages.

My pivot table looks like this:
- ROW: Type of Discrepancy and State
- COLUMN: Month
- DATA: Count of Discrepancy, Sum of Amount

Due to the amount field containing both positive and negative values, my total is inaccurate. What is the best way to sum the positive and negative values seperately to get a true total?

Thank you.

I am building a pivot table report to sum a worksheet that has times recorded
in it. The data is formatted like this

Start Finish
Total Time
6/14/2006 12:00:00 AM 6/14/2006 1:40:00 AM

I want the pivot table to sum by month.

I can't figure out how to get this done. I put a column beside the data to
pull the date from the start times for the table. But I can't find a format
or formula that the pivot table will read the month from (other than retyping
the dates).



I have a pivot table that I grouped the dates by month. I've been using it
for quite a while. I just refreshed it and the dates came ungrouped.And when
I select the date field and group, I get an error message saying "cannot
group that selection.

As I add new rows of data to my data base, my pivot table will not expand to
include them after refreshing. Is there any way of setting the pivot table
to include the entire database regardless of its continuously growing size?
Right now, as I add a new row of data I must delete my old Pivot table and
create a new one from scratch. Is this normal?

I have a macro code that runs a MSQuery returning a table of data to a specified worksheet. I have a pivot table (on a separate worksheet) that uses that table as source data. The same macro that runs the MSQuery also refreshes the pivot table. For some reason the macro must be run twice to get the pivot table to refresh.

The table of data is imported just fine on the first run. Not sure what the issue is. I have tried multiple variations of code to refresh the pivot tables. I even put the code in twice.

Thanks for any help.

Hi team,

I have created a pivot table that has a filter which I use to view different data. I have created a final version of this pivot which has some format cosmetics (nothing too crazy). My problem is that whenever I use the filter to change the criteria, the data changes but so do the width of my columns (13 columns in this pivot YTD in months labeled across). Therefore, some numbers appear smudged up like #### and I have to manually re-adjust the column width everytime this happens. Is there a way to lock the column width so no matter what filter is set the column width locks.

Also out of nowhere labels suddenly become underlined. Im like ok? I never underlined this so let me highlight and disable the underline. Filter switch again, boom the underline re-appears.


I have a pivot table that I grouped the dates by month. I've been using it
for quite a while. I just refreshed it and the dates came ungrouped.And when
I select the date field and group, I get an error message saying "cannot
group that selection.

No luck finding an answer? You could always try Google.