Free Microsoft Excel 2013 Quick Reference

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...

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...

- Pivot Table data not updating when Report Filter changes
- Why won't pasted values from a formula appear in a pivot table
- Excel Formula to denotes the sum of positives and negatives values separately
- Adding positive and negative hour values (already using 1904 date system)
- Displaying positive and negative numbers
- Pivot table will not return top 10 (Filter)
- Pivot Table will not calculate grand totals
- Pivot Table Will Not Filter A Single Character Item
- Pivot tables and AVERAGEIF function
- Pivot table will not refresh... Excel locks up
- Pivot table will not refresh
- Pivot Table Data Column Sort Excel 2007
- Excel 2007 pivot table does not aggregate properly
- Referencing pivot tables in a macro to perform functions
- Pivot Table issue with weighted average subtotals and grand total
- Sum Positive and Negative Numbers regardless of Sign
- Data Restructuring: Using Pivot Table wo the SUM, COUNT Function?
- Pivot Table Formula - SUMIF
- Pivot table not reading date formats
- Pivot table will not group a date field
- Do Pivot Tables have an automatic data range expansion?
- Pivot tables will not refresh on first try
- Pivot table: How to lock Column Width and Format
- Pivot table will not group a date field

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!

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.

Please refer the attached file for more clarification.

Regards

Pradeep

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,

Thanks,

Sam

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?

thanks.

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

Say:

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!

Cheers,

Dale

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

As an example I ran the following code which worked:

ActiveSheet.PivotTables("Tentatives_Summary_PivotTable").PivotFields("FAMILY").PivotItems("(blank)").Visible = False ActiveSheet.PivotTables("Tentatives_Summary_PivotTable").PivotFields("FAMILY").PivotItems("Test").Visible = False'this works

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

'this does not work

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!

Thanks,

Justin

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

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

Thanks in advance.

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 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!!

Thanks,

Brent

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?

It would be too difficult for me to explain what I need without referencing the file so please see the attachment for clarification.

budget.xlsm

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.

UPDATE:

I found what I needed here:

http://www.databison.com/index.php/h...ble-using-vba/

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.

-1500

8000

-2500

4000

=16000

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?

Hello to all,

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

I have the following:

VB:I want to Transpose the above table to look like the following:code Area code 1 A 709 2 C 709 3 D 709 1 B 645 2 C 645 3 A 645If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB: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).Area Code MemberID 709 645 1 A B 2 C C 3 D AIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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!

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.

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

1:40

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).

Help?

Thanks.

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.

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?

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.

dgott

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.

Help.

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.