# 7 ALTERNATIVES TO NESTED IF FUNCTION

Crispo Mwangi | 16-Jun-17 | 15 Comments

IF function is one of the most used functions in Excel. In my opinion, it is the foundation of all programming and Excel’s formulae mastery.  However, it is also one of the most misused functions, especially Nested IF. Especially now with Excel 2007 and beyond, you can nest up to 64 IF functions to form complex, […]

# FIND THE LAST OR Nth OCCURENCE IN EXCEL USING MULTIPLE CRITERIA

Crispo Mwangi | 3-Feb-17 | 1 Comment

In the previous article, we looked at 7 ways to find the last or Nth occurrence in a Sorted list in excel. Unsorted list poses a challenge since you need to find the occurrence using multiple criteria. To show the different methods I have created a list of Customers and their Purchase Orders (P.O). You can download […]

# FIND THE LAST OR Nth OCCURENCE IN EXCEL (SORTED LIST)

Finding the Nth or the Last value in a sorted or unsorted list can pose a challenge if you do not understand which functions to use. This article will show you different ways to carry out this sort of find and retrieve in a sorted list. How to handle unsorted list will be tackled in […]

# Highlight a Sample In a Range or Text that contain certain Values

Learning how to use conditional formatting in excel can save you a lot of time when you need to visually highlight important information in a worksheet. At basic level, it can be used to highlight duplicates, values within certain threshold, Top or Bottom N items etc.  However, to get the full potential of conditional formatting, you […]

# DAVERAGE vs AVERAGEIF & DCOUNT vs COUNTIF

We have been exploring on database functions for the last two articles ( EXCEL DATABASE FUNCTION and EXCEL DSUM FUNCTION.) This article will examine  DAVERAGE & DCOUNT and how they compare with their equivalent AVERAGEIFS & COUNTIFS. Will the Database functions prevail in terms of speed over their IFS equivalent? DAVERAGE vs AVERAGEIFS For example using below data compute the […]

# EXCEL DSUM FUNCTION

Crispo Mwangi | 9-Nov-16 | 2 Comments

If you are new to Database Functions, read this Introduction to Database Functions first before you continue. In this article we shall look into the uses of DSUM function which is one of the EFFICIENT Excel sum functions with criteria. Below are some of its uses; 2 way lookup Summarizing Results based on criteria 2 WAY […]

# EXCEL DATABASE FUNCTION

Since their introduction in Excel 2007, DataBase functions have remained Overlooked & Underutilised. This is an Introduction in a  Series of articles whose aim is to demystify these Database functions. Database functions include; DSUM, DAVERAGE, DCOUNT, DCOUNTA, DMAX, DMIN, DGET, DPRODUCT, DSTDEV, DSTDEVP, DVAR, DVAR. All these functions works basically the same way and have […]

# HLOOKUP, INDEX & MATCH OR VBA

Crispo Mwangi | 8-Oct-16 | 7 Comments

Having done a series on Verticle lookup, it is only fair to touch on the Horizontal lookup. HLOOKUP function is the most popular function for this kind of lookup but it has a number of limitations and thus one may need to know a few other ways you can carry out a Horizontal lookup. For […]