Summary notes on formulas to remember to solve Excel exam questions

FORMULAS TO REMEMBER

Operations with standard formulas:

To calculate =price*(1-discount%)
discounted price

To calculate =ABS((Y2-Y1)/Y1)
percentage variation
between two years

If when you do a =SUMPRODUCT(array1;array2)/SUM(weights)
weighted average, if
the sum of the
percentages (weights)
is not 100% or 1 then
you have to write
formula

Total cash outlay = monthly instalment * 12 * term in years

Increased/decreased B4 +/- 0.5%
interest rate

Extract name from cell =LEFT(Cell;SEARCH(" ";Cell)-1)
(Name Surname)

Extract surname from =RIGHT(Cell;LEN(Cell)-SEARCH(" ";Cell))
cell (Name Surname)

Same date next year: =DATE([Cell with current year]+1,[Cell with current month],[Cell with
current day])
To verify if today is Method 1) =[cell with current date]<B4
before a certain date Method 2) =[cell with current date]<[serial number of date in B4]
(in cell B4) that will Method 3) =[cell with current date]<DATE(data of date in B4)
return FALSE or Method 4) =TODAY()<B4
TRUE:
Wrong: =[cell with current date]<"1/1/2023" or =[cell with current
date]<1/1/2023
To count how many Method 1) =COUNTIF(range,"<01/01/2023")
dates in a range are Method 2) =COUNTIF(range,"<44927")
before a certain date Method 3) =COUNTIF(range,"<"&B4)
(in cell B4), using Method 4) =COUNTIF(range,"<"&DATE(data of date in B4))
COUNTIF function: