Excel ranges and formulas (2023)

• on
• the statistics
• number theory
• Java
• Data structures
• piedra angular
• calculation

Although Excel is not as powerful a statistical computing environment as R, it has the advantage of being found in almost every work environment. Therefore, knowing how to perform statistical calculations and simulations in Excel can be very useful.

Instead of using named variables to store values ​​to be used later, as you might see in R or other programming languages, Excel stores the values ​​it needs for later calculations.cells. Each cell is part of a specific one.spreadsheetand you have a unique row/column combination on that sheet. The row is specified with a positive integer and the column with a letter (or a string of letters if more than 26 letters must be used).

Below is a sample worksheet.

Notice that on the sheet above, cells B3, B4, and B5 contain numeric values, while cells A3 and A7 contain text.

There are other things that cells can contain, too. B7 appears to contain a value, but clicking on it reveals that the cell content is actually aFormula, and the value returned is simply the result of that formula.

In this case, the formula finds the range sum of all cells between B3 and B5 inclusive and is given by:"=SUMA(B3:B5)". This formula can be seen by selecting cell B7 and then looking at the text edit box with $fx$ to the left of the toolbar at the top. It is important to know that all formulas with "=' followed by an expression.

The expressionB3:B5specified in the formula aboveReachof cells Using ranges allows calculations to be performed efficiently, even when involving large amounts of data.

In general, a range takes the form of two cell addresses separated by colons (":"). These two cells are usually the top left and bottom right cells of a rectangular collection of cells, but they can also be the bottom left and top right cells of a rectangular collection of cells.

the use of"TOTAL()"in the above formula indicates whatfunctionto apply it to cell ranges, it is supplied as an argument inside the parentheses to the right of the function name. Excel has a large number of built-in functions that can be used for a variety of purposes. To see what it has to offer, click the $fx$ button in the toolbar at the top to bring upformula creatordialog box. Clicking on the name of a specific function displays more information about what that function calculates, the correct syntax for using it (ie how to write it correctly), and a link to more information about the function.

The left side of the worksheet shows an example usingAVERAGE()Function on the area of ​​the cells given byB3:F5

Formulas can also reference individual cell values, as shown on the right side of the worksheet above. When we clicked on the cellK5, we would see "=2/(1/K3+1/K4)", the formula for the harmonic mean of the values ​​seen in the cellsK3yK4. When mathematical expressions are used in a formula in Excel, the default order of operations is applied. So, since we need to write these expressions on a single line of text, care must be taken. A very common mistake is to forget to group numerators, denominators or even some exponents with parentheses when their inclusion is required.

Autocomplete and anchor

There are some "tricks" in Excel that can be used to quickly fill in a series of cells...

The first is that Excel supports relatively smart "autofill". Suppose you want to quickly create a column with cells 1 through 10. Simply enter the values ​​for the first two cells, and then select those cells, noting the little square "handle" in the lower right corner of that selection.

Would you like the sequence $3,5,7,9,\ldots$ instead? just start with3y5as your first two cells and drag down as before - Excel will figure out what you want and fill the cells accordingly. This process works for any linear sequence.

Another trick that can really save you time when filling a series of cells is to use an "anchor". To set the stage for understanding what an anchor is, consider the following...

DieRAND AMONG()The function generates a random integer between the two supplied arguments. Suppose we give the formula "=RAND BETWEEN(1,10)"in cellC2to generate a random integer between $1$ and $10$ in that cell and then copy the contents of that cell to all cells in the rangeC3:H5to create six columns of random numbers similar to the one shown below.

Now suppose we want to sum the different columns. If we "=SUMA(C2:C5)"in cellC7to add the numbers in the columnCand copy this formula to the cells of the rangeD7:H7we get the correct column totals for each column.

This works because when you copy a formula to another cell, the cell references are relatively updated. As a simple example of thisrelative addressing-- if a formula in the cellA1referencesB1, the cell immediately to the right and is copied to the cellJ5for example, then the above reference toB1is modified by Excel to refer to the cellK5, the cell tooJ5you are immediately right.

AnkerLet us change this default behavior when copying formulas to other cells so that anything "pinned" doesn't update due to relative position change.

Suppose you want to create 10 simulations of a game in which you have a 30% chance of winning. They also wanted their work to be flexible enough to easily adapt to another winning opportunity.

We begin by reading the text of "p ="in cellA1, followed by the value0,30inB1to indicate the probability of winning the game. We also have the text "X" y "x<p?"in the cellsD1yD2, or as column headers for the next part...

Then noticing that theRANDOM()generates a random value between $0$ and $1$, we enter "=RAND()"in cellD2, select it, and use the controller to automatically paste this formula into the 9 cells below.

To use these numbers to simulate a profit or loss, in the cellE2, we entered "=(D2<$B$1)". This returns a "value" ofRIGHToINCORRECTdepending on whether the cell value or notD2is smaller thanB1, as appropriate. in this way allRIGHTproduces is a triumph and everyoneINCORRECTrepresents a loss.

You may be wondering about the presence of the two dollar signs in the$B$1just used This is the "anchor" mentioned above. If we copy the formula into the cellE2To move cells down, we want to do a comparison between the value of the cell directly to the left of the cell in the columnmiand in the same row (relative addressing) and the cell valueB1no matter which linemiwe're at The presence of dollar signs essentially says, "If this formula is copied to another cell, the row or column references won't be changed after each dollar sign." Using them in this way is known asabsolute addressing.

With all cells making comparisons to the individual cellB1, if you want to change the probability of winning the game to 50%, just change the value of this cell to0,5.

The reason we need two dollar signs instead of just one in our formula is because Excel allows rows and columns to be anchored independently. The presence of the dollar sign before the row value anchors only the row, while the presence of the dollar sign before the column letters anchors only the column.

This independence between row and column anchors can be particularly useful when creating a table of values ​​that depends on two inputs. For example, consider creating a simple multiplication table. we fill the cellC2yD2with values1y2and autocomplete on the right to create the column headers for our table. Similarly, let's create row headers in the rangeB3:B12. Then, to create the body of the table, we simply write "=$B3*C$2"in cellC3and copy it to the rest of the cells in the rangeC3:L12. (Think carefully why this works!)

Another trick: suppose you want to copy a cell in a large range. You can of course copy the cell and then manually select the large range, but this can require a lot of scrolling if the range spans thousands of rows or columns. Instead, a large group of cells can be selected for copy, paste, or other purposes by going to the Edit menu and selecting Find: Go To. At this point, enter the range you want to select in the "Reference" box and click "OK". The great variety is already selected! Note:G ForceoF5can be used is a keyboard shortcut to invoke the Go To dialog to further speed up the selection process.

Recursively defined sequences

It is useful to use formulas to fill a range with a recursively defined sequence.

For example, the Fibonacci sequence $1, 2, 3, 5, 8, 13, \ldots$ is the sequence starting with $1, 1$, with the subsequent terms being the sum of the two previous terms.

Notice how easy it is to create this sequence in Excel as shown below.

This is where we come in first1in cellsB2yB3, and then enter the formula "=B2+B3"in cellB4. Then let the relative addressing do its thing, copy this formula or auto-complete it in the cells below to get the sequence shown.