Function REPT, RANK and SUMPRODUCT (use of arrays)

Subscribe my youtube channel!

 

Hii!

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:

rept_function

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):

rept_function1

Inserting the 2 parameters i’m going to get:

=REPT(“‖‖”;C2/MAX($C$2:$C$9)*25)

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:

C2/MAX($C$2:$C$9)*25

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:

rank_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:

rept_function1

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:

sum_product

This function will multiply 2 arrays values:

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

($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:

{London=London;London=London;London=London;London=London;Liverpool=London;Liverpool=London;Liverpool=London;Liverpool=London}

And final values are:

{1;1;1;1;0;0;0;0}

Second array has other comparation:

$C$2:$C$9>C2

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

sumproduct_1

 

 

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s