Excel Beginners

Intro To Formulas: Step 3 To Mastering Spreadsheets

the basic toolbox everyone needs to have

Essential Functions

Statistical Functions

This section will cover five essential functions all users should learn. Take as much or as little time as needed to make sure you have a good grasp of them. Each of these functions has an intermediate or advanced counterpart to them, and understanding the ones in this guide will only help learn their more complex variations later on. Examples for each function can be found in the next section!

• Average

  • The average function calculates a numerical average (sum of items/number of items)

  • It follows this form: =AVERAGE(VALUE1, VALUE2, VALUE3, ETC…)

• Count

  • The count function looks at the value passed into it and counts up the number of values which are of the numerical data type

  • It follows this form: =COUNT(VALUE1, VALUE2, VALUE3, ETC…)

• Max

  • The max function finds the value associated to each input and returns the largest value

  • It follows this form: =MAX(VALUE1, VALUE2, VALUE3, ETC…)

• Median

  • The median function places the numerical values passed into it in ascending order, and returns the centermost value

  • It follows this form: =MEDIAN(VALUE1, VALUE2, VALUE3, ETC…)

• Min

  • The min function finds the value associated to each input and returns the lowest value

  • It follows this form: =MIN(VALUE1, VALUE2, VALUE3, ETC…)

Math & Trig Functions

This section will cover four essential functions all users should learn. These ones also have more complex variations of themselves, but the more advanced functions in this group are most commonly used in contexts more closely linked to the sciences. Examples for each function can be found in the next section!

• Round

  • The round function takes a value and rounds it to the either upwards or downwards to the specified decimal place using the number five as a cutoff value

  • It follows this form: =ROUND(VALUE, DECIMAL PLACES)

• Roundup

  • The round function takes a value and rounds it to the upwards to the specified decimal place regardless of further decimal values

  • It follows this form: =ROUNDUP(VALUE, DECIMAL PLACES)

• Rounddown

  • The round function takes a value and rounds it to the downwards to the specified decimal place regardless of further decimal values

  • It follows this form: =ROUNDDOWN(VALUE, DECIMAL PLACES)

• Sum

  • The sum function performs the addition of all the values which were used as inputs

  • It follows this form: =SUM(VALUE1, VALUE2, VALUE3, ETC…)

Logical Functions

This section will cover five essential functions all users should learn. There are not many functions which fall under the logical category, and the majority of the remaining ones will be covered in an intermediate level guide. Examples for each function can be found in the next section!

• And

  • The and function evaluates multiple Boolean comparisons and as long as they all return TRUE it will output TRUE, elsewise it will output FALSE

  • It follows this form: =AND(COMPARISON1, COMPARISON2, ETC…)

• False

  • The false function returns the Boolean value of FALSE

  • It follows this form: =FALSE()

• If

  • The if function processes a Boolean comparison, and based on whether it is TRUE or FALSE, will output one of two results

  • It follows this form: =IF(COMPARISON, VALUE IF TRUE, VALUE IF FALSE)

• Or

  • The or function evaluates multiple Boolean comparisons and as long as any of them return TRUE it will output TRUE, elsewise it will output FALSE

  • It follows this form: =OR(COMPARISON1, COMPARISON2, ETC…)

• True

  • The true function returns the Boolean value of TRUE

  • It follows this form: =TRUE()

Text Functions

This section will cover three essential functions all users should learn. There are many text functions which hold extremely specific use cases. A small portion of which perform tasks whose outputs are similar to those found in other categories, with the exception that the outputs are formatted as text instead of a number value for instance. Examples for each function can be found in the next section!

• Left

  • The left function will return a subset of a value, starting from the leftmost character and returning the number of characters specified

  • It follows this form: =LEFT(STRING, NUMBER OF CHARACTERS)

• Mid

  • The mid function will return a subset of a value, starting from the signaled character, and returning the number of characters specified

  • It follows this form: =MID(STRING, START POSITION, NUMBER OF CHARACTERS)

• Right

  • The right function will return a subset of a value, starting from the rightmost character and returning the number of characters specified

  • It follows this form: =RIGHT(STRING, NUMBER OF CHARACTERS)

Date & Time Functions

This section will cover six essential functions all users should learn. Date and time values are very tricky as they do not behave as most users expect them to. Given that date values are integer values, and time values are decimal values, these can cause confusion when they are mistakenly adjusted without knowledge of this. Examples for each function can be found in the next section!

• Date

  • The date function takes three values and joins them to create a date value

  • It follows this form: =DATE(YEAR VALUE, MONTH VALUE, DAY VALUE)

• Day

  • The day function returns the day number from a valid date field

  • It follows this form: =DAY(DATE VALUE)

• Month

  • The month function returns the month number from a valid date field

  • It follows this form: =MONTH(DATE VALUE)

• Now

  • The now function returns the current system date and time, it updates every time the document does

  • It follows this form: =NOW()

• Today

  • The today function returns the current system date, it updates every time the document does

  • It follows this form: =TODAY()

• Year

  • The year function returns the year from a valid date field

• It follows this form: =YEAR(DATE VALUE)