ເນື້ອໃນ
One of the most powerful tools in Excel is the ability to create formulas. You can use formulas to calculate new values, analyze data, and more. But working with formulas has its downside – the slightest mistake is enough for the formula to give the wrong result.
Worst of all, Excel doesn’t always report an error in a formula. As a rule, such a formula continues to work and perform calculations, giving an erroneous result. The responsibility for the fact that you are once again too lazy to check the formula lies entirely with you.
We have compiled a list of guidelines that you can use to check the correctness of the created formulas. These hints won’t solve every problem you’ll encounter, but will provide a tool for identifying many common errors.
Check links
Most formulas use at least one cell reference. If you double-click on a formula, then the borders of all cells that are referenced will be highlighted. You can recheck each link to make sure they are correct.
Look for permutations
A common mistake is to use the correct cell references but in the wrong order. For example, if you want to subtract C2 of C3, the formula should be: =C3-C2, not like this: =C2-C3.
ເອົາມັນອອກນອກ
If the formula is too complex to test, try splitting it into several simpler formulas. Thus, you can check the accuracy of each formula, and if problems arise, you will know exactly where.
Think about what the result should be
You can use your own experience, critical thinking and intuition to determine what the result should be. If the result in Excel is much larger or smaller than expected, there may be errors in the formula (or wrong data in the cells).
For example, if you calculate the total cost 8 units of goods 98 cents for each, the result should be slightly less $8. In the example below, the formula gives an erroneous result. $ 784,00. The reason is that in cell A2 the price is entered as 98, and should be 0,98. As you can see, small details can make a big difference.
Keep in mind that this trick doesn’t always work. In some cases, the wrong answer can be remarkably close to the right one. However, in many situations, such a quick evaluation reveals an error in the formula.
Check Arguments
If you are working with a function, make sure that all required arguments are provided. When entering a function, a small tooltip with the required arguments should be displayed.
The tooltip is especially useful when you are trying to fix a feature that is not working properly. For example, look at the function below:
In the example in the figure above, the function ມື້ເຄືອຂ່າຍ (NETWORKDAYS) returns an error. If we introduce a function ມື້ເຄືອຂ່າຍ (NETWORKDAYS) to another cell, the reason becomes obvious:
ຫນ້າທີ່ ມື້ເຄືອຂ່າຍ (NETWORKDAYS) requires a minimum of two arguments − ວັນທີ່ເລີ່ມ (start_date) and ວັນສິ້ນສຸດ (end_date). In the previous example, only one argument was given, so let’s fix the function by adding the missing argument:
Now our formula works correctly!
Check the entire chain of operations (sequence)
Remember from school mathematics what is the order of mathematical operations? If not (or if you want to refresh your memory), you can study the lesson on building complex formulas. Excel always uses this order, that is, the operations are not just performed in turn from left to right. In the following example, the first step is multiplication, which is not exactly what we wanted. Let us correct this formula by concluding D2+D3 in brackets:
Turn on formula display
If there are a lot of formulas and functions on an Excel sheet, then it may be more convenient for you to switch to the formula display mode in order to see all the formulas at the same time. To do this, you need to use the command Formula view (Show formulas), which is on the tab Formulas (ສູດ) ພາກ Formula auditing (Formula dependencies).
To switch back to the familiar view, click on this command again.
Remember, mastery of formulas is achieved through constant practice. Even the most experienced Excel users make mistakes in formulas. If your formula doesn’t work or gives you the wrong value, don’t panic! In most cases, there is a simple explanation why the formula fails. Once you find this error, you can make the formula work correctly.