All articles with 'Microsoft Excel Formulas' Tag
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.
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 »
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 »
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 »
Excel 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
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
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 »
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
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 »
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 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 »
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.
‘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 »
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 »
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 »
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 »