week number

4 WAYS TO SUM DATA BY WEEK NUMBER

Crispo Mwangi | 8-Feb-18 | Leave a Comment

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 ( […]

VLOOKUP IS DEAD

VLOOKUP IS DEAD–OR IS IT?

Crispo Mwangi | 20-Jan-18 | 2 Comments

In the year 1882, a German philosopher, Friedrich Nietzsche made a claim that has revolutionalized Philosophical and Theological discussions since then. His Claim–God is Dead. There has been a similar discussion in the Excel world, not about God, but about VLOOKUP. Of all the over 450 functions in Excel, VLOOKUP is one of the most popular […]

HLOOKUP with multiple criteria (OR/AND logic)

Crispo Mwangi | 12-Aug-17 | Leave a Comment

When is the last time you comprehensively looked at HLOOKUP function and its alternative? To look up data horizontally, many excel users tend to use HLOOKUP but it has its limitations. So what are the alternatives? In this article, we shall look at? Horizontal lookup using HLOOKUP, VBA & INDEX & MATCH How to return multiple items in a Horizontal Lookup How […]

fomc

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 […]

NESTED IF

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 N using multiple criteria

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)

Crispo Mwangi | 27-Jan-17 | Leave a Comment

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

Crispo Mwangi | 14-Jan-17 | Leave a Comment

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 […]

hlookup2

HLOOKUP, INDEX & MATCH OR VBA

Crispo Mwangi | 8-Oct-16 | 3 Comments

Having done a series on Verticle lookup  it is only fair to touch on 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 example, […]

REVERSE LOOKUP

REVERSE LOOKUP USING VBA

Crispo Mwangi | 11-Sep-16 | Leave a Comment

This is the last article in the series of reverse lookup in excel. Unless you are already versed with Reverse Lookup, consider checking below 2 articles first; Reverse Lookup & Reverse Lookup with duplicates Now using VBA we are going to lookup the date, time and Jobs assigned to Joy Bell Go to Developer→Visual BAsic→Insert […]