Function REPT, RANK and SUMPRODUCT (use of arrays)

Subscribe my youtube channel!



Today i write a new post to know 3 new functions used in Excel.

Rept, Rank and Sumproduct are the 3 target function that i’m going to talk. First i show the data table to analyse:


The table show us data of 8 people who sell a specific product in 2 different cities. The first target is to make a graphic that represents sell of each person (column D). For that intent i use the function REPT  to repeat a specific text (input 1 of function) a number of times (input 2 of function):


Inserting the 2 parameters i’m going to get:


Where the character repeated is “‖‖” that i insert with excel option insert -> symbol.

The number of times that the character is repeated is given by the formula:


The formula used can be adapted by each user. The formula that i use just ensures that the biggest number has 25 bars representing the number (C2/MAX($C$2:$C$9 return always a value between 0 and 1). It’s easy to make a graphic in excel using a simple function!

In second exercise i will rank the seller from 1(person who sells more) to 8, using the function:


The function receives 3 parameters, number where i put the cell reference that will going to be compared with ref that contains a list of values (range values). Final parameter order will say if the list is sorted ascending or descending. Final values are:


Finally, the more complex process, extract ranking inside each city. To get this data i will need to compare the values that belong to the same city (in the previous exercise that situation was ignored). To make that comparation i use the  funtion SUMPRODUCT that work with arrays:


This function will multiply 2 arrays values:


($B$2:$B$9=B2) – for the first cell F2 this array will compare cell B2 with all cells in range B2:B9. In the array (array has multiple positions) will be compared something like this:

{B2=B2;B3=B2;B4=B2;B5=B2;B6=B2;B7=B2;B8=B2;B3=B9} and return 0 if comparation it’s false or 1 if its true:


And final values are:


Second array has other comparation:


Using the same logic we get final array:

{0;0;0;1;0;0;0;1} -just 78 and 87 are bigger then 56 (reason because i just have 2 one’s in this array)

The final multiplication will be (value in the same position will multiply):

{1;1;1;1;0;0;0;0}*{0;0;0;1;0;0;0;1} ={0;0;0;1;0;0;0;0}

Final result is:

SUMPRODUCT(($B$2:$B$9=B2)*($C$2:$C$9>C2))+1 = {0;0;0;1;0;0;0;0}+1=2





4 thoughts on “Function REPT, RANK and SUMPRODUCT (use of arrays)

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s