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




IF Statement and logical/math/statistic functions

Subscribe my youtube channel!


In this post i will cover the logical function IF and some logical/math/statistic functions that will help to exercise resolution.

Starting with the table exercise, i have the result of 2 tests for 4 different students, and i want to classify each student as “approved” or “not approved”, depending of the defined conditions (condition 1 and condition 2):


In the first condition, to be approved, the student must have the average of the 2 tests positive or the 2 tests above 45%. Now i’m gonna write these condition in excel:


I have 2 possible values (“approved” and “not approved”), so i use de IF function that help me to choose one of the two situations:

=IF(OR(AVERAGE(B2:C2)>=0.5;AND(B2>0.45;C2>0.45));”Approved”;”Not approved”)

My logical test is inside OR function because i have 2 possible ways to be approved using the condition 1 (average of 2 tests equal or greater then 50% OR the 2 tests are greater than 45% – use of AND function because we need the 2 tests greater then 45%):


If value_if_true appear  “Approved” and value_if_false “Not approved”


This is the version v1 for the first condition.

Now i’m gonna make the second version for the same condition:

=IF(OR(SUM(B2:C2)>=1;MIN(B2:C2)>0.45);”Aproved”;”Not aproved”)

The logical_test will be the sum of the 2 tests that must be equal or greater then 100% or the lowest  test rate must be higher then 0.45 (the final results are the same like we expected):



In the condition 2, the students are approved if the test average is positive or if one of the two tests is above 85%:


For this situation the excel function is:

=IF(OR(AVERAGE(B2:C2)>=0.5;MAX(B2:C2)>0.85);”Aproved”;”Not aproved”)

Where the average needs the be greater or equal to 50% (average positive) or one of the tests must be above 85% (i used the MAX function to see if the best test is above 85% – it’s one of the possible solutions):


Final result is:


Optimization using data validation tool in excel

Subscribe my youtube channel!


In the previous post you learn how to use Vlookup using 2 lookup tables.

In this post, using the same data, i want to make otimization of the data table search (blue table) , that returns the room price in function oh Hotel name, Number of person and Season:


To this 3 input parameters (Hotel name, Number of person and Season) we used editable cells (A11,B11,C11) where we could write any value/text.

In this post i’m gonna transform this 3 cells in 3 drop down lists with the purpose of just allowing the possible value (cell A11= 1;2 / cell B11= low;high and cell C11= Hotel Summer;Hotel Madrid; Hotel Village) and reject other values. To make this change i use data validation tool (list):


After choose data validation, select list and in source write the two possible value for number people(1 and 2 separated by semicolon):



To not allow other values, go to error message and write title for the error window appear when the value is not valid:


The final result is the drop down list with the 2 possible values:


When values are invalid gives the error message:


After configure the first drop drow box, you can configure the others:



Now you have all the 3 input cells configured with drop drow lists, giving a professional aspect to the table.

Now the final change. If you remember you use a IF function to choose what table to choose (low or high season):


The logical_test used in the IF function in the previous post was:

ISNUMBER(SEARCH(B11;A1))  -> see if text B11 appears in text A1

A way to simplify this test is seeing the lenght of cell B11 (if lenght of B11 is equal to 3, we have “low” in cell B11; if is not 3 the cell has the value “high”):

LEN(B11)=3 is the logical test, and the final result is:


As you can see, excel as the hability to use different methods, getting at the final the same results.