In Spreadsheets such as Excel, Google Sheets or OpenOffice Calc Formulas provide powerful tools for data analysis. Formulas are expressions that take values from a range of cells or a single cell, and output a value. A simple example is adding up the values in a range of cells to provide a total.
Advantages of Spreadsheet Formulas
What are the advantages of using a Spreadsheet with Formulas rather than a pen and paper or calculator?
Spreadsheet Formulas will instantly recalculate totals when the data in the cells being used changes. This is a very powerful feature of using spreadsheet formulas that makes them ideal for rapidly changing financial data such stocks and share prices. An example would be a complex set of calculations that take the share price and work out values through various exchange transactions. Simply changing the initial share price would result in all the linked formulas updating to show the new values.
Spreadsheet Formulas provide a useful tool for experimenting with changes in values, such as initial price of shares, exchange rates, or tax rate changes. Instead of laborious manual recalculation, or highly skilled programming, Spreadsheet Formulas provide an easy to use and implement tool for powerful data modelling, whether that be financial, or data analysis.
The results from numerical operations on the data from cells in a spreadsheet can be shown in different ways using different formulas, and using formulas on the results from other formulas.
Useful Spreadsheet Formulas & Functions
Here are some of the most common and useful spreadsheet formulas and functions.
SUM - adds up the numbers from a range of cells in columns or rows in the format =SUM(A1:A10). This would SUM all values in between A1 and A10.
COUNT - counts the number of cells in a range that have a number value in them. The format is =COUNT(E23:E36).
COUNTA - counts the number of cells in a range that have any value in them. The format is =COUNTA(E23:E36)
AVERAGE - calculates the Average of the numbers in the range of cells. The format is =AVERAGE(E23:E36).
VLOOKUP - searches down the first column of a range for a key and returns the value of a specified cell in the row found. The format is VLOOKUP(search_key, range, index, [is_sorted]) and an example is =VLOOKUP(5034,E23:F36,2, FALSE).
CONCATENATE - combines strings from selected cells, or a range, into one string. Spaces or other text can be placed between strings in the format =CONCATENATE(G4," ",G12," ",G20," ",G28).
MAX & MIN - returns the maximum or minimum value in a numeric dataset obtained from a range of cells in the format =MAX(H6:H34).
AND - logical function to check multiple criteria that returns TRUE if ALL of the conditions are met, FALSE otherwise such as =AND(E23>E36,E24>E35).
OR - logical functions to check multiple criteria that returns TRUE if ANY of the conditions are met, FALSE otherwise such as =OR(E23>E36,E24>E35).
PROPER - returns a text string from a selected cell with the first letter of each word in uppercase and all other letters in lowercase. Useful for proper nouns, such as names of people or geographic locations, but not paragraphs. Format is =PROPER(B35).
TRIM - removes leading, trailing, and repeated spaces in text. Useful when dealing with text imported from other applications that may have extra spaces at the beginning or end, or double spacing between sentences.
TODAY & NOW - inserts the current date with TODAY and the date and time with NOW.
IF STATEMENTS - returns one value if a logical expression is `TRUE` and another if it is `FALSE`. Very powerful logical expression with wide ranging uses when combined with other functions.
An example format is =IF(AND(K18>K26,K18>K34,K18>K42),"Best Profit","Small Profit").
IF statements can be nested as in the following example =IF(K10>0,IF(AND(K10>K18,K10>K26,K10>K34),"Best Profit","Small Profit"),IF(AND(K10<K18,K10<K26,K10<K34),"Biggest Loss","Small Loss")).
See our other pages on Excel Formulas, Google Sheets Formulas, and OpenOffice Calc Formulas.