Excel help for the rest of us
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.
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 months will be as follows:
January – one cell : B2:B2
February- 2 cells B2:B3
March – 3 cell2: B2:B4 … and so on.
The first cell of the range is always the same for all months : B2, so we will make it absolute reference like this: $B$2. Therefore our formulas , in C column will be like this:
January: =AVERAGE($B$2:B2)
February: =AVERAGE($B$2:B3)
March: =AVERAGE($B$2:B4) …. Ans so on.
Enter the first formula in cell C2 and autofill down the rest of the range.
Data
Formulas
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 cells to be averaged. If omitted, range is used.
Example:
In the example below we want to average the total sales for a particular salesman (Jim). So our range is B2:B13 and our criteria is “Jim”. The range to be averaged is C2:C13. The formula will be:
=AVERAGEIF(B2:B13;”Jim”;C2:C13)
The result is 1005, i.e. (2300 + 1100 + 200 + 420) / 4
Notes:
1. Cells in range that contain TRUE or FALSE are ignored.
2. If a cell in average_range is an empty cell, AVERAGEIF ignores it.
3. If range is a blank or text value, AVERAGEIF returns the #DIV0! error value.
4. If a cell in criteria is empty, AVERAGEIF treats it as a 0 value.
5. If no cells in the range meet the criteria, AVERAGEIF returns the #DIV/0! error value.
6. You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. In the previous example if you use “J*” in criteria then both “Jim” and “John” will be included in the average.
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 specifies what type of reference is contained in the cell ref_text.
• If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference. E.g. C12
• If a1 is FALSE, ref_text is interpreted as an R1C1-style reference. E.g R12C3
Example 1:
Cell B1 contains the formula: =INDIRECT(A1)
Cell A1 contains the text: “C6″
Cell C6 contains the value: 135
The INDIRECT function will evaluate the reference as: = C6, which is equal to 135.
If you change the text in cell A1 to “C3″ then you will get 198 in B1.
Example 2:
The data shown above is a snapshot from a sheet named “Sheet2″ in a workbook.
Cell B1 contains the formula: =INDIRECT(A1&”!”&”C8″)
Cell A1 contains the text: “Sheet2″
Cell C8 contains the value: 1922
The INDIRECT function will evaluate the reference as: = Sheet2!C8, which is equal to 1922.
If you change the text in cell A1 to “Sheet1″ then the reference will be evaluated as: = Sheet1!C8 and you will get whatever value in Cell C8 of sheet1.
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 wizard.
The following example will demonstrate how to use the conditional sum wizard:
Suppose we have data of total sales by month and product as shown below, and we want to sum the total sales for Product3 in the first quarter (months 1 to 3).
1. On the Tools menu click Conditional Sum. The dialogue for step1 will open.
2. Click on the text box then select your data range, including column headings.
3. Click next. The dialogue for step2 will open.
4. Specify your conditions by manipulating the three combo boxes for Column, Is and This value. Click Add condition every time.
5. When finished with your conditions click next. The dialogue for step 3 will open.
6. Specify whether you want to copy your generated formula only, or both formula and the values of your conditions.
7. Click next. The dialogue for step 4 will open.
8. Specify the cell(s) where you want your generated formula (and values of conditions if applicable) to be copied.
9. Click finish. The formula will be copied to the specified cell, and the result of the formula will be shown. Here is the formula in our example:
{=SUM(IF($A$2:$A$19<4,IF($B$2:$B$19=”Product3″,$C$2:$C$19,0),0))}
How to Load the Conditional Sum Wizard
The Conditional Sum Wizard is not loaded by default. To determine whether it has been loaded, on the Tools menu, look for the Wizard menu item. If it is not present, or if when you point to it, the item Conditional Sum is not present, you need to load the add-in.
To load the Conditional Sum Wizard, follow these steps:
1. On the Tools menu, click Add-ins.
2. In the Add-Ins available list, select Conditional Sum Wizard, and then click OK.
Applies to Excel 2003
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 that the list is sorted, and then each item is given a rank depending on its position in the list.
Syntax
RANK(number,ref,order)
Number: is the number whose rank you want to find.
Ref: is an array of or a reference to a list of numbers. Non-numeric values in the array or the reference are ignored.
Order: is a number specifying how to rank number.
• If order is 0 (zero) or omitted, Excel ranks number as if the list is sorted in descending order.
• If order is any nonzero value, Excel ranks number as if the list is sorted in ascending order.
Note:
The RANK function gives duplicate numbers the same rank. This however will affect the ranks of subsequent numbers. For example, if we have two numbers both have the rank of 5, then no number will have a rank of 6. The next number in the list will have a rank of 7.
Examples:
1. Rank with order 0 (descending).
This example shows ranks in descending order (Order = zero). Here are the applicable formulas in each cell:
C2: =RANK(B2,$B$2:$B$6,0)
C3: =RANK(B3,$B$2:$B$6,0)
C4: =RANK(B4,$B$2:$B$6,0)
C5: =RANK(B5,$B$2:$B$6,0)
C6: =RANK(B6,$B$2:$B$6,0)
2. Duplicate ranks.
The second example shows duplicate ranks, also in descending order (Order = zero). Notice that students 2 and 3 both have a rank of 2, and therefore rank 3 is skipped. Here are the applicable formulas in each cell:
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 more than one logical test, then you can nest more than one IF function within each other.
In the example below we have a list of hotel ratings and room prices. We want to check:
1. The rating of the hotel. If it is less than 4 stars then the cell value will be “NOT OK” and the test will end there.
2. If the rating is 4 stars or more then we will conduct another test on the room price.
A generic formula for cell “C2″ for our case will be something like this:
=IF(A2<4;”NOT OK”;(Another test))
In place of “Another test” we will have the formula:
IF(B2>80;”NOT OK”;”OK”)
So joining these two formulas together we will have the final formula (in C2):
=IF(A2<4;”NOT OK”;(IF(B2>80;”NOT OK”;”OK”)))
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 = “B5″
If ActiveSheet.Range(myRange).HasFormula Then
ActiveSheet.Range(myRange).Borders.Color = RGB(255, 0, 0)
Else
ActiveSheet.Range(myRange).Borders.Color = 0
End If
Suggested Post:
How to write a VBA macro
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 sum up the total sales for “John” in quarter 2 (Q2). i.e. our criteria is “John” in column B and “Q2″ in column C. We will use the following formula for this purpose:
=SUM(IF($B$2:$B$17=”John”,IF($C$2:$C$17=”Q2″,$D$2:$D$17,0),0))
However this formula should be an “Array formula”. To make it an “Array formula” you should press CTRL+SHIFT+ENTER while you are in the edit mode of the formula. It should then look like this :
{=SUM(IF($B$2:$B$17=”John”,IF($C$2:$C$17=”Q2″,$D$2:$D$17,0),0))}
This formula will give us a result of 9547 (summing sales in the yellow rows).
Note:
The formula presented in this post is generated using the conditional sum wizard. You can try the wizard for yourself.
If you have the Analysis Toolpak installed, then the wizard should be available in the Tools menu. If not then read more on: How-to-install-and-load-the-analysis-toolpak.
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 scenario as in the previous post then it will go like this.
What is wrong?
The cell reference style we are using here e.g. A3, B1 is called Relative Cell Reference. When you perform autofill downwards with relative cell reference, Excel will increase the row number by 1 successively e.g. A1, A2, A3 or B1, B2, B3 … etc.
In our case we want cell B1, which contains the rate, to be fixed. In this case we use the Absolute Cell Reference which means whatever cell reference you specify will remain fixed when you use autofill. The B1 cell will be coded like this: $B$1.
Now back to our example. This is how the formulas look after modification. $B$1 is the same in all rows.
And this is how our values will look like.
Related Links: