All articles with 'Microsoft Excel Formulas' Tag

This is a guest post written by Paramdeep from Pristine. Chandoo.org is partnering with Pristine to bring an excel financial modeling online training program for you. This is Part 6 of 6 on Financial Modeling using Excel In this tutorial we are going to learn how to build assumptions & input sheets in our excel […]

Here is a little experiment to freak out excel.

Go to cell C3 and write =C3 and press Enter. Excel would throw up nasty message saying, “Microsoft did not know what to do. We have a sent a support engineer to your home, but he is stuck at the round-about near your house.”

Well, not really. But what you did when you wrote the formula =C3 in cell C3 was, you created a circular reference.

A circular reference is created when you refer to same cell either directly or indirectly.

See Mona Lisa, in circular reference style.

Learn more about Excel Circular References, how to use them, examples, how to avoid them, how to deal with them in this article.

Ok, since excel school 3rd batch is going to open on 15th, I wasnt going to write anything today. I have slept just 4 hours last night, blame it on work (and that funny video on youtube). But I found 30 minutes free time, so here you go, a quick but delicious tip on making your data validation dynamic.

Dynamic Data Validation?!? What in the name of slice bread and peanut butter is that?

See the demo aside to understand and read on to master the trick.

This post builds on earlier discussion, How many hours did Johnny work? I recommend you to read that post too. Lets say you have 2 dates (with time) in cells A1 and A2 indicating starting and ending timestamps of an activity. And you want to calculate how many workings hours the task took. Further, lets […]

In the 4th and 5th part of our financial modeling series, we will learn how to model and build cashflow projections for project valuation.

We will understand the basics behind cash-flow modeling, what kind of non-cash expenses should be added back to get the free cash-flow. You can also get the blank and completed cash-flow projection worksheets to practice these techniques.

Dynamic charts are like my favorite food, Mangoes. They tempt, tease and taste awesomely. In this post, we are going to learn how to create a dynamic chart using check boxes and formulas as shown in the animation aside. Are you ready for some excel chart cooking?

You can also download a FREE Dynamic chart template from the post. So go ahead and make your boss drool.

Today we have home work for you. You need to help Johnny figure out how many hours he worked on splitting atoms. He has crazy boss who is behind him for that timesheet. Now Johnny needs your help to write the correct formula so that he can tell his boss how many hours he worked and go home.

Go ahead and read rest of this post and tell your answers using comments. Go!

As part of our Reader Awesomeness Week, Francis shares with us a travel site dashboard he made for his company. Francis took just two days to prepare this awesome dashboard which uses concepts like bullet charts, sortable KPI grid, Date based filtering, dynamic charts and more. I was really moved by Francis’ gesture in sharing his work with us, so much that, I did a video review of his work. You can see it in the embedded youtube video to the left.

Read the rest of the article to know how this dashboard is made and get a copy of the file.

Few days back we have learned how to find if 2 sets of dates overlap using Excel Formulas. For eg. we have learned that to check whether {1 July,2010 to 23 July 2010} overlaps with the date range {15 July, 2010 to 16 August 2010}, we can use a formula like, =if(or(and(x>=a,x<=b),and(a>=x,a<=y)), “Overlap”,”Do not overlap”) […]

Checking if a value falls between 2 other values is fairly common when you are working with data. In today’s quick tip, we are going to learn how to check for such between conditions in excel. You will be learning how to check if a value, date or text falls between 2 other values, dates or texts (when arranged in dictionary order) as shown in the picture aside.

Comparison of lists of data is something that we do all the time. Today, lets learn a few tricks that you can apply immediately to compare 2 lists using Excel. This post discusses how to compare two lists with formula based rules. If you just want to quickly highlight common values, click here. If you […]

It is almost 3:30 am now, I stayed awake for last 23 hours so that Excel School 2 can be ready for rolling. But that is no excuse for not having a post here. So here it goes. Excel has formulas for converting a bunch of text to UPPER, lower and Proper Cases. But not […]

The other day, while doing consulting for one of my customers, I had a strange problem. My customer has data for several KPIs and she wants to display average of top 5 values in the dashboard. Now, if she wants average of all values, we can use AVERAGE() formula if she wants top 5 values […]

Dashboards can be overwhelming with lots of details and context. A simple way to drag user’s attention to important stuff in the dashboard is to use alerts. See this example to understand what alerts mean. How to display alerts in Excel Dashboards? The easiest way to display alerts is to use Excel 2007’s Conditional Formatting […]

Denice, an Excel School student emailed me an interesting problem. I have a bunch of data from which I want to find the sum of values that meet a criteria. But I also want to exclude any rows that are hidden. Well, we know how to find sum of values that meet a criteria – […]