2 min readfrom Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

Trouble using pivot tables to calculate field and subfield values.

Our take

If you're facing challenges with pivot tables while summarizing monthly surgical statistics, you're not alone. Many users encounter difficulties when trying to calculate both the total days surgeries were performed and the individual contributions of each doctor. The key is to balance the need for accurate totals across multiple doctors while maintaining flexibility in your analysis. By leveraging pivot tables effectively, you can simplify this process without constantly adjusting formulas, making it easier to adapt as your practice evolves.

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]

Read on the original site

Open the publisher's page for the full experience

View original article

Tagged with

#Excel alternatives for data analysis#generative AI for data analysis#natural language processing for spreadsheets#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#collaborative spreadsheet tools#AI-driven spreadsheet solutions#cloud-based spreadsheet applications#real-time data collaboration#automation in spreadsheet workflows#intelligent data visualization#no-code spreadsheet solutions#AI-powered spreadsheet#data visualization tools#enterprise data management#big data performance