Please enable JavaScript.
Coggle requires JavaScript to display documents.
Excel - Basic formulas - Chapter 2 - Coggle Diagram
Excel - Basic formulas - Chapter 2
Types of data
Alphanumerical
Letters, numbers or a combination
No calculations can be performed on it
Numerical
Only numbers
Basic formulas in Excel
Calculates the maximum value contained in the cells in a selection
=max(range)
=max(A1:A6)
=12
Counts the cells in a selection containing only numerical values
=count(range)
=count(A1:A6)
=4
Calculates the minimum value contained in the cells in a selection
=min(range)
=min(A1:A6)
=2
Counts the cells in a selection containing both numerical and alphanumerical values
=counta(range)
=counta(A1:A6)
5
Calculate the average value of the content of the cells in a selection
=average(range)
=average(A1:A6)
=6.25
Returns the current date on which the spreadsheet is viewed
=today()
=today()
=20/8/2017
Add the content of all the cells in a selection (range)
=sum(range)
=sum(A1:A6)
=25
E.g.
A
1 => 5
2 => 6
3
4 => 12
5 => 2
6 => abc
Relative reference
A5 = A1+A2, if this formula was coppied to B5 the formula would automatically change to B5 = B1+B2
Basic mathematical formulas
Equals
=
Greater than
Divide
/
Multiply
*
Smaller than
<
Not equal to
<>
Percentage
%
Greater than and equals / smaller than and equals
= / <=
Plus or minus
/ -
Exponent
^
Cell addresses
Letter and numbers that refer to the cell's location in the worksheet
E.g. A5 refers to the cell in the fifth row in column A
Copying content using the fill handle
Move the mouse pointer to the bottom right hand corner of the selection
The mouse pointer will change from an open plus to a coloured in cross.
Select the range of cells you wish to repeat
Left click with your mouse and drag the selection to the desired location
Error messages
hashtag (#) DIV/0
Divide by 0
Hashtag (#) REF!
Reference to cell that does not exist
hastag (#) NAME?
Mis-spelt function name
Hashtag (#) N/A
Value is not available
hastag (#) VALUE
Value in formula is the wrong data type
Absolute reference
A5=$A$1+A2, if yhis formula was copied to B5 the formula would change to B5=$A$1+B2. The $ sign makes the reference to A1 absolute. Note that the $ sign is before the column reference and before the row reference. Nothing is in between the $ signs
Month formula
=month(reference to cell containing date)
Functions
=SEARCH(find_text,within_text,start_num)
Finds one text string within another text string (not case sensitive)
=SUBSTITUTE(text,old_text,new_text,instance_num)
Substitutes new text for old text in a text string
+RIGHT(text,num_chars)
=RIGHT(A1,3)
Returns the rightmost characters in a c=text string using num_chars as the starting point
=TEXT(value,format_text)
Converts a value to text in the specified number format
=REPT(text,number_times)
Repeats a text string a specified number of times
=TRIM(text)
Removes spaces from a text string, except for spaces between words. Useful when importing data from an external data source
=REPLACE(old_text,start_num,num_chars,new_text)
Replaces part of a text string with a new text string based on the number of characters specified in num_chars
=UPPER(text)
Converts lowercase letters in a text string to uppercase
=PROPER(text)
Capitalizes the first letter in each word in a text string and converts all other letters to lowercase. Very useful when data has been improperly stored as uppercase characters
=VALUE(text)
Converts a text string that represents a number (a number, date, or time) to a number
=MID(text,start_num, num_chars)
=MID(A1,7,4)
Returns a specific number of characters in a text string using start_num as the starting point
=ISBLANK
Value refers to an empty cell
=LOWER(text)
Converts uppercase letters in a text string to lowercase
=ISERR
Value refers to any error value except #N/A
=LEN(text)
=LEN(A1)
Returns the number of characters in a text string - how many characters there are in a text
=ISNA
Value refers to the #N/A (value not available) error value
=LEFT(text, num_chars)
=LEFT(A1,6)
Returns the leftmost character in a text string using num_chars as the starting point
=Time(hour, minute, second)
Returns a decimal number for a specific time
=FIXED(number, decimals, no_commas)
Rounds a number to a specified number of decimals and returns the number as text with commas and a period
=Today()
Returns the serial number for today's date
=FIND(find_text, within_text, start_num)
Finds one text string within another text string (case sensitive) using the start number character as the starting point
=EXACT(text1, text2)
Compares two text stings to determine if they are identical (case sensitive)
=Yearfrac(start_date, end_date)
Returns the number of years between two dates as a fraction
=DOLLAR(number, decimals)
Converts a number to text in currency format with a dollar sign and the specified number of decimal places
=Now()
Returns the serial number of the current date and time
=Round(number,num_digits)
Rounds a number to a specified number of digits
=CONCATENATE(text1, text2, .....
Joins two or more text strings into a single text string
=CONCATENATE(A1,"@sun.ac.za
")