All articles with 'Microsoft Excel Formulas' Tag

How many people used their entire sick leave entitlement? [Power Query / Excel homework]

Published on Sep 21, 2018 in Excel Challenges
How many people used their entire sick leave entitlement? [Power Query / Excel homework]

Imagine you are the HR analyst at BigLargeCompany. You are asked to find out whether staff at BLC (BigLargeCompany you silly) use up their full sick leave entitlement.

You have two tables – emps & leaves as illustrated below.

sample data - sick leave entitlement vs. usage

Your mission is to find out answers to below questions.

  • How many employees used exactly 100% of their entitled sick leave?
  • How many employees did not take any sick leaves?
  • Listing of all employees who used 100% of their entitlement

 

Use either Power Query, Excel formulas or any other technique to answer the questions.

Continue »

Calculate travel time and distance between two addresses using Excel + Maps API

Published on Jul 19, 2018 in Excel Howtos
Calculate travel time and distance between two addresses using Excel + Maps API

Ever wanted to calculate distance using Excel  – between two locations (physical addresses)? If we know the addresses, we can go to either Google Maps or Bing Maps and type them out to find the distance and travel time. But what if you are building some model (or calculator) and want to find out the […]

Continue »

100+ Excel Formula Examples + List

Published on Jun 27, 2018 in Excel Howtos
100+ Excel Formula Examples + List

The first step of getting awesome in Excel is to understand that you can ask Excel do things for you. This is done by speaking a special language called as “Excel Formulas”. When you write a formula or function, you are asking Excel to figure out something from the values you have. Say you want to add up a bunch of values in a range A1:A10, you can ask Excel to do this for you by writing =SUM(A1:A10) and bingo, you get the result immediately. The best part is, if your numbers change, the answer changes too.

If you are a beginner, the world of Excel formulas can feel overwhelming. Why not? There are hundreds of different formulas in Excel.  So which formulas should you learn?

This guide gives you the answer. Here are 100+ most common Excel formula examples for every occasion. Each box describes a problem statement, an example, result, some notes and link to learn more. Use this guide to learn formulas quickly.

Continue »

How to use Date & Time values in Excel – a handy guide

Published on Jun 20, 2018 in Excel Howtos, Learn Excel

How to use Excel Date values, formulas and tipsExcel date time features are very handy and knowing how to use Excel date values can help you save a ton of time in your day to day spreadsheet chores. Let us prepare for your date with the sheet using these 10 handy tips.

Before jumping on to the tips, it helps to know how excel represents the date and time.

Microsoft Excel stores dates as sequential numbers … January 1, 1900 is serial number 1, and 20 June, 2018 is serial number 43271 because it is 43,271 days after January 1, 1900. Excel stores times as decimal fractions because time is considered a portion of a day. [Excel Help Text on Date / Time]

So you see, Date and Time are in fact numbers in Excel. Just enter a date in your excel sheet and format it as number to see its equivalent numeric value. If a date is 20-June-2018 and excel represents it as 43271.

Continue »

Which two teams qualify to next stage? [Excel Homework]

Published on Jun 15, 2018 in Excel Challenges
Which two teams qualify to next stage? [Excel Homework]

Time for some soccer fun in spreadsheets. Given a team points table like above,

How would you figure out in Excel, which teams qualify for next stage. The rules for this exercise are,

  • Pick the top two teams by points
  • If there is a tie, use Goal Difference (GD) to break ties (more GD is good)
  • If there is a tie, use Goals For (GF) to break ties
Continue »

Excel Tables Tutorial & 13 Tips for making you a Data Guru

Published on Jun 13, 2018 in Excel Howtos, Featured, Learn Excel
Excel Tables Tutorial & 13 Tips for making you a Data Guru

Excel table is a series of rows and columns with related data that is managed independently. Excel tables, (known as lists in excel 2003) is a very powerful and supercool feature that you must learn if your work involves handling tables of data.

What is an excel table?

Table is your way of telling excel, “look, all this data from A1 to E25 is related. The row 1 has table headers. Right now we just have 24 rows of data. But I can add more later!”

Continue »

Introduction to Excel SUMIFS Formula

Published on May 25, 2018 in Learn Excel
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
Continue »

Excel formula showing as text instead of actual result – How to fix the problem

Published on May 24, 2018 in Excel Howtos
Excel formula showing as text instead of actual result – How to fix the problem

Once in a while everyone is bound to come across this problem. You type a formula in a cell, then you press ENTER. Bam! nothing happens. You check if a donut chunk went in to the key board and some how jammed the ENTER key. So press it again, this time harder. But nothing. Excel […]

Continue »

Rescue oddly shaped data – Battle between Formulas, VBA and Power Query

Published on Apr 11, 2018 in Learn Excel, Power Query, VBA Macros
Rescue oddly shaped data – Battle between Formulas, VBA and Power Query

Let’s say you have data like this in a spreadsheet. Don’t roll your eyes, I am 102% sure, right at this moment, someone is (ab)using Excel to create similar messy data.

How do you reshape it to one column?

You could use formulas, VBA or Power Query. Let’s examine all these methods to see what is best. All these methods assume your data is in a range aptly named myrange.

Continue »

vlookup(), MATCH() and INDEX() – explained in plain English [spreadcheats]

Published on Feb 10, 2018 in Featured, Learn Excel
vlookup(), MATCH() and INDEX() – explained in plain English [spreadcheats]

VLOOKUP may not make you tall, rich and famous, but learning it can certainly give you wings. It makes you to connect two different tabular lists and saves a ton of time. In my opinion understanding VLOOKUP, INDEX and MATCH worksheet formulas can transform you from normal excel user to a data processing beast. Today, […]

Continue »

2018 Calendar, daily planner Excel templates [free downloads]

Published on Jan 3, 2018 in Learn Excel

Here is a New year gift to all our readers – free 2018 Excel Calendar & daily planner Template.

This calendar has,

  • One page full calendar with notes, in 4 different color schemes
  • Daily event planner & tracker
  • 1 Mini calendar
  • Monthly calendar (prints to 12 pages)
  • Works for any year, just change year in Full tab.
Continue »

How much long service bonus to pay? [Homework]

Published on Dec 8, 2017 in Formula Challenges

‘Tis Friday and it is too hot in my home office to stand and type a longish post. So, let’s keep this skirtish (short and pretty).

How would you calculate long service bonus?

Let’s say you are HR manager at BigLargeInc. and you are looking at Julia’s service details. You have her employment start date, current date, her leave without pay details, as shown below.

 

You need to calculate how many days of continuous service Julia had (ie total service – duration on leave without pay). How would you write the formula?

Continue »

5 conditional formatting top tips – Excel basics

Published on Dec 5, 2017 in Excel Howtos, Learn Excel
5 conditional formatting top tips – Excel basics

Time for another round of unconditional love. Today, let’s learn about conditional formatting top tips. It is one of the most useful and powerful features in Excel. With just a few clicks of conditional formatting you can add powerful insights to your data. Ready to learn the top tips? Read on.

Continue »

Which power plan is best for me? Excel for everyday problems

Published on Nov 9, 2017 in Analytics, Financial Modeling
Which power plan is best for me? Excel for everyday problems

We had to switch power providers soon, so I started reviewing the options. There are heaps of providers in New Zealand and each offer a ton of different plans. Some offer welcome bonus or credit worth up to $ 200. Other offer straight forward rates. Some others offer discount if you sign up for both electricity and gas with them. So how do you decide which one is better for you?

Using Excel of course.

The result is awesome. I ended up saving more than $1000 with a simple model. Puzzled? Curious? Check out this short but powerful video tut.

Continue »

Find out if a number has repetitive digits [formula homework]

Published on Oct 3, 2017 in Formula Challenges
Find out if a number has repetitive digits [formula homework]

Time for a quick formula finesse check. Let’s say you have a number in A1. What formula can you use to find out if it has duplicate digits.

For example, if A1 has 123405, then answer should be FALSE

and if A1 has 123455, then answer should be TRUE

Go ahead and post your answers (formulas, VBA or M script) in the comments section.

Continue »