Section 5: Formulas Summary

Question 1

Functions vocabulary list:

Answer 1

Attribution, Sum, sum-if, sum-ifs, Sum range, Range, count, countA, count-if, count-ifs, Count range, Range, Choose, Goal Seek, Data Table, IfError, Index, Match, Index+Match, V-Lookup, H-Lookup, Mid, First, Last, Upper, Lower, Proper,…. add later….

Question 2

What are arguments for sum-if?

Answer 2

It has three arguments: Range, criteria and sum-range.

Question 3

What is the difference between sum-if and sum-ifs?

Answer 3

In Sum-ifs we start with sum-range and then range and criteria… but in sum-if we first start with range.

Question 4

Should we repeat sum-range in sum-ifs?

Answer 4

No! first put the sum-range and add as many ranges and criteria as needed.

Question 5

Which functions are for counting cells and what are the differences?

Answer 5

Count, CountA. Count is only for numbers and it doesn’t count blanks and text. CountA counts BOTH NUMBER AND TEXT BUT NOT BLANKS.

Question 6

What is the difference between Cont-if and Count-ifs?

Answer 6

Count if starts with range but count ifs starts with count range.

Question 7

For Count and CountA we need a criteria, what specification should that have?

Answer 7

It should be inside a “ “ . For example, criteria is greater than 60 or Yes it should be like this: “>60” or “Yes”.

Question 8

In general what happens in order of arguments when we have ifs instead of if?

Answer 8

For “if” like as sum if or count if first we start with range but for “ifs” such as count ifs or sum ifs first we start with data range(sum range or count range) and then the ranges…

Question 9

We have set of names and want to only have three first letters and make the first letter capital which function should we use?

Answer 9

We should use First, mid or last for the position of text we want and use Proper to make the first letter capital. Other font sizing letters are Upper or Lower.

Question 10

How to round a large decimal number to only first two decimals?

Answer 10

Function named Round. It has two arguments: One for value and second the number of decimals..

Question 11

What is Index?

Answer 11

Index returns the value of a cell based on position in an array(not table). Position of column and row is different in an array than their location in table. e.g. a column as array has column number 1 but 3 inside the table…

Question 12

What is Match and what are it’s arguments?

Answer 12

Match brings the position of a value in an array. For example we want to know where is location of Milan in an array and should mention it be exact(0) or contains(1). Math has three arguments: First: Look-up value which is in new table, Second: Array inside of the source sheet, Third: 0 or 1 as exact or not exact.

Question 13

What is Index+Match and how is it’s structure?

Answer 13

When we are building a new table and want to call some cells from a source sheet use these two functions together. Index(array, Match, 1(column number and we can write empty). Match brings the position number and gives that number to Index and index brings the cell to the new position.

Question 14

What is V-Lookup?

Answer 14

V-Lookup finds the look-up value from the first column of table and based to column number and true or false arguments completes new table. First click of lookup value which it’s similar column in first column of source table, Second select the source table, Third column number, and finally True as exact and False and contains…

Question 15

What is Choose function?

Answer 15

When we want to build up a scenario based on a factor(index) that whenever that factor changes group of related cells change to show the scenario in a flexible pattern. For example, we have optimistic, base and pessimistic and study our numbers based on theses factors.

Question 16

What is Index number?

Answer 16

It is the number in a cell out of the table and starts from 1 to as many as factors we want to study. e.g. we have 3 scenarios we can put number 2 to 3.

Question 17

How Index number is linked to values?

Answer 17

The order of cells shows the number of Index. Meaning that if we use number 2 in our index, excel will find the second value and puts inside the table. Note that scenarios should be below the same column that the formula used. Note that values(value 1, value 2…) are cells which should be selected one by one and not in form of area.

Question 18

How many values can we have inside Choose function?

Answer 18

Unlimited.

Question 19

For what purpose is Choose function useful for?

Answer 19

Financial modeling and studying different values upon testing different scenarios

Question 20

What is Goal Seek function?

Answer 20

Is to let us study the result of inputs in relation to outputs. For instance, we like to have a Gross Margin of 17 and want to know how much the variable cost should be. It becomes very useful when we have a large amount of data and difficult to find.

Question 21

What part of tool is Goal seek and in which segment of the excel is?

Answer 21

If Analysis and is inside of Data tab.

Question 22

How many arguments does the Goal seek contain and what is the purpose of each?

Answer 22

Three. Sell set—> Is the output we want to have, To Value—> Desire value of to set cell and By changing cell—> The number which excel will give us

Question 23

Is Goal Seek function inside functions list?

Answer 23

No! it is inside Data tab inside if analysis.

Question 24

What is Data Table?

Answer 24

When we want to study the possibility of two inputs(factors) for one formula inside another table. First input will be on header row and the other on the header column and the final output will be on the cross section of column header and row of factors.

Question 25

We calculated a total payment amount and have a question now: What if the ratio be 10% or 11% or 12%, what should we do?

Answer 25

We should use Data Table. Meaning that creating a table out of the source that different options be on header columns…

Question 26

Where is Data Table?

Answer 26

Inside the If Analysis in the Data segment(where the Goal Seek is there too).

Question 27

What is the process of using Data Table?

Answer 27

First, we decide which factors should be studies and which one on columns and which one on rows(e.g. % on column and years on rows) and create our table.—> Second: Link the row and column headers to total amount from the source table —> Go to Data and from If Analysis select Data Table —> In needs two attributions : Header Row source(e.g. %) and Header Column source(e.g. Year).

Question 28

What is the difference between Data Table and Goal Seek?

Answer 28

Goal Seek is studying one factor or input when we clearly mention the expected output but for Data Table we study two inputs and variety of outputs inside one new table.

Question 29

What are attributions for Data Table?

Answer 29

Row Input—> The cell inside of original table. Column input—> The cell inside of original table.