•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Pivot Table Trouble; outputting equal numbers despite that not being the case
Our take
Creating a pivot table can be a powerful way to analyze data, but it can also lead to confusion, especially when the results seem inconsistent. In this case, the pivot table is displaying identical counts for various animal behaviors across different categories, despite the underlying data indicating otherwise. This discrepancy raises questions about the setup and functionality of the pivot table. Let's explore how to troubleshoot this issue and ensure your analysis accurately reflects the observed interactions in your dataset of 1,535 rows.
The current setup for my pivot table.
Hey all,
I'm looking to create a pivot table to help me count how many of a set of animal behaviors were observed in some videos. My initial spreadsheet has dropdowns for the sex of the individual (male, female, female with pup, unknown), dropdowns for who is the "sender" and who is the "receiver" (some include 'both' for spontaneous starts), and dropdowns for the presence or absence of each behavior. There's also drop downs for no instances of aggression within our subsamples (to separate out the "didn't happens" from the "no interactions").
The problem comes when I try and create a pivot table that counts how many of each behavior happened with each sex; interactions for female senders x male, female, female with pup, and unknown receivers, and so on. The pivot table itself uses the same counts for each behavior, even though every interaction did not involve all of the same behaviors; while it may be a coincidence that a given behavior is exhibited the exact same number of times, all of them being the same number across the columns for every single row is giving me pause and is making me think I did something wrong.
Is there any way I could make this pivot table work (this is my first time trying one out)? Would there be a better solution to my problem? Thanks in advance!
Quick edit: I am in Excel 2021.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Sum unique values in Pivot Table without Data ModelI have a spreadsheet that tracks the surgical cases our practice sees. In this spreadsheet, each case is its own row. Column A is the date. Column B is the primary doctor assigned to the case. Column C lists which doctors are present at the practice that day (ex: "Dr.X" or ""Dr.Y" or "Dr.X/Dr.Y"). The first case of the day gets a "1" in Column D. I am trying to use pivot tables to create a summary of some metrics for the surgeries. Part of the stats we look at are how many surgeries a doctor performed when there are two working that day vs just one. The issue I'm running into is when I try to run statistics by the month, filtered by Column B and Column C, I cannot get an accurate count of the number of days a doctor worked that month. If I want to run stats for Dr.X on days both doctors worked, Column D only gets added if Dr.X was assigned to the first case of that day. I know that you can add unique values by using the Data Model feature, but this isn't available on Mac. Is there any other way I can calculate this? submitted by /u/anarchybats [link] [comments]
- Pivot Table Show Details not working, any idea?Hi there, I am facing with a quite unique issue I think. There are some pivots in an Excel file that we use for internal reports for the AP team. The issue is that for the pivots there are several filters applied on, like Intercompany filter or "is the amount negative" etc. but there is the most important one is the Vendor filter, there are numerous vendors excluded, and I was like okay let me just recreate the pivot from the ground, but that would take a bit too much due to the vendor filter. The main problem is that the show details is not working, if I execute a double click on the pivot's total cells then it is loading for a 0.1 second and then nothing happens. If I try it after I close and reopen the file, then it loads for 2 seconds, with the botom right text saying "Reading Data" but then nothing, same results. It is a connection based pivot table, I tried to copy and paste into a new sheet the Pivot, didn't work, I tried to Save & Repair the file, it didn't work. Any idea? submitted by /u/Strange_Cell1142 [link] [comments]
- Creating Pivot Table from Multiple SheetsHi All, I'm working on a large tracking workbook, consisting of several clinical trials in order to track by patient detail the payments we are owed by the funder, what we have received, and the difference. All these payments are delayed by 3m-2 years in some cases and leadership wants to accurately predict how much we are owed. I think what where I'm running into issues is that while I did standardized as much as I could, there are still several columns for each study that don't apply to other studies. I.e. some studies have different arms they could be enrolled in, some are just a 1 time enrollment payment, others have several milestones that can receive payments. But every sheet has roll ups that are standardized that I need in the Pivot Table. Those being: Protocol Randomized Date Federal Accrued Foundation Accrued Industry Accrued Supplement Accrued Federal Received Foundation Received Industry Received Supplement Received Total Owed The Accrued and received columns sum the individual payments into those buckets, that way we can go back to the funder and ask specifically what we are missing for to see if they missed paying us for that milestone specifically. When I tried pulling all these sheets into Power Query, I was able too, and aggregated all the sheets into one via Power Query. Then I tried to pull that aggregate into a pivot table. No Pivot table Loaded and all I got was "load to data model failed" on each queries. Am I asking for too much? Can I get rid of the extra columns in Power Query that do no align together with ruining the data that is being pulled in by formulas. I have if statements pulling into the table for the individual, study specific milestones, from a separate table that automatically helps us track payments accrued, and the "standard columns" have sums formulas that sum the columns that apply to them from the individual milestone columns. The milestone, study specific received columns are entered in manually and have no formulas, but are rolled up into the standard columns just like in the accrued side. And the total owed column is also a formula of the standard accrued and received columns. The goal of pulling this into a pivot table is so we can give high level data to leadership to actually start tracking how much we are owed, given the constant delay in payments, and to have a real sense of the deficit this specific program runs year to year. This way they can accurately plan for the yearly "donation" from other sources of funding in the department. If you made it through this post, thank you! Any help is appreciated. I'm using Excel 365. submitted by /u/Melodic-Pollution-91 [link] [comments]
- Trouble using pivot tables to calculate field and subfield values.I am having trouble getting a clean summary of our monthly stats for a surgical practice I work for. I am using a pivot table connected to the surgical case spreadsheet. In the spreadsheet, each row represents an individual case. I am struggling to get these values in the same field: 1) Monthly total of days the practice performed surgery 2) Subfields showing Monthly total of days each doctor performed surgery The issue is that I cannot figure out how to calculate both of these in the same field. "Sum of Days Worked" returns the correct monthly total, but the wrong total of days for that individual doctor. This is because a "1" gets placed in this column next to the first surgery of the day. So, that value only gets assigned to the doctor who performed the first surgery of the day. "Sum of Dr Day" returns a "1" on the first surgery each doctor performed that day. This calculates the correct total of days each doctor performed surgery, but the monthly total is off, because if there are more than one scheduled per day, it counts the day twice. I understand I can achieve the desired totals with regular formulas, but we are constantly having new doctors join and leave our practice, so I prefer pivot tables because I don't have to continuously adjust formulas. Google tells me "Add this Data to the Data Model" is helpful for this problem, but we only use Macbooks at work. Any help is appreciated- this is driving me crazy. submitted by /u/anarchybats [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#natural language processing for spreadsheets#financial modeling with spreadsheets#rows.com#Excel compatibility#real-time data collaboration#Excel alternatives#big data management in spreadsheets#modern spreadsheet innovations#machine learning in spreadsheet applications#enterprise-level spreadsheet solutions#digital transformation in spreadsheet software#conversational data analysis#row zero#collaborative spreadsheet tools#AI-driven spreadsheet solutions#cloud-based spreadsheet applications#automation in spreadsheet workflows#intelligent data visualization