HLOOKUP and its Application

HLOOKUP and its Application HLOOKUP function performed it search horizontally by matching lookup value located at the first row (unlike VLOOKUP that is vertical) at the top to the row that holds the value you intend to return. It searched for either EXACT MATCH of the value you intend looking up in the table or[…]

Shares 0

VLOOKUP and its Application

VLOOKUP Function is one of the powerful Excel Formula that does a lot of magic for me. Interestingly, it enables me to carry out hundreds of calculations and analysis in minutes. Though the golden rule is to search vertically (top to bottom) of the left column (usually leftmost) of the lookup table until it established[…]

Shares 0

Using LEFT, MID and RIGHT for String Extraction

LEFT, MID and RIGTH functions are ideal tools to extract parts of text. LEFT function is used to extract the left-most characters, MID function is used to extract selected characters within text, and RIGHT function is used to extract right-most characters of a text in Excel. Let’s demonstrate how these functions work in the data[…]

Shares 0

INDIRECT Function

INDIRECT is used to create reference from text. More so, it can be use to generate random values. it returns reference to a range. Syntax: INDIRECT(ref_text[a1]) ref_text: a cell reference or text string that create the range reference. a1: this is said to be TRUE if the reference is using A1 reference style and FALSE[…]

Shares 0

Database Functions in Excel

This post is set aside to shed more light on database function use in Microsoft excel. Database is a detailed collection of data in such manner that can easily be read(access), manage, updated, and delete. In other word, a database can refer to as a list of related data in which rows of related information[…]

Shares 0

RAND and RANDBETWEEN functions

RAND() and RANDBETWEEN() functions RAND() is used to generate random decimal number between 0 and 1 with exclusion of 0 and 1. while RANDBETWEEN(bottom_number, top_number) very useful in generate lot of number between specified bound. Hope this post is helpful? For more Insight on Microsoft Excel, kindly buy any of my books “Productivity Boosting Aspects of Microsoft[…]

Shares 0

Use Goal Seek to generates best input for target results

Goal Seek is another amazing “What-if” analysis tool in Excel, with cool features that are very useful in generating the best input for a formula you already know the result, but not sure of the exact input. The limitation here is that it only work with one variable input. Solver is an ideal tool in[…]

Shares 0

Scenario Manager and its Application

The process of trying out different value for formula to see how the changes will affect the outcome is known as What-If Analysis. Scenario Manager in other hand, is one of the tools used to create and save multiple assumptions “what-If” Scenario in Microsoft Excel. What-If Analysis tools are located in the Data Menu. ZHM[…]

Shares 0

Advanced Filter in Microsoft Excel

Advance Filter feature in Excel facilitates the easier way to extract records from dataset using complex criteria while saving lot of time. For instance, the criteria for filtering in our earlier Filter demonstration “How to Filter Records in Excel”  include “BAJAJ BOXER” as product and “MUSA” as Sale Rep. which took us up to eight steps[…]

Shares 0