Formulas Archives - TheSmartMethod.com

Publishers of the world’s most comprehensive and up-to-date Excel tutorials

Excel tutorials

The only Excel books you will ever need

The only constantly updated Excel 365 titles more…
Thoroughly covers Power Pivot and Power Query more…
Covers business intelligence and OLAP features more…
Simple, easy, understandable, 2-page lessons more…
Available as both printed books and e-Books more…
Used by schools, colleges and universities more…
Available for 365, 2021, 2019, 2016 and Mac versions more…

Category: Formulas

Search again:

VLOOKUP inexact match

VLOOKUP lesson with sample file that will teach you everything there is to know when creating a VLOOKUP inexact match Excel function.

VLOOKUP exact match

Simple VLOOKUP tutorial with sample file that will teach you everything there is to know when creating a VLOOKUP function with an exact match.

Excel XLOOKUP function (part two)

This is the second of a two-part series of XLOOKUP lessons. If you haven’t already done so I recommend that you begin with part one by clicking this link. Excel

Excel XLOOKUP function (part one)

The new Excel XLOOKUP function was introduced in the July 2020 Excel 365 semi-annual update. It isn’t available in older versions (Excel 2019 and earlier). The July 2020 update was

Excel SEQUENCE Function

This tutorial uses a single Excel SEQUENCE function to create a perpetual calendar. Includes sample files and a step-by-step guide.

Excel formula to get working days between two dates excluding holidays

This article shows you how to calculate the number of working days between two dates in Excel using the NETWORKDAYS function.

Applying a unique constraint in Excel

This article shows how to impose a unique constraint in Excel, preventing duplicate items from being accidentally entered.

Remove dashes from ssn, keep zeros

This article shows you how to remove dashes from social security numbers by using Excel’s SUBSTITUTE function, with a downloadable example.

Excel IF formulas with multiple arguments?

This article explains the Excel IF function and how to best handle situations where multiple conditions are needed, including a sample workbook.

Calculating Loan Payment Dates

This article shows how to calculate loan payment dates in Excel, using a formula that can calculate loans of any length and number of periods.

Excel formula to get number of days in a month

This article will show you how to create an Excel formula that calculates the number of days in the month for any given date.

A formula or easy way to annualize data based on month

This article shows you the Excel formula to annualize data by month or by any other number of periods in a year. A sample workbook is included.

Changing dollar signs to GBP in Excel

This article shows you how to change currency symbols in Excel, how to convert from one currency to another and how to get exchange rate data online.

Using VLOOKUP when one column is blank get value from another column

Using VLOOKUP, if Column 1 is blank, get value from Column 2

This article shows how to create an Excel VLOOKUP formula that extracts data from a different column if the first column it searches is blank.

Adding “+” between words in Strings

Code numbers such as product codes are common in Excel. When you work with codes like these you often need to join multiple pieces of text (or ‘strings’).

Calculating the original number after a percentage increase

This article shows you how to use Excel to calculate the original number after a percentage increase – a surprisingly common problem in accounting.

Convert negative numbers to positive in Excel

This Excel article shows you how to convert negative numbers into positive numbers without affecting existing positive numbers.

How to hide #N/A errors in Excel

This article explains the #N/A error code in Excel, why it occurs and how to hide it when it appears on your Excel worksheets.

How to use factorial (!) in Excel

This article shows how to calculate factorials and other combinatorials in Excel, showing how to calculate the probability of winning the lottery.

An Excel formula to get the week of the month

This article shows you how to create an Excel formula to get the week of month from a date, as well as how to get the week of year from a date.

Working with HEX values in Excel

This article explains the Hexadecimal counting system and how to work with it in Excel, as well as the four different counting systems supported by Excel.

Use the LAMBDA function and DYNAMIC ARRAYS to construct a roulette simulator

In this step-by-step tutorial Excel’s Lambda function and Dynamic Array feature are used to develop a full professional-grade Excel application.

Use the SUMIF and COUNTIF functions to create conditional totals

How to use VLOOKUP to search for a piece of text and return a matching value. This article also explains what VLOOKUP is and when to use it.

VLOOKUP using text strings

How to use VLOOKUP to search for a piece of text and return a matching value. This article also explains what VLOOKUP is and when to use it.

Separating text and numbers in Excel

This article shows three different ways of separating text and numbers in Excel. You’ll see how to do this using Flash Fill, Text to Columns and formulas.

Adding VAT to a value in Excel

This article shows how percentages are handled in Excel workbooks and how to add 20% VAT to a value in Excel, along with an example.

An Excel function to check if a cell contains some text

An Excel formula to check if a cell contains some text

Sometimes you need to know whether a cell contains specific, or any text. This article shows how to do this using the SEARCH, ISNUMBER and FIND functions.

Subtracting percentages in Excel

This article explains how Excel handles percentages and shows you how to subtract a percentage from a number in Excel using two different methods.

Excel tutorials

Category: Formulas

Categories

Recently added articles

Excel Tutorials

Articles

Downloads

Policies

About

Which tutorial do you need?

Which Excel Essential Skills version do you need to learn?

Excel 365 for Windows

Excel 2021 for Windows

Excel 2019 for Windows

Excel 2019 for Mac

Excel 2016 for Windows

Excel 2016 for Mac