Category Archives: Formula

SUM vs. SUBTOTAL

Posted on March 26, 2014 by MF

How to SUM a range but ignores SUM result in between?, i.e. How to get a Grand Total with SubTotals in between? How to SUM but ignores hidden cells or filtered cells? SUM could be the most popular function used in … Continue reading →

Rate this:

Posted in Excel Tips, Formula | Tagged , | 12 Comments

Excel Basic – Sum vs. “+”

Posted on March 18, 2014 by MF

+ – x / would be the four basic operators you learned in Excel basic.  When you learned to use SUM instead of +, did you ask yourself or your teacher why? 1) SUM is much easier to input and … Continue reading →

Rate this:

Posted in Excel Basic, Formula | Tagged | 9 Comments

How to lookup only partially-matched value?? – Crazy lookup!!!

Posted on January 23, 2014 by MF

This is a real workplace problem. Look at the two tables below, the task was to map the Shop ID from Table 1 (left) to Table 2 (right).  In both tables there is a common key “Shop Name”.  Sound easy? … Continue reading →

Rate this:

Posted in Excel Tips, Formula | Tagged , , , | Leave a comment

Alternative to vlookup – Index and Match

Posted on January 16, 2014 by MF

Lookup from Right to Left In the above example, if it is given the Student ID, it would be so easy to find one’s corresponding Name and Grade by using vlookup.  However, if we would need to find out the … Continue reading →

Rate this:

Posted in Formula | Tagged , , | 3 Comments

Advanced vlookup – wildcard characters “?” and “*”

Posted on January 12, 2014 by MF

Why vlookup gives me wrong answer??? Bugs in vlookup???  I am sure it’s not about text vs. number, but it still fails to give the correct answer!!! I guess many users are not aware of the wildcard characters in Excel.  … Continue reading →

Rate this:

Posted in Formula | Tagged , , | 27 Comments

Advanced vlookup – Text vs. Number

Posted on January 8, 2014 by MF

Why does vlookup not return the lookup value? I am sure it is in the source data table.  I see it by my eyes. Have you ever experienced this?? That’s normal when you import your data from other database.  Fortunately … Continue reading →

Rate this:

Posted in Formula | Tagged , , | 14 Comments

vlookup – True or False??

Posted on January 2, 2014 by MF

Usually we use vlookup for answering a particular question like “How many customers we had on January 1st 2014?”  We expect an exact match and hence using FALSE as the last argument in the vlookup formula. When do we use … Continue reading →

Rate this:

Posted in Formula | Tagged | 12 Comments

vlookup with Match

Posted on December 31, 2013 by MF

Hard-coding the col_index_num in the vlookup formula is not a wise choice as you might have to look into the formula and make changes every time you copy and paste your formula.  An alternative approach of marking column index on … Continue reading →

Rate this:

Posted in Formula | Tagged , | 8 Comments

Tips in constructing vlookup

Posted on December 29, 2013 by MF

Tips in constructing vlookup We talked about the syntax of vlookup and we had written a vlookup formula successfully in the previous post.  YEAH! Now we want to ride on the formula by copying it DOWN and to the RIGHT … Continue reading →

Rate this:

Posted in Formula | Tagged | 5 Comments

The basic of vlookup

Posted on December 27, 2013 by MF

vlookup – Something you should know about The basic of vlookup Definition of LOOKUP (http://www.merriam-webster.com/dictionary/lookup) :  the process or an instance of looking something up;especially:  the process of matching by computer the words of a text with material stored in memory v, in the … Continue reading →

Rate this:

Posted in Formula | Tagged | 10 Comments

Calculating % Change is so easy… to make a mistake!

Posted on November 30, 2013 by MF

How to calculate percentage change with negative number? I believe calculating percentage change should have a very top ranking on the most frequently used calculations in spreadsheet.  Agree? For me, I often simply do the calculation with a simple formula: … Continue reading →

Rate this:

Posted in Excel Tips, Formula | Tagged | Leave a comment

Convert unit of measurement from one system to another system

Posted on October 19, 2013 by MF

如何在EXCEL轉換量度單位? Have you ever wanted to convert Degree Celsius to Degree Fahrenheit or Meter to Mile in Excel? Yes, I did.  What I did was to first Google the formula for such conversion, and then apply the formula manually in … Continue reading →

Rate this:

Posted in Formula | Tagged | Leave a comment

Do you need TIME?

Posted on October 7, 2013 by MF

To convert [hour:minute] into number of hours quickly. If you are working in HR field, you may need to calculate wages for part-time staff whose salary is paid on hourly basis.  So you need to convert 8:45 (8 hrs 45 … Continue reading →

Rate this:

Posted in Formula | Tagged | 2 Comments

How to calculate number of days, months, or years from one date to another date – DATEDIF function

Posted on September 17, 2013 by MF

如何計算兩個日子之間有多少年／月／日？ Recently, I got enquired about function for calculating number of days from one date to another date.  DATEDIF is on the top of my mind.  ：） Interestingly this function is not commonly known probably because it won’t show up … Continue reading →

Rate this:

Posted in Formula | Tagged | 10 Comments

How to switch between Absolute and Relative reference? F4

Posted on September 12, 2013 by MF

如何快速轉換公式中的絶對參照及相對參照? To kick off my first blog, I would like to introduce my buddy on keyboard to you – F4 To me, it helps 1)      Switch between Relative and Absolute reference easily when typing formula 2)      Repeat last action Let’s … Continue reading →

Rate this:

Posted in Excel Tips, Formula, Shortcut | Tagged | 1 Comment