قالب وردپرس درنا توس
Home / Tips and Tricks / Excel & # 39; s Top 12 Most Popular Formulas with Examples

Excel & # 39; s Top 12 Most Popular Formulas with Examples



Excel has 475 formulas in its library of functions, from simple math to very complex statistical, logical, and technical tasks such as IF instructions (one of our eternal favorite stories); AND, OR, NOT works; COUNT, AVERAGE and MIN / MAX.

The following basic functions are the 12 most common formulas in Excel. To help you learn, we have also provided a table with all the formula examples below.

download

This workbook contains a tab for each common formula that is discussed in this article, with sample data.

. 1
TODAY / NOW

The Formulas> Date and Time drop-down menu lists 24 date and time functions: 11 date formats, 10 time formats, and so many custom custom formats that you can create. The TODAY function shows the current month, day and year; while the NOW function displays the current month, day, year and time. This is a useful feature if you are one of those people who always forget to work with their work.

. 1 Enter the following formula in cell A1: = TODAY () and press Enter

2. Next, tap this function in A1 with = NOW ()

IMPORTANT NOTE: Why are you typing? For these two formulas to work properly, they must be entered in the Home cell, which means A1, otherwise they will not refresh automatically when the worksheet is recalculated. Press Shift-F9 to calculate / recalculate only the active table, or press F9 for the entire workbook

After entering one of these functions in A1, You can reformat the date and time or use the system default. The default format for the TODAY function is 29.08.18 and the default for NOW is 29.08.18 21:57. If these do not work for you, change them.

. 3 Position the cursor on the date or time you want to change and select Home > Format > Format Cells .

. 4 In the format Cells dialog box, select date (or time ) from the category category under the number tab ,

. 5 Scroll through the list of date / time formats in the Type dialog box and select the most appropriate format for your project.

  01 Date and Time Functions and Formats JD Sartain / IDG Worldwide

2. SUM Functions

Probably the most commonly used function in Excel (or any other spreadsheet program), = SUM does just that: it sums up a column, row, or range of numbers – but it does not add up. It subtracts, multiplies, divides, and uses one of the comparison operators to return a result of 1 (true) or 0 (false).

You can get the same results only with the plus sign (+) of the SUM function. For example, both formulas produce the same answer: = SUM (J7 * 9) and = + (J7 * 9). In the following table, note that cells E3 through E8 use the SUM function, while cells E9 through E9 through E14 use the plus sign (+) and the results are the same.

You can manually enter the function SUM (or + sign) or choose Ribbon menu under Formulas > Math & Trig (button), then from the drop-down list ; or select (from the Ribbon menu) Formulas > Insert function then scroll down the list and select from there

If you only want to add a single number column, position Position the cursor in the cell at the bottom of this column, click the AutoSum > SUM and press Enter . Excel framing the number column in green boxes displays the formula in the current cell.

  02 Sum functions with multiple arithmetic operators JD Sartain / IDG Worldwide

