Formulas in Excel

Welcome

• this session is 🌶 - aimed at Excel beginners
• we’ll get going properly at 15.05
• you’ll need any version of Excel to follow along
• if you can’t access the chat, you might need to join our Teams channel: tinyurl.com/kindnetwork
• you can find session materials at tinyurl.com/kindtrp

The KIND network

• a social learning space for staff working with knowledge, information, and data across health, social care, and housing in Scotland
• we offer social support, free training, mentoring, community events, …
• Teams channel / mailing list

Excel training sessions

Where does this fit in?

KIND Excel beginner skill tree

• for this session, you’ll need to be familiar with the Excel basics (getting around in Excel, opening/saving/closing files)
• you’ll also need to be familiar with A1 referencing, values, and tables
• we’re going to avoid talking about formatting as much as possible today

Session outline

• values and formulas
• simple functions
• references in formulas

Values and formulas

• cells in Excel contain either values, or formulas
• we’ll start with some data derived from NRS 2022 population estimates
• make sure you have the sample data available

Values

Example value, showing identical cell contents and formula bar contents

• when you enter some data in a cell, we call that a value
• values look the same in their cell as in the formula bar

Formulas

• we can also make a value using a formula
• Excel evaluates/calculates the formula, then displays the result as a value
• in an empty cell, try entering = 2 + 2

Example formulas

• we can do ordinary arithmetic in formulas /, *, -, +…
• you can also try & which joins values
  • = "This number is " & 5

Functions

• But most formulas use functions, which are like the verbs of Excel
• Try = SUM(2, 2)

Adding functions

• you can type functions in by name (as above)

• you can also add from the Formulas area of the ribbon
  

• or you can use the \(fx\) button by the formula bar
  

Function arguments

• adding a function by \(fx\) or the menu brings up the Function Arguments interface
  

• This is a helpful builder for more complicated functions

What are arguments?

• arguments are the values we supply to a function

  • in =SUM(2,3), the arguments are 2 and 3

• we separate arguments with a comma ,

• we need to be sure we’re supplying the right arguments in the right order (this gets described as the syntax of a function)

• we also, while we’re on the jargon, say that functions return a value so = SUM(4,3) will return 7

Useful simple functions

• in the same way as =SUM(), you can try:
  • AVERAGE
  • MIN and MAX
  • SUBSTITUTE
  • CEILING
  • TODAY
  • ROUND

References in formulas

• mostly, you won’t add values directly into your formulas
• instead, you’ll bring them in from another part of your spreadsheet
• can you use SUM() to make a total Male + Female column in the supplied data?
  • add a header in cell G2
  • if needed, extend the table to include your new column
  • in G3, enter the formula = SUM(C3, D3)
  • then copy down the rows by double-clicking the green fill handle

Practice

• calculate what percentage of your total population is under 16
• estimate the population density
• find an average area for each council area

Troubleshooting functions

• #NAME? = “I don’t recognise that function name”
• #VALUE! = “Your syntax has gone wrong - usually in the wrong order”
• #REF! = “I don’t recognise that reference”
• #DIV/0! = “You’ve divided by zero”

Feedback and resources

• please can I ask for some feedback - less than 1 minute, completely anonymous, helps people like you find the right training for them