What do you call a statement entered into the cell of an Excel sheet that calculates a value select an answer?

Updated: 04/12/2021 by Computer Hope

Table of Contents Show

Entering a spreadsheet formula
Formula examples
MIN AND MAX
RANDBETWEEN

Formulas are what helped make spreadsheets so popular. By creating formulas, you can perform quick calculations even if the information changes in the cells relating to the formula. For example, you could have a total cell that adds all values in a column.

The basics

All spreadsheet formulas begin with an equal sign (=) symbol.
After the equal symbol, either a cell or formula function is entered. The function tells the spreadsheet the type of formula.
If a math function is being performed, the math formula is surrounded in parentheses. Math functions or calculations can use an operator, including plus (+), minus (-), multiply (*), divide (/), greater than (>), and less than (<).
Using the colon (:) lets you get a range of cells for a formula. For example, A1:A10 is cells A1 through A10.
Formulas are created using relative cell reference by default, and if you add a dollar sign ($) in front of the column or row, it becomes an absolute cell reference.

Entering a spreadsheet formula

Below is an animated visual example of how an excel formula can be inserted into a spreadsheet. In our first formula entered into the cell "D1," we manually enter a =sum formula to add 1+2 (in cells A1 and B2) to get the total of "3." With the next example, we use the mouse to highlight cells A2 to D2 and then click the formula button in Excel to automatically create the formula. Next, we show how you can manually enter a formula, and then using a mouse, get the cell values (you can also highlight multiple cells to create a range). Finally, we manually enter a times ( * ) formula using the sum function to find the value of 5 * 100.

Formula examples

Note

The functions listed below may not be the same in all languages of Microsoft Excel. All these examples are done in the English version of Microsoft Excel.

Tip

The examples below are listed in alphabetical order. If you want to start with the most common formula, we suggest starting with the =SUM formula.

An = (equals) creates a cell equal to another. For example, if you were to enter =A1 in B1, whatever value was in A1 would automatically be placed in B1. You could also create a formula that would make one cell equal to more than one value. For example, if cell A1 had a first name and cell B1 had a last name, you could enter =A1&" "&B1, which combines A1 with B1, with a space between each value. You can also use a concatenate formula to combine cell values.

AVERAGE

=AVERAGE(X:X)

Display the average amount between cells. For example, if you wanted to get the average for cells A1 to A30, you would type =AVERAGE(A1:A30).

COUNT

=COUNT(X:X)

Count the number of cells in a range containing only numbers. For example, you could find how many cells between A1 and A15 contain a numeric value using the =COUNT(A1:A15). If only cell A1 and A5 contained numbers, the cell containing this function would display "2" as its value.

COUNTA

=COUNTA(X:X)

Count the number of cells in a range containing any text (text and numbers, not only numbers) and are not empty. For example, you could count the number of cells containing text in cells A1 through A20 using =COUNTA(A1:A20). If seven cells were empty, the formula would return the number "13" (20-7=13).

COUNTIF

=COUNTIF(X:X,"*")

Count the cells with a certain value. For example, if you have =COUNTIF(A1:A10,"TEST") in cell A11, then any cell between A1 through A10 with the word "test" is counted as one. So, if you have five cells in that range containing the word "test," the value "5" is shown in cell A11 (10-5=5).

IF

=IF(*)

The syntax of the IF statement is =IF(CELL="VALUE","PRINT OR DO THIS","ELSE PRINT OR DO THIS"). For example, the formula =IF(A1="","BLANK","NOT BLANK") makes any cell besides A1 display the text "BLANK" if A1 has nothing in it. If A1 is not empty, the cells display the text "NOT BLANK". The IF statement has more complex uses, but can generally be reduced to the structure above.

Using IF can also be useful when you may want to calculate values in a cell, but only if those cells contain values. For example, you may be dividing the values between two cells. However, if there is nothing in the cells, you would get the #DIV/0! error. Using the IF statement, you can only calculate a cell if it contains a value. For example, if you only wanted to perform a divide function if A1 contains a value, type =IF(A1="","",SUM(B1/A1)), which only divides cell B1 by A1 if A1 contains a value. Otherwise, the cell is left blank.

Getting #DIV/0! in Microsoft Excel spreadsheet.

INDIRECT

=INDIRECT("A"&"2")

Returns a reference specified by a text string. In the example above, the formula would return the value contained in cell A2.

=INDIRECT("A"&RANDBETWEEN(1,10))

Returns the value of a random cell between A1 and A10 using the indirect and randbetween (explained below) functions.

=MEDIAN(A1:A7)

Find the median of the values of cells A1 through A7. For example, four is the median for 1, 2, 3, 4, 5, 6, 7.

MIN AND MAX

=MIN/MAX(X:X)

Min and Max represent the minimum or maximum value in the cells. For example, if you want to get the minimum value between cells A1 and A30, type =MIN(A1:A30), or =MAX(A1:A30) to get the maximum.

PRODUCT

=PRODUCT(X:X)

Multiplies two or more cells together. For example, =PRODUCT(A1:A30) would multiple cells from A1 to A30 together (i.e., A1 * A2 * A3, etc).

RAND

=RAND()

Generates a random number greater than zero, but less than one. For example, "0.681359187" could be a randomly generated number placed in the formula's cell.

RANDBETWEEN

=RANDBETWEEN(1,100)

Generate a random number between two values. In the example above, the formula would create a random whole number between 1 and 100.

ROUND

=ROUND(X,Y)

Round a number to a specific number of decimal places. X is the cell containing the number to be rounded. Y is the number of decimal places to round. Below are examples.

=ROUND(A2,2)

Rounds the number in cell A2 to one decimal place. If the number is 4.7369, the example above rounds that number to 4.74. If the number is 4.7614, it rounds to 4.76.

