Blog Stats
 2,881,837 hits

Join 3,148 other followers
 Follow wmfexcel on WordPress.com
Search wmfexcel
Want Google to translate this page?

Recent Posts
Top Posts & Pages
 How to sort bar chart in descending order?
 Date Formats  A trick to format date with "st", "nd", "rd", "th"
 How to replace value with a value from another column in Power Query
 Show number in thousand (k) or in million (M) by using custom format
 Open an embedded Excel file during a slide show in PowerPoint?
 Repeat all item labels in Pivot Table (aka Fill in the blanks please)
 Calculating CAGR with Goal Seek in #Excel
 Perform VLOOKUP with 2 lookup values
 Rename column names in a dynamic way with #Excel #PowerQuery
 Insert a text string into a specific position of another text string in #Excel
My YouTube Channel
My Facebook page
 My Tweets
Category Archives: Formula
How to turn “1st January, 2017” into #Excel recognizable date?
Quite a long time ago, I wrote a post to discuss a trick to format date with “st”, “nd”, “rd”, etc… In this post, I am going to discuss the reversed way: Turning an English written date with “Dst MMMM, … Continue reading
Lookup a specific combination of characters exclusively, e.g. 5A not 5AA
Got the following question: hi, how do i search for a specific combination of characters exclusively i.e. if one cell contains 5A;3B&4C while another cell contains 5AA;3B&4C, and my search criteria is 5A, i want the results to return only … Continue reading
Using MIN/MAX to answer whichever lower/higher questions
What is “Whichever higher”? Situation: You call to your favourite restaurant to reserve a private room for family dinner on Saturday. The restaurant manager tells you that there will be a minimum charge of $2000 for a private room. That mean, … Continue reading
The amazing SUMPRODUCT
Advanced use of SUMPRODUCT for multiconditional SUM In the previous posts, we talked about the basic of SUMPRODUCT, the behaviors of logical values (i.e. TRUE/FALSE) when they are put into basic mathematics operations. These set the foundation to understand the … Continue reading
The logic gate
Have you heard about Logic Gate? I learned logic gate in high school physics. Decades ago… so don’t expect I can remember what it is. 😛 Having said that, I do remember the fundamental concept about AND gate and OR gate, … Continue reading
SUM the PRODUCTs or SUMPRODUCT? That’s the question.
The basic of SUMPRODUCT Suppose we have a column showing retail price of different items; another column showing the units sold (illustrated above). To get the total sales, most people will deploy a helper column to get the sales of each … Continue reading
CrossTab SUM with 3 variables
This post is trying to answer a question by Laura in the post of 2D SUMIF with two variables – one on column and one on row. Here’s part of the question: ……I want to look up the department in the vertical … Continue reading
Transpose Data (Static and Linked)
Answer to the 5 little Tricks posted in the beginning of the year – Part 1/5 Transpose Data – Result is static This is actually a simple trick of using Copy and Paster (Special, Transpose). See? It can be done in a … Continue reading
Posted in Excel Tips, Formula
Tagged Array formula, Copy and Paste, Find and Replace, Paste Special, TRANSPOSE
2 Comments
Be cautious when using SUMIF(s)
SUMIF is a handy but helpful function. The syntax is simple: =SUMIF(range,criteria,[sum range] What it does is quite straight forward indeed. In the example above, it instructs Excel to look into the range (A2:A7), look for the matching criteria, which … Continue reading
Posted in Excel Tips, Formula
6 Comments
Writing a long formula in steps
Writing a long formula is not easy, even for an advanced Excel user. What I mean long is a formula with many nested FUNCTIONS within a single formula. The difficulty I am talking about is not related to whether you understand the … Continue reading
Text manipulation with Excel functions
We talked about RIGHT, LEFT, MID, UPPER, LOWER, PROPER, FIND, SEARCH, REPLACE and SUBSTITUTE in the past few weeks. Now it’s time to put every thing together to solve a problem. This is actually the most amazing part of using … Continue reading
Caseinsensitive SUBSTITUTE???…
We talked about REPLACE and SUBSTITUTE last week. At the final note, I said that SUBSTITUTE is casesensitive. If we need to perform a caseinsensitive SUBSTITUTE, it is indeed not an easy job unless we are talking a single letter … Continue reading
REPLACE vs. SUBSTITUTE in Excel
Another pair of functions that has very similar meaning literally. Although I am not able to tell you the differences between “replace” and “substitute” in English, I can show you the differences of the REPLACE and SUBSTITUTE in Excel. … Continue reading
FIND vs. SEARCH
In many situations, we use either FIND or SEARCH together with LEFT, RIGHT, MID to achieve what we need. A typical example is to extract the first name and the last name from a full name where a coma acts as … Continue reading
UPPER, lower, Proper
I am wondering why these functions are not available in Word, but Excel? Don’t make me wrong. I like these functions. Nevertheless I am not a big fan (nor a small fan) of using Excel as word processor. Whenever possible, … Continue reading
RIGHT. I LEFT. In the MID of…
Obviously this post is about the most popular text related functions in Excel. Meanwhile, it is also a message from me… Yes, you are RIGH! I LEFT my excompany and have landed on a new job which is much more … Continue reading
Display single letter Day of week in Excel
Is it achievable? Not by formatting but formula.
Number Stored as Text…
It is something basic and commonly seen. Nonetheless, it is really important to understand the different behaviors between a number, and a number stored as text, especially if you want to move forward to write effective formula. This post is intended … Continue reading
Three different ways to do casesensitive lookup
If you do VLOOKUP, you probably know that one of the limitations of VLOOKUP is caseinsensitive. It means case is not a consideration in the lookup process, where PETER is essentially the same as peter. Hence VLOOKUP will consider that … Continue reading
Rank in subgroup… RANKIF?
To get the overall ranking is easy with the RANK function. The syntax of RANK =RANK(Number,Ref,[order]) Number is the number we want to rank; Ref is the list of numbers of comparison (could be an array, a reference or a list … Continue reading