All articles with 'Microsoft Excel Formulas' Tag

Purpose of the dashboard:

This is a guest article written by John for our Excel Dashboard Week.

This Dashboard was constructed for a number of reasons, one of which was to reduce the number of reports produced with the same data ( up to 6 separate files ). As we all know, when it comes to senior management and reports / files the more information they can get on one report / file the better for them. So, with this in mind I created the Dashboard to show the data they need to see “quickly” each week.

Here is a fun formula to write.

Given a number in cell, I want you to find the sum of digits in it. So, for eg. if you have the number 3584398594 in a cell, the sum would be =3+5+8+4+3+9+8+5+9+4, equal to 58.

Now, how would you write a formula to find this sum automatically based on the number entered in the cell?

Go ahead and figure it out. If you can, come back and check your answer with mine below.

Last Friday, we have learned about an interesting formula – IFERROR Formula using which you can easily handle errors in Excel workbooks.

Quite a few people reading that page asked, “Wow, this is good. But how can I take a sheet full of =IF(ISERROR(…)….) formulas and convert them to =IFERROR()”

There is a different set of folks who asked “Wow, this is good. But quite a few of my colleagues use Excel 2003 and they see a bunch of #NAME errors when I send them an excel workbook with IFERROR formulas. Any help?!?”

I am pleased to announce that I wrote 2 simple macros, iferror2iserror() and iserror2iferror() that would scan formulas in a bunch of selected cells and convert them from IFERROR to ISERROR and vice-a-versa.

If IFERROR() were to be a person, I would hug her so hard that Jo (my wife) would get in to a cat fight with her. I know many a woman (and man) who get in to a fight with Excel formulas often. But thankfully, we avoid that as IFERROR is not a real person. It is, however a darned useful formula.

Since I cannot hug a formula anymore than I can get my son to sit tight, I will go ahead and sing an ode to her, in my style – by writing about how useful and powerful IFERROR formula is.

We have a new series on chandoo.org. – Excel Challenges. From now, every 1-2 months, I will post an interesting Excel Challenge. These are tricky problems for which elegant solutions should be found. To keep the challenges exciting, we will have a small prize for a winner (if more people answer correctly, we pick one randomly)

Excel Challenge #1 – Find Overlaps in Machine Schedule Dates

We have an Excel Table, with the following data (snapshot above). You need to write formulas to display Ok or Not Ok in the adjacent column based on the following criteria:

1. Display Ok if the scheduling dates for that row do not overlap with remaining scheduling dates for that machine
2. Display Not Ok otherwise.

Our of curiosity and fun I asked you “how long have you been using Excel?”. I was overwhelmed by the response we got to this simple question. More than 437 people responded with their comments, stories and enthusiastic responses. Thank you so much.

It would taken me more time to make the charts and understand the data. But thanks to Hui, who volunteered to tabulate all the survey data in a simple CSV.

Shown above is a chart I came up with based on the data. Read the rest of the post to understand the survey results and view more charts. Also, you can download the excel workbooks and original data set to play.

The folks at Washington Post made an interesting chart to understand whether winning a Grammy award makes any difference to album sales. Go ahead and browse it if you have not already seen it. Go, I will wait.

Are you impressed?

I really liked this chart. This is what I liked about the chart,

• It tells a story.
• It is an ego chart. We would all instantly search for our favorite artists and learn about how Grammy award changed their album sales.
• It is a simple chart. No clutter, no gaudy colors, just a bunch of lines and the story is out there.
• It lets you play.

In fact, I liked the chart so much that I wanted to make it in Excel.. You can see a snapshot of what I came up with above. Read the rest of this article to learn more.

In the second part of our series on project finance using Excel, Paramdeep tells us how to model “interest during construction” in real estate and construction type of projects. We take a case of hospital construction and learn how to calculate the IDC using circular references.

You can also download both blank and completed model so that you can test the skills.

It is not too sunny here, but I am going to put on my business man hat. At the end of each month, I ask myself if my business (chandoo.org that is) has performed better or worse. One simple way is to look at previous month’s numbers and then I know how good the latest […]

This is a guest post written by Paramdeep from Pristine. Chandoo.org runs Financial Modeling School program in partnership with Pristine Careers. Visit Financial Modeling School to learn more and sign-up for our newsletter.

Greetings!

It’s been long time since we interacted on Chandoo.org. Actually I was very busy teaching the 105 awesome students for financial modeling in Excel. We all worked together to create some easy and some complex financial models. I found the journey to be quite exciting and enriching (From the feedback that I got, my students too didn’t find it bad either 😉 )

During the interaction, I found that a lot of students were looking for financial modeling around the project financing as well. So we thought why not introduce financial modeling for project finance.

In this post I will speak about some of the key aspects of a project finance model and why it can be different from modeling a normal company.

This is a guest article by Matt, who works as a Business Analyst with allrecipes.com. He shares with us how he is using Excel to become a rockstar business analyst. In his own words,

“At Allrecipes.com we use excel for a variety of purposes. Analyzing site trends, forecasting traffic, charts, dashboards, and slide shows; you name it, we use excel for it. That’s why Chandoo’s tips have been so helpful – because we use excel every day. Thanks to chandoo.org, I’ve developed a reputation as an “excel wizard” and even taught a 4 week excel training class!“

Theodor, one of our readers, first emailed me in December asking a question. But he also made a promise to share some of his techniques with us thru Guest posts. Naturally, I was too happy and invited him to share a file or two so that I can use them for articles here.

Later during Christmas holidays, he sent me this beautiful Holiday Request Form made using Excel.

In the final post of the Are You Trendy? series we will look at the use of Excel Charts and associated Trendlines for trend analysis and I will give you a free tool (Normally valued at $200, I wish) which will allow interactive assessment of a Charts Trendline Y value for any X value.

Does your data hold hidden secrets?
In Part 2 of Are You Trendy? we will examine the Excel functions that assist us in Trend Analysis.

I have an exciting news & massive post for you.

Chandoo.org reaches 20,156 RSS Subscriber mark on Jan 19, 2011As of Jan 19, 2011, our little blog has registered our 20,000th RSS Subscriber. While this is not a huge achievement or anything, It certainly calls for celebration. I am so happy to see our mission to make people awesome in Excel is reaching out to more people everyday. Thank you.

To celebrate this milestone, I am doing a massive post with 220 Excel tips, tricks, tutorials & templates.

Formulas [52 tips]
Formatting & Conditional Formatting [36 tips]
Charting [60 tips]
Tables & Pivot Tables [15 tips]
Using Excel [47 tips]
Free Downloads [5 tips]
Recommended Resources [5 tips]