Expert at SQL and python but not very good at excel... Things like group by I honestly can't figure out how to do in excel. Is there a different way of thinking I should approach excel? Ex: doing group by with a count (*) - I know pivot tables can basically do this, but I feel like doing 2 formula columns in a separate tab would be faster

likehelpful
Posting as :
works at
You are currently posting as works at

Using countifs() would do

like

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.

like

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.

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.

like

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

helpful

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

And I am an expert at neither. Every now and then, some implementation code keeps me humble

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

helpful

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

Related Posts

There are 3 in NYC (Manhattan), Lex, 8th Ave, East 16th St.

Please provide your opinion;
Persistent systems or KPMG?

like

Expats- how the hell did you do it? I’m a US citizen from birth and all I have wanted for the past decade is to get TF out of here. Preferably EU, Canada (I speak fluent French) or Australia/New Zealand, but I’d also be down for some parts of Asia. I’ve worked for global companies in the hopes that there would be a transfer opportunity, but the opportunity has never come up. Any tips would be awesome.

likehelpful

What is jpmorgan chase first year VP Salary?

like

Remote: Senior G/L Accountant - StockX - est. $80K+ - 7yrs exp - CPA a plus

likesmart

Refering to the history of compensation release dates

Post Photo
like

Hard to focus on anything else when wearing a Lange...

Post Photo
likeuplifting

Currently in 2 months into Tech Risk, have worked on three IT audit engagements but trying to get onto an IT Resilience project to try something new. Is this going to be any more interesting than working on ITGCs?

likeupliftingfunny

For those who already have a baby - I (36f) just had a baby 6 weeks ago, and my wife (33F) is now back at work (WFH) and I’m still on leave. Since I have the baby all day and get up with her all night, my wife basically “takes over” baby duty when she stops working. The problem is that within an HOUR she has lost all patience with the baby. She gets frustrated so quickly if the baby is crying and will say things like “I’m just going to let her cry since nothing im doing helps” (cont)

like

Should I get an mba if I have a Maac already?

like

Hi everyone,

I am currently looking forward any remote job related to project/program management.
Do you know some?

PD: I'm not based in USA.

like

Looking for a job in incident management or Majar incident management team. Currently working in TCS for 3.5 yrs as a Network operator/Network analyst/Incident manager.

like

Hi Fishes,

How's the hike % ?

like

I joined a company yesterday. I got another offer the next day from a very good company.
The joined company has not shipped the laptop yet. Just onboarding calls are over. Can I abscond and join with a new offer? What are your suggestions? I really want to join a new offer as it's a good company.

like
like

Which stock broker is good to choose between fidelity investments vs Morgan Stanley to get Amazon rsu.

An Amazon employees working from Amazon India.

like

Has anyone taken a BRYQ assessment? Wondering if anyone has any tips that would be helpful before I take the assessment?

like

Hello All,
Anyone working in Cognizant who can refer me?
Tech stack-Front end developer, React js
Experience relevant-3.5

like

How’s your work-life balance? New job at a new company and wondering how to clarify standards on this

like

Additional Posts in Data & Analytics Consultants

Has anyone here taken the Nike SQL assessment via HireVue? Wondering what to expect.

like

Considering a data and analytics role for a data migration project. What kinds of SQL queries will I be expected to know?

like

Wanting to get out of fed space. I am a data scientist with 5YOE. What are good companies to look for in DC area that’d hire someone with gov consulting experience?

like

I am a beginner DA user and trying to gain experience. I am trying to do a project on ESG for fun, and having some difficulty coming up with a hypothesis. Want to pick your brains and see if I can get started. I have data about CO2 emisiones, electricity usage, air quality, and that’s pretty much it. Using alteryx and powerbi (to visualize results) as I don’t have any other programs. Thanks so much!

like

Hey all. Question regarding Presto SQL and Arrays.

I’m working within a Presto DB and the column contains a nested array. I need to pull out the 3rd item of each array. Does anyone got any info on how to approach this?

like

If you’re hiring for sr analyst roles , what tradeoff would you be comfortable ? Business acumen and prior knowledge of industry/ function or core data analytics skills ? Need both at end of day but candidates are strong in only one of the two areas. Assume candidates are coachable and willing to learn.

like

What’s the coolest data solution you have delivered? The industry is getting a little predictable and some inspiration is always welcome.

like

Does anyone work for a company that is paying for them to get an MS in Data Science or Business Analytics? If so, where?

like

What’s the salary range for DS position at Walmart? At the end of interviews but would like to know what I’m expecting. Walmart

like

I am finishing up a project and I’m trying to document what I’ve done for my resume. What are some good metrics for someone who mainly created user stories, created dashboards, interacted with the client and developed statistical models? One of my dashboards got deployed to production but it’s to early to see the impact. My other dashboards are almost done but I’m moving on to something else.

like

Any recommended courses or books on customer care/cust success analytics or marketing analytics in general?

like

I studied for 2 months with a friend and doubled my income to 200k+ the consulting game is a a racket and to that extent big tech is too BUT big but tech and large streaming companies actually pay top dollar for good talent that can code . These suits have been lying to you ALL. Wake up in the morning and studying my friends you do not need these consulting firms they need you! Do not feed into there bs…studying for interviews and get you fare share of this tech wave… I am making more than my Ms

likefunny

I have 2YOE and a liberal arts undergrad. Does an MSBA open more doors into analytics? I don’t really see myself getting an MBA at this stage of my life, but I’m not entirely closed off to the idea yet.

like

Vibe check - do you also feel often times that you are a wizard who pulls a rabbit out of his hat on each project, with unrealistic timelines, no data, understaffing and lack of clear requirements?

like
like

I’m interested in going back to school for an analytics degree. I have a math & stat undergraduate degree and have been doing data science in my projects. Does anyone have recommendations for...

like

Looking for strategy and analytics consulting role in the big 4 . Any help would be welcome thanks

like

If u r in D&A consulting.. do certifications really helps? Whats ur take on it?

like

Anybody have any luck using virtual box to load a VM on windows 10? Throwing an error saying:

“The hardware virtualization is enabled in the Acceleration section of the system page”

like

What are some benefits of doing a Masters in a more applied subject like data science compared to a more theory one like purely statistics? Considering a quant heavy masters vs functional (M Ed)

like

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
  • Discover what it’s like to work at companies from real professionals
  • Get candid advice from people in your field in a safe space
  • Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here

Share

Embed this post

Copy and paste embed code on your site

Preview

Download the
Fishbowl app

See what’s happening in your industry
from the palm of your hand.

A phone with Fishbowl app

Send download link to your phone

OR

Scan your QR code to download
Fishbowl app on your mobile

By continuing you agree to Terms of Use and Privacy Policy.

Messaging rates may apply

Download app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy

Already have an account? Log in

Sign up for free to continue using Fishbowl

By continuing you agree to Terms of Use(New) and Privacy Policy(New)
Messaging rates may apply

Already have an account? Log in

For account settings, visit Fishbowl on Desktop Browser or

General

Legal