Excel is showing formula not result plus#

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. Min and Max represent the minimum or maximum value in the cells. For example, four is the median for 1, 2, 3, 4, 5, 6, 7. MEDIAN =MEDIAN(A1:A7)įind the median of the values of cells A1 through A7. Returns the value of a random cell between A1 and A10 using the indirect and randbetween (explained below) functions. In the example above, the formula would return the value contained in cell A2. Returns a reference specified by a text string. Getting #DIV/0! in Microsoft Excel spreadsheet.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. Using the IF statement, you can only calculate a cell if it contains a value. However, if there is nothing in the cells, you would get the #DIV/0! error. For example, you may be dividing the values between two cells. Using IF can also be useful when you may want to calculate values in a cell, but only if those cells contain values. The IF statement has more complex uses, but can generally be reduced to the structure above. If A1 is not empty, the cells display the text 'NOT BLANK'. For example, the formula =IF(A1='','BLANK','NOT BLANK') makes any cell besides A1 display the text 'BLANK' if A1 has nothing in it. The syntax of the IF statement is =IF(CELL='VALUE','PRINT OR DO THIS','ELSE PRINT OR DO THIS'). So, if you have five cells in that range containing the word 'test,' the value '5' is shown in cell A11 (10-5=5). 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. COUNTIF =COUNTIF(X:X,'*')Ĭount the cells with a certain value. If seven cells were empty, the formula would return the number '13' (20-7=13). For example, you could count the number of cells containing text in cells A1 through A20 using =COUNTA(A1:A20). COUNTA =COUNTA(X:X)Ĭount the number of cells in a range containing any text (text and numbers, not only numbers) and are not empty. If only cell A1 and A5 contained numbers, the cell containing this function would display '2' as its value.

For example, you could find how many cells between A1 and A15 contain a numeric value using the =COUNT(A1:A15). COUNT =COUNT(X:X)Ĭount the number of cells in a range containing only numbers. For example, if you wanted to get the average for cells A1 to A30, you would type =AVERAGE(A1:A30). AVERAGE =AVERAGE(X:X)ĭisplay the average amount between cells. You can also use a concatenate formula to combine cell values. 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 could also create a formula that would make one cell equal to more than one value. For example, if you were to enter =A1 in B1, whatever value was in A1 would automatically be placed in B1. Finally, we manually enter a times ( * ) formula using the sum function to find the value of 5 * 100.Īn = ( equals) creates a cell equal to another. 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). 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. 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.īelow is an animated visual example of how an excel formula can be inserted into a spreadsheet.For example, A1:A10 is cells A1 through A10. Using the colon (:) lets you get a range of cells for a formula.

Excel is showing formula not result plus#

Math functions or calculations can use an operator, including plus (+), minus (-), multiply (*), divide (/), greater than (>), and less than (<).

If a math function is being performed, the math formula is surrounded in parentheses.

The function tells the spreadsheet the type of formula.

After the equal symbol, either a cell or formula function is entered.

All spreadsheet formulas begin with an equal sign (=) symbol.

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