Filter one table if the value is in another table (Formula Trick)
I have an orders table and want to see only orders from a given list of products. We can use Excel’s FILTER function to do this.
10 Advanced IF formula tricks you must know
IF is the most used Excel function out there. Here are 10 advanced IF tricks to take your formulas to next-level ?
How many calls we got outside office hours? [Excel / Power Query homework]
Time for another Excel formula / Power Query challenge. This is based on a common business data analysis problem. Say you have two tables – calls log and office hours. Call log tells when each call is received. Office hours tell us working hours for seven days of the week. We want to know how many calls are outside office hours.
Top 10 Excel Formulas for any situation
Excel has hundreds of formulas. But as a new learner or user, you may want to just focus on top 10 formulas to get the most out of it. Assuming you already know the basics (check out Beginner Excel page if you are complete newbie), here is a list of top 10 Excel formulas for you.
Top 5 HR Analytics Examples – Free Video Masterclass
I recently finished a long consulting gig with one of the government ministries in New Zealand. Guess what I was doing? HR Analytics and Reporting. In this post, I want to share my top 5 Excel tips for HR people, based on what I learned in the last 18 months.
Specifically, we will cover:
- Gathering and structuring Employee data in Excel
- How to use Power Query to collect data
- Polish / clean data in Power Query
- Bring cleaner data to Excel as refreshable table
- Answering questions about employees
- Using Excel formulas such as COUNTIFS, SUMIFS, AVERAGEIFS
- Pivot tables for data analysis
- Understanding the results quickly with conditional formatting
- Understanding pay gap
- Calculating gender pay gap
- Visualize pay gap
- Creating salary distribution charts
- Working with histogram charts in Excel 2016 / Office 365
- Making interactive charts
- Generating letters thru mail merge
- Calculating employee bonus based on bonus mapping logic
- Creating 100s of letters with a single click using Mail Merge + Word
Sounds interesting? Read on for details.
Introduction to Excel SUMIFS Formula
Excel SUMIFS function is used to calculate the sum of values that meet any criteria. For example, you can calculate the total sales in east zone for product Pod Gun using SUMIFS formula.
In this article, you will learn:
- What is SUMIFS function and how to use it?
- Syntax for SUMIFS
- Using SUMIFS() with tables and structural references
- SUMIFS examples – simple, wild card
- Using SUMIFS() with date & time values
- Free sample file for SUMIFS formula
- More formulas for data analysis
Avoid Hiring Boo-boos with Excel – COUNTIFS for the win [video]
Imagine you are head of human resources at Casual Contracting Co. Every month you hire a lot of temporary staff who spend 1-4 months with CCC before leaving. Sometimes you hire the same people again. Of late, you have noticed a strange process gap. You are paying same person two (or more) salaries.
This is because you are hiring a person for new temp role even before their current one ended. See above picture.
So how to avoid making such hiring boo-boos.
Simple, using Excel of course.