Appunti su Excel

SUMIF

The SUMIF function adds up values that meet a specific condition. It is useful

for summing numbers based on criteria like text, numbers, or dates.

=SUMIF(range, criteria, [sum range])

Syntax :

1. Range range of cells to check for the condition



2. Criteria condition to match



3. Sum range the range of values to add —if omitted: sum up values



from range !!

SUMIFS

The SUMIFS function sums values based on multiple conditions across one or

more ranges. It is an extension of SUMIF, allowing for more complex criteria.

=SUMIFS(sum range, range 1, criteria 1, …, range N, criteria

Syntax :

N) 1. Sum range range of numbers to sum



2. Range 1 first range to evaluate



3. Criteria 1 condition that must hold in range 1

 10

ALESSANDRA COLLOCA - BIEF

4. Range N N range to evaluate



5. Criteria N condition that must hold in range N



Note : each range must be of the same size !!!

Example:

AVERAGEIF

The AVERAGEIF function calculates the average (arithmetic mean) of values

that meet a specific condition.

=AVERAGEIF(range, criteria, [average range])

Syntax :

1. Range range of cells to check for the condition



2. Criteria condition to match



3. Average range range of number to average if the condition holds —if



omitted: averages values from the range

AVERAGEIFS

The AVERAGEIFS function calculates the average (arithmetic mean) of values

that meet multiple conditions across one or more ranges. It is an extension of

AVERAGEIF, allowing for more complex filtering. 11

ALESSANDRA COLLOCA - BIEF

Syntax : =AVERAGEIFS(average range, range 1, criteria 1, …, range N,

criteria N)

1. Average range range of numbers to average



2. Range 1 first range to check



3. Criteria 1 condition that must hold in range 1



4. Range N N range to check



5. Criteria N condition that must hold in range N



Note : each range must be of the same size !!!

Example :

NOTES :

Criteria must be written between “”

 Need to lock the ranges !!!!



PIVOT TABLES

A Pivot Table helps summarize and analyze data dynamically.

a. Select data —make sure that data has headers !!

b. Insert > pivot table

c. How to build pivot table :

- Rows = groups data (ex: product, region)

- Columns = categories

- Values = summarizes data (ex: sum of sales)

- Filters

Example 1: sales summary by product 12

ALESSANDRA COLLOCA - BIEF

Example 2: sales by product and region with filters

Criteria that sort the data : rows / columns (tabella a doppia entrata)

TEXT FUNCTIONS

1. LEFT function : extracts a specific number of characters from the

start (left side) of a text string

=LEFT(text, num char)

Syntax :

1. Text original text or cell reference



2. Num char number of characters that want to extract —if omitted: 1



default !!!

Example: 13

ALESSANDRA COLLOCA - BIEF

2. RIGHT function : extracts a specific number of characters from the

end (right side) of a text string

=RIGHT(text, num char)

Syntax :

1. Text original text or cell reference



2. Num char number of characters that want to extract —if omitted: 1



default !!!

Example:

3. MID function : extracts characters from any position in a text string

=MID(text, start num, num char)

Syntax :

1. Text original text or cell reference



2. Start num the position to start extracting from



3. Num char number of characters that want to extract



Example:

4. CONCATENATE function : joins multiple text strings into one.

=CONCATENATE(text 1, text 2, …, text N)

Syntax :

1. Text 1-2-N text or cell reference to join together



Note : to insert a space type “ “ !! 14

ALESSANDRA COLLOCA - BIEF

Example:

5. & function : joins multiple text strings together

=text 1 & text 2 & … & text N

Syntax :

1. Text 1-2-N text or cell reference to join together



Example:

6. UPPER function : converts all letters in a text string to uppercase

=UPPER(text)

Syntax :

1. Text text or cell reference to convert to uppercase



Example:

7. LOWER function : converts all letters in a text string to lowercase

=LOWER(text)

Syntax :

1. Text text or cell reference to convert to lowercase

 15

ALESSANDRA COLLOCA - BIEF

Example:

8. PROPER function : capitalizes the first letter of each word, making it

title case.

=PROPER(text)

Syntax :

1. Text text or cell reference to convert



Example:

9. LEN function : counts the number of characters in a text string,

including spaces, punctuation, and numbers.

=LEN(text)

syntax :

1. Text text or cell reference to measure



Note : spaces count as characters !!!

Example:

10. SEARCH function : finds the position of a specific character or

text inside another text string

=SEARCH(find text, within text, [start num])

Syntax :

1. Find text text you want to find

 16

ALESSANDRA COLLOCA - BIEF

2. Within text text or cell to search in



3. Start num the position to start searching from —if omitted: =1



Note : case insensitive !! (excel = EXCEL)

Example:

