Oct 03 2021 11:50 AM
Oct 03 2021 11:50 AM
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.
Oct 03 2021 12:15 PM
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.
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.
Oct 03 2021 12:43 PM - edited Oct 03 2021 12:44 PM
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?
Oct 03 2021 12:57 PM
No, proper formulas refer to other cells.
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)
Oct 03 2021 09:33 PM
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?
To find the "Variance," I'm okay to use the standard, "actual usage-theoretical usage?"
Oct 04 2021 01:06 AM
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]]