Troubleshoot totals and calculated values in Datasheet view
I get a message saying the results of the formula will be sent to the server.
You edited the formula, and the list is attempting to submit the new results to the server. Do one of the following:
- Click Continue to complete the edit operation. If there are pending changes, conflicts, or errors, the list will prompt you to take appropriate actions.
- Click Cancel to return to the cell containing the formula.
This could be because of one or more of the following reasons:
- The parentheses used in the formula do not match. Make sure each opening parenthesis has a matching closing parenthesis.
- The formula includes a special character other than the equal sign (=) and calculation operators.
- The formula calls one or more functions, but you have specified an incorrect number of arguments for one or more of these functions.
- The formula uses a function that is not supported by a list. Specifically, lists do not support the Rand and Now functions. You cannot use the Today and Me functions in a calculated column, but you can use them to specify the default value for a column.
The cell displays the formula instead of the result.
Make sure the cell that contains the formula is part of a calculated column. If you enter a formula in a column other than a calculated column, such as a column of type single line of text, the list will treat the formula as a text string and display it as it is.
I get a message about pending changes when I edit a calculated column.
Any change to a calculated column results in a change to the list structure. You can make a structural change to a list only if there are no pending changes, conflicts, and errors. If the list encounters one of these in the list, you will prompted to wait until all changes are submitted to the server, and resolve any conflicts and errors, before you can add or edit a column.
I get a timeout or unanticipated error.
Refresh the list, and repeat the change you made to the calculated column.
I get an error indicating that the list structure has changed.
Editing a calculated column results in a change to the list structure. If the list encounters a conflict between the change you made, and a structural change made by another user, you will prompted to refresh the list. Refreshing the list updates your view, but discards the changes you made to the calculated column. Try repeating your change.
I get an error indicating that I do not have permission to change calculated columns in the list.
Editing a calculated column results in a change to the list structure. If you do not have permissions to make changes to a list structure, you will not be able to edit calculated columns in the list.
I am having problems with columns or column references.
I cannot edit a formula because one or more other columns require values.
The list must commit all of your changes before you can edit a formula. A row cannot be committed if one of its columns is set as a required column, but is left blank. Enter valid values in all the required columns and then trying editing a formula.
I get an error indicating that one or more column references are not allowed.
The formula includes a reference to a column whose data type is Multiple lines of text, Lookup, or Hyperlink. These data types do not support calculations. Edit the formula to remove references to unsupported columns.
I get an error indicating that a column does not exist.
You have specified a string as an operand, but the list could not find a column with a name that matches the string. Do one or more of the following:
- Make sure the columns referenced in the formula exist.
- Make sure the column names are enclosed in square brackets ([]) if they include spaces or special characters.
- Verify the spelling of the column references.
When editing a calculated column, I cannot select the column I want.
After you type the equal sign (=) in a calculated column, you can paste a column reference simply by clicking the cell in that column that belongs to the current row. However, if the column is of a data type that does not support calculations, or you are attempting to select a cell in a different row or select more than one cell, the list will not paste the column reference in the formula.
The calculated column displays a string that I do not recognize.
Cells in a calculated column display #NAME?.
A column referenced in a formula does not exist anymore. Either add the column to the list, or edit the formula to remove the reference.
Cells in a calculated column display #CIRC!.
The error is caused by one or more circular references. A circular reference occurs in the following situations:
- The formula used in the column includes a reference to itself. For example, the formula used to calculate values for the Taxable Income column references the Taxable Income column.
- Two or more calculated columns reference each other. For example, the Calc1 column uses the formula =[Calc2]+2 and the Calc2 column uses the formula =[Calc1]-1.
- Make sure the formula does not reference the column that contains the formula.
- If the formula references another column, make sure the referenced column's formula does not reference the first column.
- Verify the spelling of the column references.
Cells in a calculated column display #VALUE!.
This error can occur in one of the following two situations:
- A wrong type of operand is used in the formula.
- A wrong type of argument is specified for a function that is used in the formula.
Cells in a calculated column display #NUM!.
This error occurs when the formula includes invalid numeric values, such as:
- The value returned by the formula is too large to be represented in a list.
- The formula encountered a date value that is earlier than 1/1/1900.
- A function used in the formula expects a numeric argument, but you have specified a non-numeric argument. A wrong type of argument is specified for a function that is used in the formula.
Cells in a calculated column display #DIV/0!.
This error occurs when a number is divided by zero (0), such as:
- Explicitly specifying a division by zero (0), such as =133/0.
- The formula includes column references, and at the time of evaluation, the values in the referenced columns result in a division by zero (0). For example, the [ECount1] and [ECount2] columns have the same values in some rows, so the formula =[Qty]/[ECount1]-[ECount2] results in an error for those rows.
Cells in a calculated column display #NULL!.
This error occurs when an argument or operand specified in a formula has a null value. If one element of a formula has a null value, the entire formula results in a null value. To avoid this error, make sure null values are replaced with zero (0). You might also want to change the setting of the referenced columns to Required, so that all rows have a non-null value.
Cells in a calculated column display #N/A .
This error occurs when a function is missing a value. Make sure you have specified all the required arguments to a function.