Learn how to convert binary numbers to decimal numbers:

Skip to content
# Binary to decimal number conversion

# Function REPT, RANK and SUMPRODUCT (use of arrays)

# IF Statement and logical/math/statistic functions

# Optimization using data validation tool in excel

Free online tutorial

Links

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

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

OR(AVERAGE(B2:C2)>=0.5;AND(B2>0.45;C2>0.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):

OR(SUM(B2:C2)>=1;MIN(B2:C2)>0.45)

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

OR(AVERAGE(B2:C2)>=0.5;MAX(B2:C2)>0.85

Final result is:

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:

IF(**LEN(B11)=3**;VLOOKUP(C11;B2:D5;MATCH(A11;B2:D2;0);FALSE);VLOOKUP(C11;I2:K5;MATCH(A11;I2:K2;0);FALSE)).

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

%d bloggers like this: