Part 2: Creative functions
2.1 Named ranges
Most formulae need to reference cells or ranges. An alternative to the usual reference style is to assign names which are used in place of the cell or range address. They help reduce errors by:
making spreadsheet formulae more meaningful
avoiding the need for ‘dollar’ references
2.1.1 Creating named ranges
The methods are different in Excel and Google Sheets, but you should always first select the cell or range to be named. If you are working with listed values and the number of rows in use may change, select entire columns rather than just the rows currently in use.
2.1.2 Using named ranges
The names may now be used anywhere in a formula or function where a cell reference or range would normally be used.
2.2 Conditional functions
Conditional functions check whether a value (usually a value in a cell) meets a logical test where the outcome can be either true or false. They then generate a result based on that true/false outcome.
2.2.1 IF()
The IF function allows you to compare values and give a different response depending on the outcome. It needs three parameters (or 'arguments'):
=IF( An expression that is true or false, Result if true, Result if false )
The 'Result' portions of the above can be:
a value (number or "text") that you wish to be placed in the spreadsheet as the result of the function
a reference to a cell containing a value
further formula to perform a calculation.
Suppose there are 3 possible outcomes to an exam:
Since one IF function only has two outcomes, it cannot give all three possibilities. However, combining two IF functions, one inside another, lets you test for all three results.
Our decision flowchart might now look something like this:
=IF(A1<60,"Fail",IF(A1>60,"Distinction","Pass"))
Nesting can be applied with any functions and is a very powerful technique; however the formulae involved can easily become confusing. Always test your formula carefully to make sure you get the expected results.
2.2.2 Getting conditional with the basic functions (SUM, COUNT, etc.)
All of the basic functions we looked at earlier (the ones on the Σ "Functions" menu) have variants that incorporate conditional tests. With these, cell values can be summed, averaged, counted, etc., only if they meet certain defined criteria.
2.3 Time and date calculations
We saw earlier how dates are stored as whole numbers and times are stored as decimal fractions. This means that we can perform calculations using dates and times.
When performing calculations with dates and times, be aware that you'll have to format the results appropriately. The spreadsheet won't necessarily format your answer as a date/time by default.
2.3.1 Basic calculation with dates
Since dates are represented as whole numbers, adding or subtracting a whole number to a date will give a different date. Subtracting one date from another will give the number of days between those dates:
2.3.2 Time and duration
By default, times are treated as a time of day, so 12:00 means noon. But time can also be used as a duration, and can be used to calculate new times, even on a new day:
2.3.3 Date and time functions
Spreadsheets include some functions that are able to refer to the computer’s internal calendar and clock. These functions are unusual in that they require no arguments:
These values change with the day/time, so cannot be used as a date/time stamp. These functions can be used in calculations, for example to find out how many days there are until a deadline is due:
Several other functions allow you to deconstruct and reconstruct date or time values:
