Sum or count only certain values in a range

Tools: functions SUMIF, COUNTIF

Level: intermediate

Problem

You have a sequential list of grades for every student in your class. You need to compute average grades for every student that will be their final grades.

	A	B
	Student	Grade
	Alisha
	Becky
	Cathleen
	Becky
	Alisha
	Becky
	Cathleen
	Alisha
	Cathleen
	Alisha

Solution

A student's average grade is sum of her grades divided by number of her grades. We will use two conditional lookup functions SUMIF and COUNTIF in a single formula.

(OPTIONAL) Select range A2:A11 and name it "students".
(OPTIONAL) Select range B2:B11 and name it "grades".
Active cell C1. Type "Alisha"
Active cell C2. Enter function =SUMIF(students,C1,grades).
Active cell C3. Enter function =COUNIF(students,C1).
Active cell C4. Enter =C2/C3.
Type in cells D1 "Becky" and E1 "Cathleen".
Copy range C2:C4 to D2:E4.

A	B	C	D	E
Student	Grade	Alisha	Becky	Cathleen
Alisha
Becky
Cathleen		3.00	3.33	3.67
Becky
Alisha
Becky
Cathleen
Alisha
Cathleen
Alisha

You should get as a result the following table.

In practice, we could have just as well combined formulas in rows 2-4 in a single formula =SUMIF(students,C1,grades)/COUNTIF(students,C1).

Formula syntax

Code	Variable	Comment
=SUMIF(
students	Range	Evaluate range "students"
C1	Criteria	Get value from C1 ("Alisha") and select all entries in "students" than match it
grades	Sum_range	Do not sum values from "students", sum values from "grades" but based on criteria from "students"

)		Formula complete

Code	Variable	Comment
=COUNIF(
students	Range	Evaluate range "students"
C1	Criteria	Get value from C1 ("Alisha") and count all entries in "students" than match it
)		Formula complete

Find data in a table

Tools: functions INDEX and MATCH

Level: advanced

Problem

You have a room occupancy list (Yes = room occupied, No = room available).

A	B	C	D	E	F
Room	1-Mar	2-Mar	3-Mar	4-Mar	5-Mar
Red	Yes	Yes	Yes	Yes	No
Green	Yes	Yes	No	No	No
Blue	Yes	Yes	Yes	Yes	Yes
Cyan	Yes	No	Yes	Yes	No
Magenta	No	Yes	Yes	No	Yes
Yellow	No	Yes	No	Yes	No
Black	Yes	Yes	Yes	Yes	Yes

Create a formula that will save you the trouble of manually looking for a row and column intersection to answer a question if a particular room is available on a particular date. Instead, all this formula should take is to type in dedicated cells a room name and a date to get a Yes/No result.

Technically this operation is called "parsing the matrix".

Solution

We will use two functions: INDEX and MATCH. MATCH() locates the place of an item in an array, and INDEX() returns the result from the specified row and column in the array.

(OPTIONAL) Name range B2:F8 "available". This will be an array or available rooms that we will analyze with INDEX().
(OPTIONAL) Name range A2:A8 "room". This array will let us identify the right row.
(OPTIONAL) Name range B1:F1 "dates". This array will let us identify the right column.
(OPTIONAL) Type in cells A9,B9 and C9 text "Room", "Date" and "Available" – these are labels for our formula variables and formula output.
Active cell A10. Type "Green". This is first variable.
Active cell A11. Type "3-Mar" (text will be converted to a proper date automatically). This is second variable.
Active cell A12. Enter function INDEX either via Function Wizard or by typing. Either way, you will get a prompt offering you a choice between "Array" and "Reference".
Choose "Array".
Type or press F3 and enter "available" as array argument.
Type or enter function MATCH(A10,room,0) as row_num.
Type or enter function MATCH(A11,date,0) as column_num.
Press Enter. Your completed formula will look as follows: =INDEX(available,MATCH(A10,room),MATCH(A11,date))

Your resulting table should look as follows now:

A	B	C	D	E	F
Room	1-Mar	2-Mar	3-Mar	4-Mar	5-Mar
Red	Yes	Yes	Yes	Yes	No
Green	Yes	Yes	No	No	No
Blue	Yes	Yes	Yes	Yes	Yes
Cyan	Yes	No	Yes	Yes	No
Magenta	No	Yes	Yes	No	Yes
Yellow	No	Yes	No	Yes	No
Black	Yes	Yes	Yes	Yes	Yes
Room	Date	Available?
Green	3-Mar	No

Formula syntax

Code	Variable	Comment
=INDEX(		INDEX () finds inside array a value that has a row equal to row_num and column equal to column_num. These are not cell column and row numbers, they are relative to the array and its size.
available	Array	Array that INDEX should parse (i.e. load and analyze)
MATCH (	Row_num	Row number inside array. This value will be calculated by MATCH () function that will tell us what is the sequential number of the value in an array.
A10	Lookup_value	The value we want to find in the lookup_array ("Green")
room	Lookup_array	The first column array where "Green" should be found.
	Match_type	"0" forces MATCH() to find an exact value. Since the list of rooms is not sorted alphabetically, we need with switch to make sure the function will not stop before it finds "Green".
)		Function MATCH () complete
MATCH (	Column_num	Column number inside array. This value will be calculated by MATCH () function that will tell us what is the sequential number of the value in an array.
A11	Lookup_value	The value we want to find in the lookup_array ("3-Mar")
date	Lookup_array	The header row array where "3-Mar" should be found..
	Match_type	"0" forces MATCH() to find an exact value. We probably could have done without it this time, but it both makes the calculation less intensive and makes the results less prone to accidental error or bug.
)		Function MATCH () complete
)		Function INDEX () complete

Create an alphabetic list

Tools: functions CHAR

Level: advanced

Problem

A,B,C…X,Y,Z is not a custom list that comes with Excel. How do you make one without typing it manually?

Solution

Use CHAR() function.

Create in A1:A26 a list of values from 65 to 90.
Active cell B1. Type CHAR(A1).
Copy the formula down all the way to cell A26. You will have an A-Z list.
(OPTIONAL) With B1:B26 still selected, open Options and press Import List. A-Z custom list will be created. After that, you will be able to type A,B... and drag the fill handle to create a list.

Formula syntax

Code	Variable	Comment
=CHAR(
	Number	ASCII code for the character you want the function to return. "65" is ASCII code for "A". If you are a Mac user, you will need to use codes from the Mac code table. "A" on Mac is "41".
)		Formula complete

Create a random password

Tools: functions RANDBETWEEN, INDEX, CHAR, CONCATENATE

Level: advanced

Problem

You need to create a system to generate random passwords that must:

Be 8 symbols long.
Contain only numbers or Latin letters and digits 0-9.
Be a random combination of characters.

Solution

Excel does not allow us to get totally random numbers but for a password generation purpose, a pseudo-random number will work well. We will use a function RANDBETWEEN() to get the numbers we need and then convert them into characters using CHAR() function.

However, we also need to make sure that numbers we get are {0-9;A-Z;a-z } and that symbols do not follow any particular order. In DOS character table this range of digits and letter corresponds to set {48-57;65-90;97-122}. In between these numbers, various other symbols are coded – e.g. =, @, [ and so on. They are sometimes allowed in passwords but not in our case. RANDBETWEEN can only take a single range of integers. So we will need a workaround to select at random either an uppercase letter, lowercase letter or digit.

In a new sheet, create a sequential vertical range of number from 47 to 22.
Delete from range numbers 58-64 and 91-96. Use "Shift cells up" switch to make sure the range is not interrupted. It should contain code number for 10 digits, 26 uppercase and 26 lowercase letters – a total of 62 different codes, 62 cells in a single vertical range.
Name this range "symbols".
In a cell where you want your 8-digit password to appear, type of insert =RANDBETWEEN(1,62). This formula will generate a random number that we will use to point to the range of permitted symbols.
Press F2, click in the Formula bar or double click in a cell to activate Edit mode. Select and cut all formula using Ctrl-X or Cut button.
Type or insert =INDEX(symbols,.
Paste what you just cut and close the bracket. You should have now =INDEX(symbols,RANDBETWEEN(1,62)). We will search the "symbols" range for whatever symbol code is at the row with the number we got at step 4.
Cut this formula again.
Type or insert =CHAR(
Paste what you just cut again. Result should be: =CHAR(INDEX(symbols,RANDBETWEEN(1,62))). We converted a random code obtained at step 7 to a corresponding digit or letter.
Cut this formula. Promise, this is the last time.
Type or insert =CONCATENATE(. Using a Function Wizard is strongly recommended, as now is the most tedious part. This function merges the values or its arguments in a single text string.
Paste into first field what you just cut.
Press Tab to jump to next field.
Paste what you just cut again.
Repeat 6 more times.
Press Enter or click OK. Your formula is complete.

Your final result will look

=CONCATENATE(

CHAR(INDEX(symbols,RANDBETWEEN(1,62))),

CHAR(INDEX(symbols,RANDBETWEEN(1,62)))

)

And your final output may look like anything below -- vRw5JwL2, mYMlQgk3, bICVbyWL, AG03Qs9P, 28Zsd2O9, PgZ83RZ0, CRqkpcli, 3I5uIupY, 9R8q6oIp, hjzEeK4u, tqI8BnhD, N1yjuKB0, zRNqR6tO, tKbik8Co, lauGmWVD, w0zAIet6, dlfX9CpH, hKqKPVgj, OertQqMp, DmWicF1j, sVZXUlWz, -- but most most likely will be something totally different.

Not bad for a password, I hope.

Formula syntax

Code	Variable	Comment
=CONCATENATE	Text	Merges arguments in a single text string
CHAR(	Number	Converts symbol code to a symbol
INDEX(		Finds symbol code in an range or symbol codes
symbols	Array	Range of symbol codes
RANDBETWEEN(	Row_num	Random number of a row of a "symbols" range
		Min row of "symbols" range
		Max row of "symbols" range
)		Function RANDBETWEEN() complete
)		Function INDEX() complete
)		Function CHAR() complete
REPEAT BLOCK 8 TIMES		CONCATENATE() includes 8 identical arguments - same as the one above.
)		Formula complete