The problem occurs when the number of numbers you need The calculation is complicated with multiple arithmetic operators across multiple cells. For example: = SUM (H1 + I1 * J1-M1 * J1. Remember your high school math? If the numbers in the formula are not properly grouped, the answer is wrong. Screenshot below (Figure 2)

Enter enter the following column headers in H2 through P2 (use Alt + Enter to stack headers in a single cell): daily receipts, bonuses, processed times, gross pay, (formula), minus meals at $ 9.00 per day, Monthly Total Income, Formula and Comment

NOTE: The formula columns are for the info sheet only and do not represent an intrinsic value for the spreadsheet. They merely "show" the formula for your benefit (you can use the syntax of ea see ch-formula used)

For this exercise, you can enter the same values ​​in H3: 11, I3: 11, and J3: 11, with or without blank lines in between (added again to make the display easier) You like this off: $ 86.00, $ 20.00, 22.0 working days, and the rest are formulas. Note that when we create each formula, we eventually combine the steps into a single formula.

We start with three separate formulas. The first is to add daily earnings plus bonuses multiplied by the number of days worked in a month, which is the gross salary: = SUM (H3 + I3 * J3) in cell K3. Note that the answer is $ 526.00. That just does not look right.

Use your calculator to check the formulas to make sure they are correct BEFORE copying them into the remaining cells in the column.

The formula in K3 is wrong. It requires grouping the numbers according to the order of computation with commas or parentheses.

Note the corrected formula in cell K4: = SUM (H4 + I4) * J4. Check your numbers again (with your calculator) and note that this formula is correct. The correct answer is $ 2,332.00.

. 5 The second formula (in M4) is = SUM (J4 * 9) multiplies the working days (22) times $ 9.00, the cost of meals per day. The correct answer is $ 198.00

6. The third formula (in N4) calculates the monthly income less meals: = SUM (K4-M4); The answer is $ 2,134.00

7. In the next group (H6: N8), the formulas in M6: M8 remain the same: = SUM (J7 * 9), etc. – that's the number of working days multiplied by meals. But the formulas in column K are eliminated and then combined with the formulas in column M: = SUM (H7 + I7) * J7-M7. Note that the syntax (the structure or layout of the formula) is correct in cells N7 and N8, but incorrect in N6.

. 8 The next group (H10: H11) combines the formulas in column M with the formulas in column N: = SUM (H11 + I11) * J11- (M11 * J11) – Note that the formula in N10 is false. By combining these formulas into one, you can eliminate the K and L columns.

. 9 Instead of "coding" the price of meals (as shown in M3: M4 and M6: M8), you can now change the price of meals in column M (M10: M11) if inflation dictates an increase rather than a change the formula

  03 Complex Sum Functions with Multiple Arguments JD Sartain / IDG Worldwide

3. RAND function

The RAND function is very simple and is traditionally used for, among other things, statistical analysis, cryptography, games, gambling, and probability theory. In Excel, the RAND function generates a random number between 0 and 1. Note; However, if you enter new data and press Enter, the list of random numbers you just created changes. If you need to maintain your random number lists, you must format the cells as values.

. 1 Enter the function = RAND () in columns A3 to A14. Select this column and press Ctrl + C (to copy) or click the Copy button at Home and select Copy from dropdown menu. Move the cursor to cell B3 and select Home > Paste > Paste Special . In the Paste Special dialog box, click the Values ​​ button, and then click OK .

. 2 Now the list contains values ​​instead of functions, so it does not change. Note (in the formula bar) that the random numbers have 15 decimal places (Excel is 9 by default), which you can change as needed (as shown in cell F3). Just click the Decimal Increase button in the Number tab Home .

. 3 If you prefer to work with integers, enter this formula in cell F3: = INT (RAND () * 999) and you will get a three-digit random number. Copy the formula over F12, then add another "9" to the string to add another number to your random number – eg. For example, four nine are four digits, five nine are five digits. Again, you must copy the list and paste it as values ​​to create a static list.

  04 Use the Rand feature to create random lists JD Sartain / IDG Worldwide

4. COUNT functions

Use the COUNT function to count the number of numeric values ​​in a range of cells; For example: C4: C15 contains the amount of gardening equipment Mr. McGregor needs to order for his business. Note that the answer is 10 (out of 12) because the COUNT function does not contain empty cells. However, if you enter a zero, numeric code, or date, Excel counts it as a "busy" cell and inserts it into its response.

Enter 10 numbers in column C (Quantity). Replace a number with a space (or tap the space bar), replace a different number with a semicolon, and enter a date in cell C7

Enter this formula at the end of the number list (C16): = COUNT (C4: C15) . The answer is 10 (out of 12) because Excel counted all the numbers and the date, but ignored the blank cell (with spaces) and the punctuation in cell C8.

Use the COUNTA function if you want to include numeric values, logical or error values, text, a space (space bar), punctuation, symbols, or other characters on your keyboard.

. 1 Enter $ 12 in column D (price). Replace one cell with a question mark, another cell with a symbol, and another cell with text.

. 2 Enter this formula in D16: = COUNTA (C4: C15) . The answer is 12 (out of 12) because Excel contained all "non-numeric" values ​​and characters.

. 3 Note that in line 18 (C and D) the actual formulas are displayed in C and D 16.

  05 count counta counts the number of cells containing something JD Sartain / IDG Worldwide

5. AVERAGE function

Almost everyone knows that an average is found by adding all the values ​​in a list and then dividing by the number of listed values. z. 4 + 5 + 3 = 12/3 = 4, which is the average. You can use the SUM function to add the division all in one formula, or you can just use the AVERAGE function. The syntax is: = AVERAGE (range).

. 1 Enter some numbers in column A. Enter the AVERAGE function at the bottom of the list: = AVERAGE (A4: A13) and note the answer (in our case) 53. You can confirm your answer with the button SUM function; that is: = SUM (A4: A13 / 10) = 53.

2. Next, enter some more numbers in column C, but this time insert one text into a cell, one punctuation into another, and one space into another cell. Enter the same formula: = AVERAGE (A4: A15) and note that the answer is 78. To confirm, enter the SUM formula without the cells that contain non-numeric characters:

Cells that contain text logical values, punctuation, or empty cells are disregarded; but cells with zeros (as a number but not as text) are included. A text zero would have an apostrophe before the zero, which you can not see in the cell, but is visible in the formula bar. IMPORTANT NOTE: When you import huge databases from a mainframe or an external, external source, sometimes the numbers are exported as text. How do you know if a number is really text? In general, text is left-aligned and numbers are right-aligned, but since everyone now formats their tables for aesthetics, this method is unreliable. Another option is to quickly browse through a long list of imported numbers and view the formula bar. If you see apostrophes before the numbers, these entries are text. Lastly, look for the green triangle in the upper left corner of the cell. If the previous owner of the Excel spreadsheet did not tell you to ignore this error, the contents of the cell are text.

If the values ​​are text, you must immediately convert them to numbers. To do this, move the first number in the list, which is actually text. Select the text area that represents itself as a number. Right-click on the yellow warning sign located to the left of the first text cell in the area. Click Convert to Number from the pop-up list and it's done.

  06 the average function JD Sartain / IDG Worldwide

6. MIN / MAX Functions

Use the MIN function to find the smallest number in a range of values, and the MAX function to find the highest one. The syntax for these functions is: = MIN (range); = MAX (range), where the range corresponds to the list of numbers you calculate.

This feature is commonly used. B. find the highest / lowest grade in a classroom; the highest / lowest sales dollars in a store; the highest / lowest average scores of your favorite baseball team; and so on.

Some would ask, why not just sort the data? You could, but every time the numbers change, you would have to sort them out. And if you sort multiple columns / fields with many records / rows, the sort option might be cumbersome.

The MIN / MAX functions remain the same regardless of the changes in the data, even if you add more rows (as long as you add the rows using the Insert> Row command within the existing range – that is, above the cell containing the formula

Enter some numbers in column A4: A11, and then enter this formula A13: = MIN (A4: A11) and this formula in A14: = MAN ( A4: A11)

NOTE: The MIN / MAX functions ignore blank cells, TRUE / FALSE responses, text, text representing numbers, symbols, and punctuation.

  07 The Min Max Function JD Sartain / IDG Worldwide

7. CONCAT / CONCATENATE

The functions CONCAT and CONCATENATE do the same thing: they combine several cells, ranges or data strings into one cell. The most common use of this feature is to combine first and last names in a cell, or to connect the city, state, and postal code in a cell.


Source link