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.

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]

Want to read more?

Check out the full article on the original site

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