=ROUND(A2,0)

Rounds the number in cell A2 to zero decimal places, or the nearest whole number. If the number is 4.736, the example above would round that number to 5. If the number is 4.367, it would round to 4.

SUM

=SUM(X:X)

The most commonly used function to add, subtract, multiple, or divide values in cells. Below are different examples of this function.

=SUM(A1+A2)

Add the cells A1 and A2.

=SUM(A1:A5)

Add cells A1 through A5.

=SUM(A1,A2,A5)

Adds cells A1, A2, and A5.

=SUM(A2-A1)

Subtracts cell A1 from A2.

=SUM(A1*A2)

Multiplies cells A1 and A2.

=SUM(A1/A2)

Divides cell A1 by A2.

SUMIF

=SUMIF(X:X,"*"X:X)

Perform the SUM function only if there is a specified value in the first selected cells. An example of this would be =SUMIF(A1:A6,"TEST",B1:B6), which only adds the values B1:B6 if the word "test" was entered somewhere between A1:A6. So if you entered "test" (not case sensitive) in A1, but had numbers in B1 through B6, it would only add the value in B1 because "test" is in A1.

See our SUMIF definition for additional information.

TODAY

=TODAY()

Would print out the current date in the cell containing the formula. The value changes each time you open your spreadsheet to reflect the current date and time. If you want to enter a date that doesn't change, press Ctrl and ; (semicolon) to enter the date.

TREND

=TREND(X:X)

Find the common value of cells. For example, if cells A1 through A6 had 2,4,6,8,10,12 and you entered formula =TREND(A1:A6) in a different cell, you would get the value of 2 because each number is going up by 2.

TRIM

=TRIM( )

The trim formula lets you remove any unnecessary spaces at the beginning or end of a value in a cell.

How to remove extra spaces in a cell in Microsoft Excel.

VLOOKUP

=VLOOKUP(X,X:X,X,X)

The lookup, hlookup, or vlookup formula lets you search and find related values for returned results. See our lookup definition for more information about this formula.

Formulas in Excel are basically mathematical expressions that use cell references (e.g., “A5”,” D17”) as arguments. For example, a formula that adds the contents of cell E5 and E6 could be written as follows:

= E5+E6

(Note: all formulas in Excel need to be preceded by an “=” sign.) If the values contained in E5 and E6 are 6 and 11, respectively, the formula will produce 17 as the value it displays. If you change E5 to 7, the result will automatically change to 18.

Example

Let's say you were putting together an office supply order, and you wanted to keep track of much you were spending. You could put together a spreadsheet like the one below, with the list of items to be purchased, their unit prices, the number of each item ordered, and the total spent for each. It would make sense to enter the things you know in advance (like the price of individual items and the number ordered), but you could let Excel calculate the totals for you. For the first item listed below (pencils), this could be done by making the value of the total price (cell D2), the value of the unit price (held in cell C2) multiplied by the number of items ordered (held in D2). This formula would be written "=B2*C2".

After hitting "Enter", the cell will display the calculated value, while the formula bar will still display the formula. (Note: Always hit “Enter” when finished entering a formula, manually. If you click off the cell, the cell you click to will be added to your formula.)

Excel will generally be able to handle any properly-input mathematical formula, if valid operators are used. Commonly used operators include "+" (addition), "-" (subtraction), "*" (multiplication) and "/" (division). (Microsoft has a complete list of valid operators to be used in Excel formulas on the Office website). Here are some examples of formulas using common operators:

Formula Description

=C2-B2 Subtracts contents of B2 from contents of C2

=C2/B2 Divides contents of C2 by contents of B2

=(B2+C2+D2)/3 Adds contents of B2, C2, and D2 and divides result by 3

Excel also has built-in functions that can do a lot of useful calculations. These are most easily accessed by hitting the Insert Function button, which is represented by the “fx” symbol next to the formula bar. For example, instead of entering the formula shown above, the same result could have been achieved using the built-in "PRODUCT" function by clicking in cell D2 and hitting the Insert Formula button. This would give a dialog box like the one shown, below.

After selecting "PRODUCT" and clicking OK, you will get another dialog box, that allows you to select the cells to be multiplied. You can do this for individual cells, by selecting cells separately in the "Number1" and "Number2" boxes shown below, or by selecting an array of cells, by clicking and dragging on the range cells you want to use on the spreadsheet, itself. (Note: if you try to enter a formula in a cell using the Insert Formula button and there are adjacent cells with numbers, Excel will often select those cells automatically, so make sure the cells selected in the dialog box are the correct ones.)

Once you click "OK", your completed formula will be input into the cell.

Copying and pasting formulas

Often, you will need Excel to do a series of similar computations, where the only things that will change are the cells used as arguments. For instance, in the example above, you would probably like Excel to calculate the Total Price for each item in the order. You could re-input the same formula used to get the total price for pencils in each cell in that row, just changing the cells referenced (i.e. "=PRODUCT(B3:C3)", "=PRODUCT(B4:C4)", etc.), but Excel has simpler method for this. If you have multiple cells in the same row or column that need to do the same computation, you can simply copy the value in the cell you entered a formula, and then paste it into the subsequent cells. Excel will then automatically adjust which cells are included in the formula, based upon which cell the formula was pasted to. So, if the original formula entered in D2 was "=PRODUCT(B2:C2)", the formula pasted into D4 would be "=PRODUCT(B4:C4)"

More simply, if you have a formula you want repeated in a number of directly adjoining cells, you can just click and drag the bottom right corner of the cell with the original formula (see image below) onto the cells you want the same formula entered, and Excel will automatically copy and paste the formula for you, with appropriate adjustments made to the cell numbers in the formula.