 # All articles with 'Microsoft Excel Formulas' Tag

## Weighted Average in Excel [Formulas]

Published on Sep 19, 2019 in Learn Excel Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

Continue »

## #awesome trick – Extract word by position using FILTERXML()

Published on Sep 12, 2019 in Excel Howtos, Learn Excel This is CRAZY!!!. I stumbled on a weird use for FILTERXML() while reading a forum post earlier today. So I couldn’t wait to test it. I am happy to share the results.

Say you have some text (sentence / phrase / keyword etc.) in a cell and you want to extract the nth word. Unfortunately Excel doesn’t have SPLIT() formula. So we end up writing obscenely long array formulas or use gazillion helper columns.

Here is the super sneaky trick. Use FILTERXML() instead.

Continue »

## How to trace precedents in Excel formulas? [tip+music from Prague]

Published on May 22, 2019 in Excel Howtos Here is a very useful and almost secret Excel tip for you. Imagine you are looking at a big, complex workbook with lots of calculations. You want to understand where everything is pointing to and how the workbook is set up.

You can use trace precedents in Excel to do this. Read this tip to learn how it works.

Continue »

## VLOOKUP multiple matches – trick

Published on Apr 16, 2019 in Excel Howtos, Learn Excel We all know that VLOOKUP can find first match and return the results. But what if you want all the matches? Use this simple trick instead.

Continue »

## Top 10 Excel Formulas for any situation

Published on Mar 27, 2019 in Learn Excel 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.

Continue »

## Quickly Change Formulas Using Find / Replace

Published on Feb 28, 2019 in Learn Excel So you have built that excel report your boss wanted. And you were all eager to use the spreadsheet in your presentation. But in the last minute, your boss asked you to change average sales to total sales figures. You also want to grab an espresso before rushing to the meeting. Now what?

Continue »

## Elevator problem – Excel homework

Published on Dec 13, 2018 in Excel Challenges The other day while I was in lift (elevator), it made an alarm like sound and won’t close the doors. Turns out there are one too many people in the lift for it to operate safely. As soon as a couple of people volunteered and stepped out, it started fighting gravity and took us upstairs. […]

Continue »

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

Published on Sep 21, 2018 in Excel Challenges 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. • 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 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 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  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 `43271`.

Continue »

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

Published on Jun 15, 2018 in Excel Challenges 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 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 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.

• 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 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 »

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.