Part 5: Creating Formulas and Performing Data Analysis in C#

Create QR Code ISO/IEC18004 in C# Part 5: Creating Formulas and Performing Data Analysis

Because SUM is such a commonly used function, Excel provides the AutoSum button on the Standard toolbar. If you select a cell and click the AutoSum button, Excel creates a SUM forAutoSum mula and guesses which cells you want to total. To enter SUM formulas into a range of cells, select the cells before clicking AutoSum.

14

Ever since the first spreadsheet program was created, one of the most common problems has been inserting cells at the bottom or to the right of a range that is already referenced in a formula. For example, suppose you type the formula =SUM(A1:A4) in cell A5, and then select row 5 and insert a new row. The new row is inserted above the selected row, thus pushing the SUM formula down to cell A6. Any numbers in the new inserted cell A5 are not included in the SUM formula. A few versions ago, Excel changed all that. Now you can insert cells at the bottom or to the right of a range referenced by a formula, and Excel adjusts the formulas for you and this is key as soon as you type values in the new inserted cells. This works only when you insert cells immediately to the right or below a referenced range. Inserting cells at the top or to the left of a referenced range still involves editing the referencing formulas manually.

Excel has 58 built-in math and trig functions, all of which are listed in Appendix D, Function Reference. This section only brushes the surface, covering a few of the more useful or misunderstood functions.

The PRODUCT function multiplies all its arguments and can take as many as 30 arguments that are text or logical values; blank cells are ignored. You can use the SUMPRODUCT function to multiply the value in each cell in one range by the corresponding cell in another range of equal size and then add the results. You can include up to 30 arrays as arguments but each array must have the same dimensions. (Nonnumeric entries are treated as zero.) For example, the following formulas are essentially the same:

=SUMPRODUCT(A1:A4, B1:B4) {=SUM(A1:A4*B1:B4)}

Everyday Functions The only difference between them is that the SUM formula must be entered as an array by pressing Ctrl+Shift+Enter.

The MOD function returns the remainder of a division operation (modulus). It takes the arguments (number, divisor). The result of the MOD function is the remainder produced when number is divided by divisor. For example, the function =MOD(9, 4) returns 1, the remainder that results from dividing 9 by 4.

Here s a practical use of the MOD function that you can ponder:

1 Select a cell and choose Format, Conditional Formatting. 2 Select the Formula Is option from the first condition list, and then type the formula

that applies the selected color to every other row. Note that if you select cells in an odd-numbered row, nothing seems to happen, but if you copy or apply the format to other rows, you ll see the result. This formula checks the current row number using the ROW function, divides it by two, and if there is a remainder (indicating an odd-numbered row), returns FALSE because the formula also contains the conditional test =0. If MOD returns anything but 0 (a remainder), the condition tests FALSE. Therefore, formatting is applied only when the formula returns TRUE (in even-numbered rows). For more information about conditional formatting, see Applying Conditional Formats on page 241.

The COMBIN function determines the number of possible combinations, or groups, that can be taken from a pool of items. It takes the arguments (number, number_chosen), where number is the total number of items in the pool and number_chosen is the number of items you want to group in each combination. For example, to determine how many different 12-player football teams you can create from a pool of 17 players, type the formula =COMBIN(17, 12). The result indicates that 6,188 teams could be created.