Forum Discussion
Excel Formula Issue
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)
HansVogelaar
Thank you! I was able to get my same answers using the formula provided.
You're right, the formula is so complicated but I understand why.
If it did not have per, "kg, case, each," I could have used a more simplified formula, possibly even using for example, =D3*D6?
How do you generate these equations, especially for someone who is not used to the software?
Any tips?
To find the "Variance," I'm okay to use the standard, "actual usage-theoretical usage?"
e.g I3-J3?If you didn't have kg, case etc., the formula could be
=[@[Unit Price]]*[@[Quantity in Stock]]
This way of referring to the columns of a table is called a structured reference. See Introduction to structured references.
You'll find lots of useful information about Excel formulas on that website.
The formula for variance could be
=[@[Actual Usage]]-[@[Theoretical Usage]]