# All articles with 'Microsoft Excel Formulas' Tag

**First a quick personal update:** There has been a magnitude 7.8 earth quake in NZ on 14th November 2016 early morning. It is centered in Kaikoura, which is about 250 km away from Wellington. We did feel several shakes and after shocks. It has been an interesting and often scary experience. But my family is safe. I feel very sad for the all the damage and the loss for families in NZ. If you suffered from this quake, My prayers and thoughts are with you.

Yesterday, a friend asked me an interesting question. He has school distance data, like above. He wants to know which is the closest school for each school.

There are a few ways to answer this question. Let’s examine two approaches – formulas & pivot tables and see the merits of both.

Continue »**4**Comments }

## CP056: So which formulas you should care to learn?

*in*Chandoo.org Podcast Sessions

Podcast: Play in new window | Download

Subscribe: iTunes | Android | RSS

In the 56th episode of Chandoo.org podcast, let me answer the chicken and egg question of Excel users. **How many formulas should you care to learn?**

**What is in this session?**

In this podcast,

- Two personal updates
- 3 legs of formula writing
- Function knowledge
- Operators
- Referencing

- 6 categories of must-know functions
- Basic math
- Conditions
- Lookups
- Text
- Date & time
- Work specific

- Closing remarks & resources for you

**16**Comments }

## How many formulas should you learn? [Weekend Poll]

*in*Learn Excel

Over at twitter, @for_the_moves asks,

@For_the_moves Same as growing your vocabulary. Remember, words (or functions) = ideas. the more you know, the better you can think.

— Chandoo.org (@r1c1) October 12, 2016

That got me thinking. *How many functions should you care to learn?*

**15**Comments }

## Finding if a cell has 7 in it… [Pattern matching in Excel]

*in*Learn Excel

Imagine you work at MI5 as a HR officer. **You want to find all agents who have license to kill (licence 7)**. Your data looks like above.

**How would you go about it? **

If you filter the list or use FIND() or SEARCH() formulas, you will end up with agents who also have licenses 77, 17 or *not7*. So how would you solve this problem?

Of course, you do what any smart person does. You summon Excel and ask it nicely by using some wicked pattern matching logic.

Continue »**5**Comments }

## CP055: “Yes, I am back” edition (and a bonus Excel tip)

*in*Chandoo.org Podcast Sessions

Podcast: Play in new window | Download

Subscribe: iTunes | Android | RSS

Ladies & gentlemen, its time we revived the much loved Chandoo.org podcast. In the 55th episode, I do a lousy imitation of Arnold Schwarzenegger’s famous “I will be back” and tell you why there was such a long gap between episodes, my plans for reviving our podcast and more.

**What is in this session?**

In this podcast,

- Why there was such a long gap between last and this episode
- What next?
- How to extract every 6th item from a list?

Finally, spring weather showed up in Wellington this week. We cashed it as much as possible by going on treks, cycling trips, more treks and of course doing laundry.

Anyways, I don’t have time to blog. I must go out and help kids with some cycling. But I want to keep you busy this weekend. So here is a fun homework problem.

**Does my range have all numbers from 1 to n?**

Let’s say you have a range called ** range **(duh!). And you want to check if

**range**has all the numbers 1 to n (say n=5) in it, each number appearing only once (no more, no less). You can assume the named ranges

**range**and

**n**in your formulas.

See above examples to understand the problem.

So go ahead and post your formulas in the comments section. I will sneak in whenever I can to look at all your creative answers.

Continue »Let’s say you have a date in A1 and want to find out future date after 2 years, 4 months and 9 days.

Here are a few formulas you can try.

- =A1 + DATE(2,4,9)
- =EDATE(A1, 2*12+4) + 9
- =A1 + 2*365 + 4*30 + 9

Surprisingly, * each formula gives a different result! *So which one should you use?

**5**Comments }

## Introduction to Forecasting in Excel 2016 [Charts & Visual Analysis]

*in*Charts and Graphs

One of the coolest features of Excel 2016 is **forecasting. **Today, let’s understand how it works with a sample data set.

Watch below video to understand forecasting in Excel 2016.

Continue »*Here is an interesting problem to start your day.*

Let’s say you work as DNA sequencing engineer at *The Enterprise. *And you just unlocked the sequence that is responsible for all male problems. *The early onset of baldness. *The sequence code is AAAA. And you want to find out how many times this sequence is found in a sample of DNA strings, in the range B6:B19. Essentially you want the above.

So how do you write the formula?

Continue »Okay, this is an extension of the Neither “A” Nor “B” sum problem we discussed few days back.

Imagine you have a table named * mydata* with a few columns and a stop list named

*as shown above.*

**stop.list**How would you calculate,

- Sum of Hours for all activities excluding those in stop list?
- Sum of all
*Regular*hours for activities not in stop list?

So go ahead and post your answers in the comments.

Continue »We know how to use SUMIFS function to answer questions like, **“What is the sum of values for ‘A’?” **But how would you answer questions like,

- What is the sum of values that are neither “A” nor “B”?

We can still use SUMIFS, but it will get awfully long. So let’s turn our attention to other functions in Excel.

Continue »**12**Comments }

## Generating sequence numbers from cluster values [VLOOKUP to the rescue]

*in*Excel Howtos

Last night I got an email from Joshua, one of our readers with the subject – **Hard Excel problem. **Hard?!?, at this stage of summer, the hard problems seem to be (in no particular order),

- Lack of good quality mangoes to eat
- Intense heat and humidity
- Lack of good quality mangoes to eat

Yes, I like mangoes.

Any how, back to Joshua’s email, So I got curios and read it. He is facing a *curious *problem.

**90**Comments }

## Extract the 10 digit number [formula homework]

*in*Excel Challenges

Okay, time for another challenge.

Imagine you have some data like this. Each cell contains 3 numbers separated by line break – CHAR(10) and you need to extract the number that is 10 digits long.

Go ahead and solve this riddle.

Continue »**131**Comments }

## Sumerian Voter Problem [IF formula homework]

*in*Excel Challenges

Here is a simple IF formula challenge for you. Go ahead and post your answers in the comments section. Can this person vote in Sumeria? Imagine you are the chief election officer in the great country of Sumeria. You have introduced a new eligibility criteria for voters just before the grand presidential elections of 2016. In order […]

Continue »Here is an interesting scenario.

Let’s say you are looking at a time, like 9:42 AM and want to know which 15 minute slot it fits into. The answer is 9:30 – 9:45. But how would you get this answer thru Excel formulas?

Continue »