Related Posts
What is jpmorgan chase first year VP Salary?
Should I get an mba if I have a Maac already?
Hi Fishes,
How's the hike % ?
Additional Posts in Data & Analytics Consultants
New to Fishbowl?
Download the Fishbowl app to
unlock all discussions on Fishbowl.
unlock all discussions on Fishbowl.
Using countifs() would do
My workflow for doing group by is:
Select multiple columns => copy paste to another sheet => remove duplicates => use averageifs/sumifs etc
For median you can use array functions to do the equivalent of medianIfs.
You'll want to use Pivot tables. It's pretty easy and is similar to group by. Has summary statistics like median, mean, sum etc. built in.
Subject Expert
Pivot tables. You can specify custom aggregations if what you need isn’t available. You can do a shocking amount with pivot tables if you get creative (obviously no ml models). They do seem to get finicky if referencing the same names table which I haven’t wrapped my head around.
My excel workflow is:
- heavy data manipulation in R
- push tables to local sql server
- ingest tables into excel
- pivot tables for end users
The nice thing about this workflow is that you can re-run a script and then simply refresh your excel tables to update your analysis.
You can use power pivot add in for excel which is generally available. This has group by and distinct count. I used this for excel validations
I just wanna say dang I’m jealous lol because I’m the complete opposite. Group bys are a bit different in excel. Are you looking for sumif and sumifs type of thing?
Op I’m with you - I make pivot tables for group bys. Honestly just throw it into python/r
Mentor
And I am an expert at neither. Every now and then, some implementation code keeps me humble
Mentor
Depends on what your goal is but on a general context, wouldn’t recommend. They have specific use cases and there are alternatives to most of them in the industry. Rather learn things like Deep learning frameworks etc
Maybe you can build a python code to do group by on excel data and put it back to excel.
-CountIf
-using filters (data tab)
-excel graph
-if planning on using for awhile import excel to powerbi (use Cloud based excel for real time updates)
I’ve found that the counts and sums by different fields useful for specific questions but that exporting things to graphs gets you to ask different types of questions and thus opens more types of analysis. Just my thoughts on the matter