Posted on 08 October 2009
A drop down list in Excel allows users to pick a value from a restricted set. By providing user a choice and yet preventing impermissible values, drop down lists help make your spreadsheets interactive and foolproof. While there are more than 1 way to make them, the most commonly used method is using data validation. You can create both static and dynamic drop down lists in Excel - the later re-sizing themselves as newer values are added or existing ones ...
Continue Reading
Posted on 08 July 2009
Array formulas help you turn normal Excel Formulas into super formulas. Here are a few examples of
Array formulas. These examples highlight the use of multiple conditions within a single array formula and how we can club multiple formulas in to a single array formula.
Array formulas are powerful - no doubt about that. However before we go any further, one must look at the flip side to using array formulas. They take much more computational power and can slow ...
Continue Reading
Posted on 05 July 2009
Array Formulas in Excel
An Array Formula in excel is a formula that uses arrays instead of single cell value as input.
Excel Array Formulas can be thought of as many formulas packed into a single super formula. In this article we will take a look at the syntax of an array formula, its various parts, learn how to write a basic array formula and then graduate on to writing the more powerful versions of it. Before we begin, let ...
Continue Reading
Posted on 04 July 2009
IF formula in Excel checks whether a given condition evaluates TRUE or FALSE.
Syntax of IF Formula
Example of IF Formula
Possible Errors returned by the IF Formula
IF Formula Syntax
IF Formula has three parts:
IF(criteria, value_when_true, value_when_false)
criteria
criteria is the condition that we would like to evaluate for being TRUE or FALSE.
value_when_true
value_when_true is the value that will be returned by the function when the above criteria evaluates to TRUE. This ...
Continue Reading
Posted on 09 June 2009
Normal distribution is one of the most commonly found distribution types in nature. The normal distribution is a continuous probability distribution where the data tends to cluster around a mean or average. If you were to plot the frequency distribution of a normal distribution, you will tend to get the famous inverted bell-shaped curve also known as the Gaussian function.
Coming to the point, we are sometimes faced with situations where we would like to test out a hypothesis or carry ...
Continue Reading
Posted on 20 May 2009
Have you ever encountered the #N/A, #REF!, #NAME?, #DIV/0!, #NULL!, #VALUE! or #NUM! errors in Excel? Learn more about these errors and how to avoid them in simple terms.
Why does Excel show #N/A, #REF!, #NAME?, #DIV/0!, #NULL!, #VALUE! or #NUM! errors?
#NAME? Error
The #NAME error is generated when a cell tries to reference a non-existent cell call a non-existent function. If you enter a formula that does not exist ...
Continue Reading
Posted on 21 April 2009
The VBA For Loop and the Do While loops are the two most important loops in the VBA language. They can be used to carry out a specific task in an iterative manner till such time that a condition is met (or the VBA loop is terminated).
Download the worksheet with examples of
the for, do while and do until loops in VBA
The VBA For Loop
The VBA For ...
Continue Reading
Posted on 01 April 2009
The OFFSET function in Excel returns a reference to a range that is offset by a specific number of rows and columns from another range or cell.
Before we begin, you can download the workbook containing a few examples of the
offset function in excel here.
The syntax for the Offset function in excel is: Offset( range, rows, columns, height, width )
range is the starting range from which the offset will be applied. Often it is a single cell but can ...
Continue Reading
Posted on 18 October 2008
You think you've entered the right formula and yet you get the dreaded #N/A error on the sheet.....well....here are some useful tips to get you through the vlookup blues!
=vlookup(value_to_match, range_to_find_this_value, which_column_to_return , try_to_approximate_if_no_match)
(Delimited) text being compared to numbers
If you have imported data from a plain text file (a .txt file), your data may most likely have numbers which have been converted to string. Excel will still display the values ...
Continue Reading
Posted on 23 August 2008
60 useful excel formulas that are a good to know
DATE = Returns the serial number of a particular date
DATEVALUE = Converts a date in the form of text to a serial number
DAY = Converts a serial number to a day of the month
EOMONTH = Returns the serial number of the last day of the month before or after a specified number of months
MONTH = Converts a serial number to a month
NOW = ...
Continue Reading
Posted on 23 August 2008
20 Excel formulas you should know
DATEVALUE = Converts a date in the form of text to a serial number
DAY = Converts a serial number to a day of the month
MONTH = Converts a serial number to a month
YEAR = Converts a serial number to a year
LEFT = Returns the leftmost characters from a text value
LEN = Returns the number of characters in a text string
RIGHT = Returns the rightmost characters from ...
Continue Reading