MCPHERSON - EXCEL FORMULAS & FUNCTIONS 2023

An expression called FORMULAS IN EXCEL operates on values from a variety of cell addresses and operators. Consider the formula =A1+A2+A3, which calculates the sum of the values in cells A1 through A3. An illustration of a formula with discrete values might be =6*3.

=A2 * D2 / 2

"=" tells Excel that this is a formula, and it should evaluate it.

"A2" * D2" makes reference to cell addresses A2 and D2 then multiplies the values found in these cell addresses.

"/" is the division arithmetic operator

"2" is a discrete value

To determine the subtotal, we will use the sample data from the household budget.

• Make a new Excel workbook.
• Enter the information from the budget for household goods above.
• The following should appear on your worksheet.

• The formula for determining the subtotal will now be written.
• Set cell E4 as the focus.

Enter the formula below.

=C4*D4

HERE,

The value of the cell addresses C4 and D4 are multiplied using the arithmetic operator multiplication (*) in the formula "C4*D4".

• Hit the Enter key.

The outcome will be as follows.

You can see how to automatically select a cell address and apply the same formula to additional rows in the animated image below.

The Division, Multiplication, Addition, and Subtraction Brackets rules should be kept in mind (BODMAS). Since they are enclosed in brackets, expressions are now evaluated first. Division is evaluated first for arithmetic operators, followed by multiplication, addition, and finally subtraction. The formula above can be rewritten as =(A2 * D2) / 2 by applying this rule. By doing this, it will be made sure that D2 and A2 are split by 2 after evaluation.

Data validation allows you to specify the type of data that should be allowed by a cell, such as only numbers, as spreadsheet formulas in Excel typically work with numeric data. You can hit F2 on the keyboard to confirm that you are using the right cell references listed in the formulas. The cell addresses used in the calculation will be highlighted, and you may cross-check them to make sure they are the correct cell addresses.

When working with a large number of rows, you can assign serial numbers to each row and keep track of the total count at the bottom of the sheet. To make sure that your calculations took into account all the rows, compare the serial number count with the number of records.

Using specified values in a given order, the predefined formula FUNCTION IN EXCEL is applied. Using a function, you may quickly determine the sum, count, average, maximum value, and minimum value for a set of cells. The SUM function, for instance, calculates the sum of the values in the range A1:A2 in cell A3 below.

SUM for adding up a number range

Use AVERAGE to determine the average of a specified range of integers.

Count to determine how many objects fall within a certain range.

How important functions are

When using Excel, functions boost user productivity. Let's imagine you want to know the overall cost of the home supply budget mentioned above. You can calculate the sum using a formula to make it easier. You would have to make each reference to the cells E4 through E8 individually in a formula. You would need to apply the subsequent formula.

= E4 + E5 + E6 + E7 + E8

With a function, you would write the above formula as

=SUM (E4:E8)

As you can see from the function used above to calculate the sum of a set of cells, employing a function to calculate the sum is far more effective than using a formula, which would require referencing a lot of cells.

The most significant Excel formulas and functions are shown here.

SUM function = =SUM(E4:E8)

MIN function = =MIN(E4:E8)

MAX function = =MAX(E4:E8)

AVERAGE function = =AVERAGE(E4:E8)

COUNT function = =COUNT(E4:E8)

DAYS function = =DAYS(D4,C4)

VLOOKUP function = =VLOOKUP (C12, A4:B8, 2, FALSE)

DATE function = =DATE(2020,2,4)