I find it annoying sometimes when I enter a correct formula in a cell and Excel gives me an error like “Inconsistent Formula” or “Formula Omits Adjacent Cells”.
Fortunately you can hide these error indicators through Excel Options.
Click the Microsoft Office button.
Click Excel Options.
In the left pane of Excel Options click Formulas.
In the Error Checking [...]
To calculate a running average, or a moving average, for a range of cells (a column), you use the AVERAGE function. The only trick you need to apply is to make your range changing continuously.
In the example below we want to calculate the running average of the sales from January to June.
Our ranges for the [...]
To average a range of values that meet a given criteria or condition, you use the AVERAGEIF function.
Syntax
AVERAGEIF(range,criteria,average_range)
Range : The group of cells to be evaluated for criteria.
Criteria: is the criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged.
Average_range: is the actual set of [...]
The INDIRECT function returns a reference specified by a text string, evaluates that reference and displays its contents. The function is useful when you want a cell reference in a formula to be variable, without the need to change the formula every time.
Syntax
INDIRECT(ref_text,a1)
ref_text: a text that contains a cell reference.
a1: a logical value that [...]
The Conditional sum wizard helps you create complex formulas to sum values in a column based on certain criteria. The wizard is part of Excel Add-ins. You must load it first before it can show up in the Tools menu. See then bottom of this post if you want to know how to load the [...]
The RANK function returns the rank of a number, i.e. its position within a list of numbers. It is based on the value of a number, relative to the other numbers in the list.
The list evaluated by the rank doesn’t have to be sorted, but to get the idea of how items are ranked imagine [...]
The IF function is used to conduct conditional tests on values and formulas, and it is syntax is;
IF(logical_test,value_if_true,value_if_false)
As you can see it has only one logical test, and then the cell which has the formula will have a certain value if the test is “TRUE” and another value if the test is “FALSE”.
If you have [...]
If you want to determine whether a specific cell has a formula or a value then you need to check a range property called “HasFormula”. Here is an example where the border color of a cell is changed to red if it has a formula, otherwise it is set to black.
Dim myRange As String
myRange = [...]
The SUMIF function is a direct way to sum values based on a single criteria. If we want to sum or add values based on multiple criteria however, then we need to take extra steps. One alternative for this is to use the SUM function and the IF function.
In the example below we want to [...]
In a previous post I explained How to use Autofill with formulas.Today I will cover the same subject but from a different perspective.
Suppose you have a list of values in a certain currency and you want to convert those values to another currency based on a given currency rate. If we follow the same [...]
