Microsoft Excel: Advanced Functions and Complex Formulas

Study Notes

Microsoft Excel

A software program produced by Microsoft that allows users to organize, format, and calculate data with formulas using a spreadsheet system.

Advance and Complex Calculations in Excel

A function is a predefined formula that performs calculations using specific values in a particular order.
The parts of a function include: inputs, operators, and output.
Complex formula is the combination of more than two simple formulas.

Basic Computations in Excel

Four basic computations used in Excel: Addition (+), Subtraction (-), Multiplication (*), and Division (/).
Excel follows the PEMDAS (Parentheses, Exponents, Multiplication and Division, and Addition and Subtraction) order of operations.
Example: 10 + (6-3)/2 ^2*4-1 = 12

Formula Errors in Excel

7 common error values that can occur due to faulty formulas:
#DIV/0!: occurs when dividing by zero or referring to a blank cell.
#####: occurs when the column is not wide enough to display the content, especially with dates and times.
#NAME?: occurs when referring to a non-existent range name in the worksheet.
#N/A: occurs when an inappropriate value is given for the lookup value argument in functions like HLOOKUP, LOOKUP, MATCH, or VLOOKUP.
#NULL!: occurs when inserting a space instead of a comma to separate cell references in functions.
#NUM!: occurs when Excel encounters a problem with a number in the formula.
#REF!: occurs when Excel encounters an invalid cell reference.
#VALUE!: occurs when using the wrong type of argument or operator in a function, or referring to cells containing text entries.

Applied Productivity Tools with Advance Application Techniques

Custom Animation

Animation is a simulation of movement created by displaying a series of pictures or frames.
Animation can be created using software applications like presentation software (e.g., WPS Office, LibreOffice, MS PowerPoint).
Steps to apply animation effects to an object in a presentation:
Select the object to animate.
Go to the Slide Show tab, click the Animations tab, and select the desired effect.
Preview the animation by clicking the Preview button.

Description

Explore the advanced functions and complex formulas in Microsoft Excel. Learn how to perform calculations, organize data, and create powerful spreadsheets with predefined formulas. Test your knowledge on calculating complex formulas and utilizing Excel's key features.