Course Content

Excel: the Power of Openpyxl

Excel: the Power of Openpyxl

Formulas

Formulas are mathematical expressions or equations that can be used to perform calculations on data in Excel worksheets. The openpyxl library in Python provides methods for creating and manipulating formulas in Excel files, allowing you to automate complex calculations and data analysis tasks using Python. With openpyxl, you can apply virtually any formula that is supported by Excel itself. The library allows you to set a cell's value to a formula by entering the formula as a string, prefixed with an equal sign `=`. When the Excel file is opened, the formula will be evaluated by Excel, and the result will be displayed in the cell.

This ability includes, but is not limited to:

• Arithmetic operations: =A1 + A2, =A1 * A2, etc.
• Statistical functions: =SUM(range), =AVERAGE(range), =MIN(range), =MAX(range), etc.
• Text manipulation: =CONCATENATE(A1, B1), =LEFT(text, num_chars), =UPPER(text), etc.
• Date and time: =TODAY(), =NOW(), =DATE(year, month, day), etc.
• Lookup and reference: =VLOOKUP(value, range, col_index, [range_lookup]), =INDEX(range, row_num, [col_num]), =MATCH(value, range, [match_type]), etc.
• Logical operations: =IF(condition, value_if_true, value_if_false), =AND(logical1, [logical2], ...), =OR(logical1, [logical2], ...), etc.

1. Add a formula in cell `"P2"` that will calculate the average from cell `"H2"` to `"H100"` (`"=AVERAGE"`);
2. Save the `workbook`.