Archive for June, 2012

Check if a list has duplicate numbers [Quick tip]

Published on Jun 28, 2012 in Excel Howtos, Learn Excel
Check if a list has duplicate numbers [Quick tip]

A while ago (well more than 3 years ago), I wrote about an array formula based technique to check if a list of values have any duplicates in them.

Today, lets learn a simpler formula to check if a list has duplicate numbers.

Assuming you have some numbers in a range B4:B10 as shown below, we can use MODE + COUNTIF formulas to check if there are any duplicate values in a list.

Continue »

Extract Numbers from Text using Excel VBA [Video]

Published on Jun 26, 2012 in Excel Howtos, VBA Macros
Extract Numbers from Text using Excel VBA [Video]

Last week we discussed how to extract numbers from text in Excel using formulas. In comments, quite a few people suggested that using VBA (Macros) to extract numbers would be simpler.

So today, lets learn how to write a VBA Function to extract numbers from any text.

Continue »

Visualize Excel Salary Data & You could win XBOX 360 + Kinect Bundle [Contest]

Published on Jun 25, 2012 in Excel Challenges
Visualize Excel Salary Data & You could win XBOX 360 + Kinect Bundle [Contest]

Its contest time again! Put on your creative hats & bring your Excel skills to the game.

Analyze more than 1900 survey responses & present your results in a stunning fashion, and you could walk away with an XBOX 360 + Kinect Sports Bundle (valued at $299).

Sounds interesting? Read on.

Continue »

My Australian Experience [Personal]

Published on Jun 22, 2012 in personal
My Australian Experience [Personal]

As the gentleman at immigration counter stamped my passport & said, “Welcome to Australia”, I could barely contain my excitement. You see, Australia has been on my list of places to visit as far back as I can remember. It finally happened on On Sunday, 29th of April 2012.

After collecting my baggage, I walked out of Sydney Kingsford Smith Airport. My friend Danielle (from Plum Solutions) is waiting for me there. Thus began my Australian adventure and it was fantastic. (Aussies so fondly use this word).

Grab a fine cup of coffee, sit back and read to know how the whole experience went.

Continue »

Join our Financial Modeling Class before fee hike [Quick update]

Published on Jun 20, 2012 in Financial Modeling
Join our Financial Modeling Class before fee hike [Quick update]

Hello lovelies,

I have a quick announcement to make. As you know, we run 2 online courses on Financial Modeling.

1) Financial Modeling School
2) Project Finance Modeling School

After running them for almost 2 years, we are now planning to hike the fees by $50. See below table to know about new fees.

Continue »

Extracting numbers from text in excel [Case study]

Published on Jun 19, 2012 in Excel Howtos
Extracting numbers from text in excel [Case study]

Often we deal with data where numbers are buried inside text and we need to extract them. Today morning I had such task. As you know, we recently ran a survey asking how much salary you make. We had 1800 responses to it so far. I took the data to Excel to analyze it. And surprise! the numbers are a mess. Here is a sample of the data.

Continue »

Use MAX to find latest date in a list [Quick tip]

Published on Jun 14, 2012 in Excel Howtos

Here is a quick tip that I learned while conducting training classes in Australia. If you have several dates in a range and you want to find out what the latest date is, just use MAX, like: =MAX(A1:A10) would give you the latest date. A Question…, Assuming you have some dates (not necessarily sorted) in […]

Continue »

Thermo-meter chart with Marker for Last Year Value

Published on Jun 11, 2012 in Charts and Graphs, Excel Howtos
Thermo-meter chart with Marker for Last Year Value

During a recent training program, one of the students asked,

Thermo-meter chart is very good to show how actual value compares with target (or budget). But how can we add another point for say Last Year value to the chart with out cluttering it.

Something like above.

Sounds interesting? Read on

Continue »

Formula Forensics 023. Count and Sum a Filtered List according to Criteria

Published on Jun 7, 2012 in Formula Forensics, Huis, Posts by Hui
Formula Forensics 023. Count and Sum a Filtered List according to Criteria

Today at Formula Forensics, we look at how to Count and Sum data using Criteria on Filtered data sets.

Continue »