# 4 WAYS TO SUM DATA BY WEEK NUMBER

Every organization requires weekly summaries to monitor peak and off-peak periods. This is mostly so when you are in the production section. Excel has a beautiful but overlooked function (WEEKNUM) that returns an integer representing the week in the year (1 to 53). For anyone not familiar with WEEKNUM function, it contains 2 arguments; Serial_number ( […]

# 7+ WAYS TO LOOKUP NUMBER VALUES

Crispo Mwangi | 5-Oct-17 | 3 Comments

The ability to lookup number values in excel is a must have for all data analysts. Some Excel users just know the basic VLOOKUP function but there are 7 more functions to lookup values. The more lookup function you know the better you become in retrieving numbers like sales for a certain month, a balance for a […]

# FOMC Dot Plot Chart Using REPT Function

Crispo Mwangi | 4-Aug-17 | 4 Comments

On Linkedin, a user commented on how he has been struggling to recreate Federal Reserve dot Plot. After being puzzled for almost 2 years, he came to realize how REPT function in excel can be of great help. After searching the web, I have found there is no comprehensive article on how to recreate this […]

# 7 ALTERNATIVES TO NESTED IF FUNCTION

Crispo Mwangi | 16-Jun-17 | 13 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 […]

# REVERSE LOOKUP IN EXCEL

Crispo Mwangi | 3-Sep-16 | 6 Comments

In my research and tutoring excel, I have found that many people find Reverse lookup concept to be among the top 10 complicated things in excel. This article hopes to shed more light than heat in demystifying the Reverse Lookup in excel. In the normal lookup, we use the Column and/or Row header to return the value that […]

# 2 WAY LOOKUP IN EXCEL

Crispo Mwangi | 20-Aug-16 | 2 Comments

The ability to lookup values is a MUST have skills for all Excel users. If you cannot lookup you cannot excel in data analysis. In this article, I will show you  5  ways you can do a 2-way lookup in Excel. 1. USING SUMPRODUCT Using below sales data lookup and sum the total quantity for ALL items ordered […]

# COUNT UNIQUE DUPLICATE VALUES IN EXCEL

Crispo Mwangi | 30-Jul-16 | 4 Comments

COUNTIF is an excellent function to count only those cells whose  value meets a certain criteria. But as excellent as it is, it becomes a challenge to count Unique or Repeats in a range that contain duplicates. For example, Using below data count; Total number of unique customers Total Non-Repeat Customers Total Repeat customers There are 4 ways of counting […]

# Sumproduct with Wild Cards

Crispo Mwangi | 18-Jun-16 | 4 Comments

The 3 wildcard characters (?*~) used in other excel formulas do not work with sumproduct. All the same SUMPRODUCT utilizes other functions (LEFT, RIGHT, FIND and MID) to give you the same results. Suppose the following is yearly financial transactions  showing  Cost Codes and Total Cost ►Find the Sum of the total cost for Sales department if […]