SUM
This function returns the sum of the cells or
range selected
=SUM(Number 1, Number 2...)
Example
Projects Development cost Operational cost Training cost Total cost
Project 1 9400 5800 3100
Project 2 2600 4900 2900
Project 3 5500 7200 8200
Project 4 7800 4600 7300
Project 5 2900 2800 1600
AVERAGE
This function returns the average value of the
given numbers - It ignores text values and blank
cells
=AVERAGE(Number1,Number2 and so on..)
Example
Projects Development cost Operational cost Training cost
Project 1 9400 5800 3100
Project 2 2600 4900 2900
Project 3 5500 7200 8200
Project 4 7800 4600 7300
Project 5 2900 2800 1600
Average
TRIM
The function removes spaces all before and following
the words i.e. leading and trailing spaces - and also
removes extra spaces between words but does not
remove the single space between words.
=TRIM(Text)
Example
String Result Formula
Start-Tech Academy =TRIM(L5)
Start-Tech Academy =TRIM(L6)
Start-Tech Academy =TRIM(L7)
CONCAT
This function joins several (more than 1) text
string into 1 single string
=CONCAT(String1, and so on..)
OR
=String1&String2& so on..
Example
Projects Development cost Operational cost Training cost
Project 1 9400 5800 3100
Project 2 2600 4900 2900
Project 3 5500 7200 8200
Project 4 7800 4600 7300
Project 5 2900 2800 1600
Average 5640 5060 4620
Concat
&
VLOOKUP
VLOOKUP function looks up a supplied value in the
first column of a table, and returns the corresponding
value from another column
=VLOOKUP( lookup_value, table_array,
col_index_num, [range_lookup] )
Example
Projects Development cost Operational cost Training cost Total cost Est. Benefits
Project 1 9400 5800 3100 18300
Project 2 2600 4900 2900 10400
Project 3 5500 7200 8200 20900
Project 4 7800 4600 7300 19700
Project 5 2900 2800 1600 7300
Projects Est. Benefits
Project 6 27900
Project 2 18600
Project 4 24600
Project 12 29700
Project 13 19500
Project 3 25500
Project 5 30000
Project 1 13500
IF Example
It checks whether a condition is true or not and
on the basis of that returns a value
=if(Logical_Test, [value_if_true],
[value_if_false])
Example
Projects Development cost Operational cost Training cost Total cost Est. Benefits
Project 1 9400 5800 3100 18300 13500
Project 2 2600 4900 2900 10400 18600
Project 3 5500 7200 8200 20900 25500
Project 4 7800 4600 7300 19700 24600
Project 5 2900 2800 1600 7300 30000
Profitable
COUNTIF
This function returns the count of number of
cells which consist of numbers and meet a given
condition
=COUNTIF(Range,Criteria)
Example
Projects Development cost Operational cost Training cost Total cost
Project 1 9400 5800 3100 18300
Project 2 2600 4900 2900 10400
Project 3 5500 7200 8200 20900
Project 4 7800 4600 7300 19700
Project 5 2900 2800 1600 7300
How many projects have estimated benefits of more than 22000?
How many projects have estimated benefits of more than 22000 and total cost less than 20000?
Est. Benefits
13500
18600
25500
24600
30000
al cost less than 20000?
SUMIF Exampl
This function returns the sum of the cells which
meet a given criteria
=SUMIF(Range, Criteria, [sum_range])
Example
Projects Development cost Operational cost Training cost Total cost Est. Benefits
Project 1 9400 5800 3100 18300 13500
Project 2 2600 4900 2900 10400 18600
Project 3 5500 7200 8200 20900 25500
Project 4 7800 4600 7300 19700 24600
Project 5 2900 2800 1600 7300 30000
What is the total cost of projects which have estimated benefits of more than 22000?
What is the total cost of projects which have estimated benefits of more than 22000 and development cost
less than 6000?
and development cost
