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

GUID

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

Random groups

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

future

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

Lottery Small

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

password-866981_640

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

Sumproduct Wild Cards

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

DateTime Value

How to Update Date or Time in EXCEL

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

To update/extract the date or Time part separately in a DateTime Value is a lot easier  if you understand that excel stores dates as sequential serial numbers and time as a fractional portion of a 24 hour day. With the above understanding, you can combine INT and MOD functions to perform calculations on or extract the […]