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

# 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 WAYS TO USE EXCEL REPT FUNCTION

Crispo Mwangi | 29-Jun-17 | 8 Comments

When is the last time you used REPT function in Excel? REPT  is one of excel’s little-known, overlooked and underutilized function, yet very useful. Generally, REPT  returns a specific text string a specified number of times. =REPT(“Text”, Number of times) Here are 7 ways you can start using REPT function. ADD LEADING ZEROS CREATE INLINE […]

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

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

# GUID Generator In Excel

Crispo Mwangi | 6-Jul-16 | 1 Comment

Are you a developer working  with microsoft technologies and looking for a unique identifiers for  components/applications/files ? Or are you a database developer or administrator looking for primary keys tables? Well! in this article I will show you how to create a GUID (globally unique identifier) which is the best version of an ID and almost as […]

# RANDOMLY ASSIGN GROUPS

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

If your involves sampling, research, taking polls or working with control groups, then knowing how to  randomly assign groups is a key skill. Random assigning of groups ensures each participant or subject has an equal chance of being placed in any group and differences between and within the groups is not systematic at the outset […]

# Generating Random Future Dates,Time & Datetime in Excel

Crispo Mwangi | 25-Jun-16 | Leave a Comment

In this article I will show you how to generate random future Dates, Time &Datetime in excel using RAND(), RANDBETWEEN and WORKDAY.INTL functions. RANDBETWEEN function returns a random integer in a range of integers. = RANDBETWEEN(bottom, top) →→bottom(smallest integer) & top (largest integer) And since excel stores date as sequential serial numbers, we can use RANDBETWEEN to generate […]

# Generating Lottery Numbers In Excel

Crispo Mwangi | 25-Jun-16 | Leave a Comment

The chance of winning a lottery is usually infinitesimal but the magnitude of the rewards tends to over shadow it. This is why millions of people play or fantasize about winning the lottery daily. Since most Lottery use pseudo-random numbers to pick a winner, I will show you how you can use Excel RANDBETWEEN function to […]

# Generating Strong Passwords or Encryption keys Using Excel

Crispo Mwangi | 24-Jun-16 | Leave a Comment

A strong password should be at least 16 characters long, include special characters, Numbers, uppercase letters and lowercase letters. A mix of all these characters makes it hard for the password to crack. Most people find it hard to create a strong password but with Excel RANDBETWEEN and CHAR functions I will show you how […]