Forum Discussion
Excel Formula Issue
Hi there!
I am completing a worksheet and m formula does not seem to be correct, although it does compute it.
I am getting an "inconsistent calculated column formula," and I am not too sure what this may mean.
I know what numbers I must take to compute to solve for "X," however I think I may be inputting the formula incorrectly to Excels standards.
This is for an Income Statement and Balance Sheet.
Please advise!
The error occurs because you have a formula in only 4 of the cells of the Actual Usage column, and it doesn't refer to cells in the same row.
Select I3.
Enter the formula =[@[Beginning Value]]+[@Purchases]-[@[Ending Value]]
Click the error triangle and select 'Restore to calculated column formula' from the popup menu.
Fill down from I3 to I46 (the last row of the table).
Also, you should not use SUM formulas in row 41. Delete the word Totals and the SUM formulas in that row.
Instead, activate the Table Design tab of the ribbon and tick the check box Total Row in the Table Style Options group.
This will add a new row to the table in row 47.
Click in the cell in row 47 of each of the columns where you want a total, click the drop-down arrow in that cell, and select Sum from the drop-down menu.
5 Replies
The error occurs because you have a formula in only 4 of the cells of the Actual Usage column, and it doesn't refer to cells in the same row.
Select I3.
Enter the formula =[@[Beginning Value]]+[@Purchases]-[@[Ending Value]]
Click the error triangle and select 'Restore to calculated column formula' from the popup menu.
Fill down from I3 to I46 (the last row of the table).
Also, you should not use SUM formulas in row 41. Delete the word Totals and the SUM formulas in that row.
Instead, activate the Table Design tab of the ribbon and tick the check box Total Row in the Table Style Options group.
This will add a new row to the table in row 47.
Click in the cell in row 47 of each of the columns where you want a total, click the drop-down arrow in that cell, and select Sum from the drop-down menu.
HansVogelaar
Thank you for your response! I appreciate it
I have given your suggestion a try and the 'error,' no longer shows up.
In relation to the 'Ending Value,' column, I simply too the unit price and the quantity in stock and multiplied them together, like so (10*10). It has given me 100, however would this be considered a proper formula?No, proper formulas refer to other cells.
Select F3:F46.
Enter the formula
=MID([@[Unit Price]],2,FIND("/",[@[Unit Price]])-2)*[@[Quantity in Stock]]
and confirm it with Ctrl+Enter.
(It's complicated because the Unit Price column contains text instead of a number)