11. TRIM function : removes all extra spaces from text, leaving only

single spaces between words

=TRIM(text)

Syntax :

1. Text text or cell reference where spaces should be removed



Example:

DATES AND TIME FUNCTIONS

YEAR function : extracts the year from a date

 =YEAR(cell)

Syntax :

MONTH function : extracts the month (1-12) from a date

 =MONTH(cell)

Syntax :

DAY function : extracts the day from a date

 =DAY(cell)

Syntax : 17

ALESSANDRA COLLOCA - BIEF

HOUR function : extracts the hour (0-23) from a time

 =HOUR(cell)

Syntax :

MINUTE function : extracts the minute (0-59) from a time

 =MINUTE(cell)

Syntax :

SECOND function : extracts the second (0-59) from a time

 =SECOND(cell)

Syntax :

Example:

WEEKDAY function : returns the day of the week as a number

 =WEEKDAY(serial number, [return type])

Syntax :

1. Serial number date value



2. Return type defines the numbering system for days —default:



1=Sunday !!

Example:

DATE / TIME FORMATS

 18

ALESSANDRA COLLOCA - BIEF

DATE function : creates a date from year, month, and day values

 =DATE(year, month, day)

Syntax :

1. Year ex: 2025



2. Month 1-12



3. Day 1-31



Example:

DATEDIF function : Calculates the difference between two dates in

 years, months, or days

Syntax : =DATEDIF(start date, end date, unit)

1. Start date earlier date



2. End date later date



3. Unit type of difference :



- Y = years

- M = months

- D = days

- YM = months excluding years

- YD = days excluding years

- MD = days excluding months and years 19

ALESSANDRA COLLOCA - BIEF

Example:

TODAY function : returns the current date without the time

 =TODAY()

Syntax :

Example:

NOW function : returns the current date and time

 =NOW()

Syntax :

Example:

FINANCIAL FUNCTIONS

[ FRENCH AMORTIZATION MODEL : constant payment + constant

interest rate

Composition of installments changes progressively: installment

made up of two parts

1) Interest amount: this is calculated on the residual debt (i.e. on

the amount still to be repaid). At the beginning it is high,

because the residual debt is greater. 20

ALESSANDRA COLLOCA - BIEF

2) Principal amount: this is the part of the installment that

actually reduces the outstanding debt. It is lower at first, but

increases over time.

The main features of this type of fixed-rate mortgage are therefore:

- Fixed installments: you pay the same amount every month

- More interest at the beginning: in the early years, you pay

more interest and less principal

- More principal towards the end: as the debt is reduced, the

interest decreases and the principal amount increases ]

WHAT-IF ANALYSIS = decision making technique used to evaluate the effects

of different scenarios on an outcome by changing variables. It helps in

assessing risks, predicting future results, making informed choices by

exploring various possibilities.

- GOAL SEEK : allows to find what value a cell must contain in order to

obtain the desired result

Data > what if analysis > goal seek

it automatically changes the value in order to obtain the

desired result

- SCENARIO MANAGER : allows to examine different possible outcomes

by adjusting an input

Data > what if analysis > scenario manager

PMT function (payment) : calculates the periodic payment for a loan

 or investment based on a constant interest rate and a constant

payment schedule.

It tells you how much you need to pay each month (or year,



depending on the period) to pay off a loan or how much you'd receive

periodically from an investment. 21

ALESSANDRA COLLOCA - BIEF

=PMT(rate, nper, pv, [fv], [type])

Syntax :

1. Rate interest rate per period



–note : if I have annual interest rate, need to divide it by the number of

payments per year !!

2. Nper total number of payment periods



3. Pv (present value) total amount of the loan or investment in the

present

4. Fv (future value) cash balance you want after the last payment is



made —if omitted: 0 (typical for loans) ; with savings it is how much

you want to save

5. Type when payments are due



a. 0 = end of the period

b. 1 = beginning of the period

Example: monthly loan payment calculations

FV function (future value) : calculates the future value of an

 investment or loan based on periodic, constant payments and a fixed

interest rate

=FV(rate, nper, pmt, [pv], [type])

Syntax : 22

ALESSANDRA COLLOCA - BIEF

1. Rate interest rate per period



2. Nper total number of payment periods



3. Pmt payment made each period (negative if it is an outgoing



payment)

4. Pv present value or initial investment —if omitted: 0 !!



5. Type when payments are due



a. 0 = end of the period

b. 1 = beginning of the period

Example: future value of an investment with regular deposits

PPMT function (principal payment) : calculates the principal

 portion of a payment for a specific period in a loan

=PPMT(rate, per, nper, pv, [fv], [type])

Syntax :

1. Rate interest rate per period



2. Per period for which you want to calulate the principal



3. Nper total number