Author: lmcc1978

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.

Vlookup using 2 lookup tables (use of If function)

Subscribe my youtube channel!

 

Today’s exercise implies an increase of difficulty level, the use of Vlookup using two possible lookup tables.

Image below show us 3 tables. Two of them give the prices of rooms for 3 different hotels (for 1-2 persons), where each table gives us low season  and high season prices:

image1.png

The function of the third table (the blue table) is to return the price per room depending the number of person, season and hotel name.

To find the price, i have to lookup in the two main tables the input info that im going to put in the blue table (number of people, season and hotel name) using two function (If and Vlookup)

The If function will decide which of the two tables i use (depending if its low or high season). Vlookup will find the hotel that i want (hotel name) and when find the name,  return value for 1 or 2 persons (depending the value that i put in number of people cell).

Example of price for Hotel Madrid (Cell C11), in low season (Cell B11) for 2 persons (Cell A11):

image2

Starting with If function sintax:

image3

This function has 3 important arguments:

  • Logical test that receives value or expression and return TRUE (1) or FALSE(0)
  • Value_if_true returns a value if Logical_test is true
  • Value_if_false returns a value if Logical_test is false

If i want to decide what table to use (low or high season), my logical_test will be the text that i input in cell B11 (season):

ISNUMBER(SEARCH(B11;A1)) -> Search if text in B11 appears in Text A1 (text “low season”) and returns the number position where text B11 is found in text A1.

If the text is not find will return #VALUE!

Example of function SEARCH:

SEARCH(“abc”;”xpabc”) =3 (first letter of text “abc” is found in position 3 of text “xpabc”)

SEARCH(“abc”;”xp”) =#VALUE!

You know that the logical_text must return TRUE or FALSE, so i add function ISNUMBER that receives the result of SEARCH function ( this function returns a number or #VALUE!).

The function ISNUMBER returns TRUE (1) if the parameter is a number or returns FALSE(0) if is not a number (#VALUE!). The first parameter of IF function is now explained and completed.

image5

Second and third parameter of IF function is the value if logical_test is TRUE or FALSE.

If the condition  ISNUMBER(SEARCH(B11;A1)) is TRUE it means that in cell B11  appears in cell A1 text(“Low season”), and we are talking about table low season. If its FALSE its the high season table.

In the case below i put “high” in cell B11, so when using ISNUMBER(SEARCH(B11;A1)), text “high” does not appear in A1 text (Low season), that’s why return FALSE:

image4.png

So after choose the table (low or high), i will use the Vlookup to choose the price in function of the hotel name (C11) and number of person(A11).

If my logical_test is TRUE (low season), my Vlookup will be (Value_if_true):

image5

VLOOKUP(C11;B2:D5;MATCH(A11;B2:D2;0);FALSE), where:

  • C11 is the lookup_value (hotel name)
  • B2:D5 is the table array
  • MATCH(A11;B2:D2;0) will return column C or D depending the A11 value (1 or 2)
  • False to return exact_value

In case of high season table (Value_if_false):

image6

VLOOKUP(C11;I2:K5;MATCH(A11;I2:K2;0);FALSE), where:

  • C11 is the lookup_value (hotel name)
  • I2:K5 is the table array
  • MATCH(A11;I2:K2;0) will return column J or K depending the A11 value(1 or 2)
  • False to return exact_value

The final formula is:

IF(ISNUMBER(SEARCH(B11;A1));VLOOKUP(C11;B2:D5;MATCH(A11;B2:D2;0);FALSE);VLOOKUP(C11;I2:K5;MATCH(A11;I2:K2;0);FALSE))

For the case where room is for 2 persons in high season for hotel madrid

image7.png

You can  download here this file: vlookup_if

Number Systems: Master of numbers conversion in 90 minutes

Subscribe my youtube channel!

$1o promotion course

This is the number systems course, where you will learn how to convert and make arithmetic operations with decimal, binary, octal and hexadecimal numbers.

Tech.jpg

Use of function SUM/SUMIF/SUMIFS in excel

Subscribe my youtube channel!

Hello,

Today I’m gonna talk about 3 math function used in Excel, one that simply makes the addition of values (SUM), and 2 others that make addition of values using 1 criteria (SUMIF) or 2 or more criteria (SUMIFS).

Lets start with the data table to work.

This table show the number os rooms booked in 3 differents hotel (for 4 different months):

excel-table

The first operation that i’m going to do is get the total of rooms booked in this 4 months using the 3 hotels:

excel-table_sum

To make this operation, i just have to sum all the values, using the function SUM. This function receive in parameters just sigle cells or cell ranges (in this case receives cell range D3:D14).

Final result is (7465 booked in 4 months):

excel_sum_1

In the second example, using the same table, i want to return number of booked rooms (cell B20), for a specific hotel (cell A20):

excel_sumif_1

Now i have to make a sum, but with the name of hotel as condition (sum all the values that match with value of cell B20):

excel_sumif_2

To make sum depending on a condition we use SUMIF. This function has 3 parameters, first the range wich you will apply your criteria, and finally the sum_range that cantain the value to sum. In the example, the range is the cell range C3:C14, criteria for example could be Hotel Madrid and sum_range is cell range D1:D14.

The final result is:

excel_sumif4

excel_sumif_3

In the final example i make an exercise but using 2 conditions (use SUMIFS when using more than 1 condition).I want to return the number of booked rooms depending the hotel name (A23) and the month (B23):

excel_sumifs1

First parameter of SUMIFS is sum_range, that in our example is the range that have all the values of  booked rooms (D3:D14). The others parameters are the diferent criteria ranges with the respective criteria value. In this example i have 2 ranges with 2 criterias (hotel name and month). First criteria_range is Hotel names (C3:C14)and the cell where i specifie the hotel name  (A23) is my first criteria.The second criteria_range is the months (B3:B14) and the cell B23 where i specifie the month is my second criteria value:

excel_sumifs2.png

Final result (selection one hotel and one month)  is number of booked rooms for Hotel Madrid in January Month:

excel_sum_ifs3.png

escel_sum_ifs2