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 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 [...]
To generate a random number within a given range use the RANDBETWEEN function.
Syntax
RANDBETWEEN(bottom,top)
Bottom: is the smallest integer RANDBETWEEN will return.
Top: is the largest integer RANDBETWEEN will return.
Examples:
RANDBETWEEN (1,100) will generate numbers from 1 to 100
RANDBETWEEN (-15,2000) will generate numbers from -15 1 to 2000
Notes:
1. A new random integer number is returned every time [...]
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 [...]
The MATCH function searches for an item (lookup value) in a given array or a data range (lookup array), and if I understand it right the array can only be within one column or within one row but I could not find that mentioned explicitly in any reference. When the item is found, MATCH returns [...]
The HLOOKUP function (short for Horizontal Lookup), searches for a value in the first row of a table array and returns the corresponding value in the same column from another row in the table array.
Syntax
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
Lookup_value:The value to search for in the first row of the table array.
Table_array:Two [...]
In a previous post I explained how to perform random lookup based on the VLOOKUP function. The formula was:
=VLOOKUP(INT(RAND()*10+3),P12:Q23,2,FALSE)
I explained how I used the RAND() function to generate values between 1 and 12 for the first parameter of the function (red color). However there is another direct function which generates random numbers within a given [...]
