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:

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:

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

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:

=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

Ranking Top City is quit useful, idea multiple 2 array is Gud one.

LikeLiked by 1 person

Thanks Rajesh

LikeLike

Excellent example for SUMPRODUCT usage. Fantastic.

LikeLike

Thanks for your comment Udayakumar

LikeLike