database-functions

EXCEL DATABASE FUNCTION

Crispo Mwangi | 5-Nov-16 | Leave a Comment

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

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

Reverse lookup with duplicates

REVERSE LOOKUP WITH DUPLICATES IN EXCEL

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

It is good to be able to do a Reverse Lookup but it is Awesome if you know how to return Multiple Items. Before you continue with the current article, Please revist the Previous Article on reverse lookup as this is a continuation. Suppose you are managing a project and you have below scheduled dates, […]

reverse LOOKUP

REVERSE LOOKUP IN EXCEL

Crispo Mwangi | 3-Sep-16 | 2 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 […]

2way lookup

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 For example using below sales data lookup and sum the total quantity for ALL […]

lookup and Sum

LOOKUP and SUM IN EXCEL

Crispo Mwangi | 7-Aug-16 | 1 Comment

Array fomulas comes in handy when you want to lookup and sum large amount of data. Array formulas replaces multiple normal formulas,performs multiple calculations and returns a single value as a result. For example using  below Main sales data in Quantity and a lookup price table, Calculate total sales for customer Brosina Hoffman?   There […]

blogunique

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

WILDCARD

WILDCARDS IN EXCEL

Crispo Mwangi | 23-Jul-16 | Leave a Comment

A Wildcard is a symbol used to replace or represent one or more characters. Excel wildcards are either an asterisk * which represents one or more characters, a question mark ? which represents a single character or a tide ~ used to distiguish between normal characters and a wildcard. A tide is rarely used. Examples: ►M* will […]

random

Excel RAND() and RANDBETWEEN Functions

Crispo Mwangi | 8-Jul-16 | Leave a Comment

Human being cannot consciously generate random numbers sequences as they tend to avoid/favor picking specific numbers yet there is so much application of randomness in life. Random numbers are useful in a number of ways, such as generating data encryption keys,GUIDS, Strong Passwords, selecting random samples or creating survey groups, generating lottery numbers e.t.c Scientist, […]