Links

Binary to decimal number conversion

Subscribe my youtube channel!

 

Learn how to convert binary numbers to decimal numbers:

 

Advertisements

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

 

 

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

if_statement

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:

if_statement_1

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”

if_statement_3

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)

if_statement_4

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

if_statement_5

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:

if_statement_6

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:

image2

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

excel_data_validation

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

data_val_list

number_people_list

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

number_people__error.png

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

number_people_list_1

When values are invalid gives the error message:

number_people_list_2

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

data_validation_season

data_validation_hotel

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

image6

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.