- Introduction to IRR & XIRR functions
- Calculate rate of return from a set of cash-flows with XIRR
- Simulating purchase or terminal value changes with data tables
- Apply conditional formatting to visualize the outputs
- Common issues and challenges faced when using XIRR

The post Using IRR with Data Tables – Modeling Cash-flow Scenarios in Excel appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>- Introduction to IRR & XIRR functions
- Calculate rate of return from a set of cash-flows with XIRR
- Simulating purchase or terminal value changes with data tables
- Apply conditional formatting to visualize the outputs
- Common issues and challenges faced when using XIRR

**Let’s set a context for this.** Say, Mr. John wants to purchase an orchard. But he has a problem. He wants to know if the orchard is worth his investment. So he prepared all the cash-flows (purchase, operational expenditure every year, fruit sales and terminal value), like this:

He wants to calculate the potential return on investment.

We can use IRR (Internal Rate of Return) function in Excel to calculate return from a series of even spaced cash flows.

For example, say you invest $10,000 in a savings bond and you get $500 per year for next 5 years and then you will get $11,000 back. What is the return on this investment?

Because the cashflows are evenly spaced (ie every year you get some money, with upfront investment of 10,000), we can use IRR formula to calculate the return, like this:

=IRR({-10000,500,500,500,500,500,11000})

**The result will be 5.72%.**

An IRR of 5.72% in simple terms means: your investment of $10,000 is giving you 5.72% return over the period of investment.

All else being equal, if you have multiple options to make an investment, you pick the option with highest IRR.

.. For example, an energy company may use IRR in deciding whether to open a new power plant or to renovate and expand a previously existing one. While both projects are likely to add value to the company, it is likely that one will be the more logical decision as prescribed by IRR.

Investopedia, IRR page

Often, your cash-flows are not regular. In such cases, you cannot use the IRR function. But you can use XIRR function to calculate the rate of return, given a set of dates & cash-flows.

=XIRR(amounts, dates)

For example, =XIRR(C7:C35, B7:B35) will return the rate of return based on values in column C and dates in column B.

- Obviously, both values and dates must be equally sized.
- There must be at least one negative and positive cash-flow values. Otherwise, XIRR will return #NUM! error.

More on XIRR function sytanx, usage and errors [Microsoft Help]

Since, we have a series of estimated cash-flows (shown below), we can easily calculate the rate of return using XIRR function.

=XIRR(C7:C35, B7:B35)

You will get the rate of return as answer.

Now, let’s say John thinks both purchase price and terminal value can change. Assuming we have the percentage changes in cells like below,

… we can calculate IRR by making necessary changes to the input cells (B7 & B8)

But, what-if there are a whole heap of possible changes?

**We want to calculate IRR for each of these scenarios.**

*We can write a heap of XIRR formulas. But that is a hassle. *There must be a better way.

The answer is Data Tables. This powerful and elegant feature of Excel can take a formula and calculate it for any set of scenarios.

*This page is a simple introduction to Data Tables.* For a more comprehensive overview, examples and advanced use-cases, visit Excel Data Tables page.

We need a two variable data table to calculate all the IRR combinations. To set this up follow these instructions:

- Modify input data cells (B7 = Purchase Price and B8 = Terminal Value) so that they include the % change values from cells H10, H11 respectively.
- Set up the IRR calculation in the top left corner of the scenario grid.
- Select entire range, including the top left cell with IRR calculation.
- Go to Data ribbon and click on What-if scenarios

- In the data table inputs screen, select H11 (Terminal value %) as Row input and H12 (Purchase value %) as Column input.

That is all. Excel will calculate all possible scenario values for IRR.

*This page is a simple introduction to Data Tables.* For a more comprehensive overview, examples and advanced use-cases, visit Excel Data Tables page.

We can easily create a visual from the scenario results; something like this:

All you need is conditional formatting. Select the scenario outputs, go to Home ribbon > Conditional Formatting > Color Scales > and apply any scale that suits your needs.

You can also make charts from this.

Keep these things in mind whenever you are modeling scenarios, with IRR or anything else.

**Select right what-if variables:**You need to select variables that*can be controlled*. In our example, Purchase Price change is a variable that is*somewhat*under control. Mr. John can always negotiate the price down / up. But Terminal Value Change is not that much in our control. It is well into future and cannot be determined. Instead, a better variable to model is Opex changes, as these are in Mr. John’s control.**Do not use Excel Tables with this model:**First two rows of our cash-flow are dependent on formulas and rest of them are simple inputs. This means, you should not be using Excel Tables (CTRL+T) for this data.**Modeling scenarios on investment horizon:**What-if John wants to calculate the returns for 5, 10, 15 years of operations? In such case, you need to use OFFSET or INDEX formulas to dynamically fetch the range for IRR calculation. Here is an example of IRR with Offset.**Do not average the IRRs from scenarios:**You cannot average all the scenario IRRs to arrive at composite rate of return. This can mislead. Instead either go with weighted averages or use the scenario model to understand the variations.**Modeling more than two variables:**Excel data tables allow only two variables. But you can*cheat*Excel and calculate multi-variable data tables. Start by setting up a scenario table that all all possible variables values and scenario IDs. Then use Scenario ID as one-way variable to calculate the outputs. See this page for an example.

I made a *mini* Masterclass on financial modeling, analysis and data tables with IRR explaining this. Please watch it below to learn more about this concept. **You can also watch this video on my YouTube channel.**

**Click here to download the complete IRR + Data Tables model file. **Play with it to learn how to use Data Tables, XIRR and IRR functions.

Data Tables & Scenario modeling is one of the 50 ways to analyze data in Excel. I run a comprehensive online class on data analytics, modeling and data science with Excel. In this program you can learn all about statistical, marketing, financial and operational data analysis with Excel. Check out the course outline, sample lessons and more information from below link.

**50 ways to analyze data course.**

The post Using IRR with Data Tables – Modeling Cash-flow Scenarios in Excel appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>The post Impress with Tornado Charts in Excel appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>Tornado charts / population pyramids are very useful in below situations.

- See population distribution by gender (obviously)
- Purchase patterns by gender and ages
- Customer walk-ins by gender and time of day
- Distribution of time on page by visitor type (customer / prospect)
- Units purchased by customer types (international vs. domestic)

In general, you can apply this type of charts whenever you have a strong binary category and a detailed dimension (time of day / age / distribution etc.)

Just open Excel in your laptop, load your data, go outdoors in Oklahoma (if you live outside Midwestern states then catch a flight to nearest one) and wait for a tornado.

Jokes aside, to tornado charts are nothing but cleverly formatted bar charts. Let me demonstrate how to make them from a sample data of London’s actual & projected population numbers.

Watch this video tutorial or read the instructions below to create this chart in Excel.

This depends on your data. For the London population data-set that I am using, we need a summary like this.

Once you have the numbers by age and gender, we need to turn one of the gender values to negative.

To do this, just create a copy of the *calculations, paste as linked values. *Turn one gender values to negative by using – (minus) sign in the front.

We get this sort of table.

Select your age by gender (with negative values for one gender) values and insert “stacked bar” chart.

You will get this.

We are almost done. Just format the chart using below steps.

- Set gap between bars to 0 (select any bar, press CTRL+1 to format them and set gap width to 0)
- Move vertical axis labels to either low or high position, so that you can read them.
- Flip the tornado so you can see age 0 on top and 100 at bottom. To do this, select the vertical axis, go to format and click on “Categories in reverse order” option.
- Remove -ve signs from the horizontal axis labels. To do this, select the axis, format and go to “Number” settings. Here, you can tell Excel to omit the -ve sign while displaying labels with special codes. For numbers, you can use the code 0;0;;

Related: For more on custom formatting codes, see this page. - Move legend to top
- Add relevant chart title and captions if necessary.

Here is a time lapse GIF of the formatting steps.

Now that you have a tornado chart, you can easily make it interactive. Just move the slicer (from step 1) closer to the chart and you have an interactive tornado chart in Excel.

**Click here to download the full tornado chart template**. Use it to learn how to make these.

**Histograms and Pareto Analysis**

When you have more than two categories, then try histogram charts. You can explore distribution of all data or make it interactive (with slicers of course). See this page for details on histograms in Excel.

**In-cell bar charts – when you have too many categories**

Another option is to make a table visualizing everything. But a table of numbers can be dull. So make them visual with in-cell bar charts. Here is a case study of survey results from “how people in various countries spend money?”.

*Check out Advanced Charting page for more inspiration.*

I create tornado charts often, especially when I am exploring demographic trends.

**What about you?** Do you make tornado charts? If so, how do you make them? Please share your tips and experiences in the comments section.

The post Impress with Tornado Charts in Excel appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>The post Excel Basics: How to add drop down list to validate data appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>Validating your data as you type can prevent any surprises when you are doing analysis / follow-up on the data. Thankfully, excel has the right tools to do it. **Excel drop down list can assist you in picking up a value** from a valid list to enter in a cell. Here is a short how-to guide to get you started on data validation in excel.

- First, set up a list of valid values in range of cells. Say your valid list of entries is in A1:A6.
- Now go the cell where you want to validation drop down to appear.
- Go to Data ribbon and click on Validation
- Set up “List” as allowed values and enter =A1:A6 as Source (see below picture)
- Done. Now you can see the drop-down in your cell.

If you want to use a source list that has duplicates and want to ignore them when setting up validation drop-down, then you have two options. Something like this:

- Use Excel Dynamic Arrays (works in Excel 365)
- Use Pivot Tables (works in all versions of Excel)

Let’s say your list of inputs is in customers[Education] column.

*Note: This works only in Excel 365 with dynamic array feature. Not all 365 users will have access to DA now, but everyone of them will get Dynamic Arrays soon.*

Just go to an empty cell (preferably in a separate worksheet like settings tab) and type** =SORT(UNIQUE(customers[Education]))**

**Excel will spill your data down** to next few cells depending on how many unique values are in your data.

Let’s say your formula is in cell A1

Now, go to Formulas > Define Name and create a name for validation options as,

**Finally, use myOptions as list source for data validation.**

This is most compatible option as it works in all versions of Excel.

- In a new sheet or blank range, insert a pivot table from your data.
- Add the Education field to row labels area
- Remove any grand, sub-totals
- Let’s say the first item in the pivot is in cell A2.

Now, create a name with myOptions and use the formula

=OFFSET($A$2,0,0, COUNTA($A$2:$A$21), 1)

this will make a *dynamic named range *with how many ever education options are there in that pivot table.

*Note: Change $A$21 to a cell address *further down if you will have more options.

Finally, use myOptions as the list source for data validation.

Your drop-down list without duplicates will be ready.

I made a video explaining how to make dropdowns without duplicate values. You can see a cameo from Nishanth (my son) in the video. Check it out if you want to understand how Dynamic Array method and Pivot Table method can be setup. Watch it below or visit my YouTube Channel.

Drop-downs are very useful for data analysis, charting and reporting work. They are user friendly and easy to set up. That said, keep these ideas in mind when implementing them.

**Use named ranges:**Instead of hard-coding cell addresses, use named ranges for setting up validation lists. This will also enable you to connect data validation list to table columns thru structural references.**Source lists in a separate tab**: Whenever possible, set up all your source lists in a separate tab. I call mine “Settings”. This will make any changes easy for you.**Don’t have too many options:**If your drop-down is having more than 50 options, consider two-level cascading drop-downs or some other way to gather inputs.**Try Form controls or Slicers too:**Drop-downs are great, but they are just one of the many ways to add*interactive abilities*to your workbooks. Consider form controls and Slicers too.

I made a workbook with simple and “avoiding duplicates” examples. **Please download it here** and practice to learn more about these techniques.

Here is a collection of useful tricks and ideas with Data Validation. Check them out to learn more.

- Using OFFSET or Table Refs to set up dynamic validation lists
- Switch between short & long lists with IF formula in validation list
- Use dropdown list to make a dynamic chart
- Make AWESOME Data Entry form with validation rules – Case Study

The post Excel Basics: How to add drop down list to validate data appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>The post Zelda Stamina Wheel Chart appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>In the breath of the wild video game, Link the lead character has stamina. As he runs, climbs, flies or swims the stamina runs out. Link can have up to 300% stamina. To show how much stamina he has, Nintendo uses Stamina Wheel. You can see a sample of the 300% stamina wheel running out slowly as Link is flying to the right.

I thought it would be cool **to recreate this stamina wheel in Excel**. It is a *kinda sorta *gauge chart with ability to go up to 300%. See the demo below and read on to learn how to make this in Excel.

**This chart works well when you want to compare actual vs. target** (or budget vs. actual) performance where there is a possibility of >100% achievement.

Imagine you have data like this in 2 cells. You can easily calculate Pct in the third cell.

**Quickly calculate these six values using simple IF formulas**. The filled portion will be either 1 or fraction depending on the Pct. The gap will be difference from 1. See below list for sample formulas

- Circle 1 Filled: =MIN(pct, 1)
- 2 Filled =IF(pct>1,MIN(pct-1,1),0)
- 3 Filled =IF(pct>2,MIN(pct-2,1),0)
- Gap =1-Filled for all circles

Now that our data prep is done, let’s go with making some charts.

The first step for making stamina wheel chart is to create donut chart from our calculated values in Table 2 above.

We get this. (Note if your donuts look different, go to Chart Design ribbon and click on “Switch row / column button”.

Right click on circle 1 and select “Change series chart type” option. Now set up the options such that,

- Circle 1 should be pie chart
- Circle 2 & 3 should be donut charts, but on
**secondary axis.**

Our stamina wheel chart at this stage looks like,

We want to set “Filled” portions in one color and “Gap” portions in white color.

Carefully select individual points on the chart (there are total 6 points) and color them one at a time. You may need to change “Actual” value to see the gap portions as some points will have zero for them.

We will end up with this chart.

We are nearly done. Remove any unnecessary chart elements (title, legend etc.)

For label, Select the chart, add a circle shape to it. Move it so that it is centered on the chart. Fill the circle with white color and link it to a cell that has the Pct completion value.

**Our final stamina wheel chart looks like this:**

I made a video tutorial explaining my obsession with Breath of the Wild and how to make this chart in Excel. Watch it below if you need help. You can also see this my YouTube Channel.

If you want your own stamina wheel for a presentation or fund-raiser, just **download it from here**. Change the “Actual” and “Target” values and your stamina wheel will be ready.

Stamina wheel is a type of gauge chart. Gauges or speedometer charts have a lot of critics. That said, they are also very familiar metaphors. If you are looking for some inspiration and alternatives for boring budget vs. actual charts, then consider the stamina wheel. It is fun conversation starter.

If you want some alternatives to stamina wheel, check out below charts:

- Thermometer chart – ever green way to visualize budget vs. actual data
- Beautiful Budget vs. Actual chart – you got to see this to believe the hype
- Target vs. Actual – Biker on a hill chart – a biker chasing the target, what else I can say
- Speedometer / gauge chart – simpler version of stamina wheel.

**Get full stamina wheel + bonus **with this AWESOME Excel course. Learn everything about data analysis, charting and dashboards from the comfort of your couch or office chair.

**Click here to know more about Excel School**.

The post Zelda Stamina Wheel Chart appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>What is weighted average?

Wikipedia defines weighted average as, "The weighted mean is similar to an arithmetic mean ..., where instead of each of the data points contributing equally to the final average, some data points contribute more than others."

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

The post Weighted Average in Excel [Formulas] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>Weighted average or weighted mean is defined as [from wikipedia],

The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.

…If all the weights are equal, then the weighted mean is the same as the arithmetic mean.

*or in other words, why you should not calculate normal average?*

**Well, it is because, in some situations normal averages give in-correct picture.** For eg. assume you are the CEO of ACME Widgets co.. Now you are looking annual salary report and being the numbers-gal you are, you wanted to find-out the average salary of your employees. You asked each department head to give you the average salary of that department to you. Here are the numbers,

Now, the average salary seems to be $ 330,000 [total all of all salaries by 5, (55000+65000+75000+120000+1200000)/5 ].

You are a happy boss to find that your employees are making $330k per year.

**Except, you are wrong.** You have not considered the number of employees in each department before calculating the average. So, the correct average would be $76k as shown above.

There is no built-in formula in Excel to calculate weighted averages. However, there is an easy fix to that. **You can use SUMPRODUCT formula**. By definition, SUMPRODUCT formula takes 2 or more lists of numbers and returns the sum of product of corresponding values. [related: Excel SUMPRODUCT Formula – what is it and how to use it?]

**So, if you have values in A1:A10 and the corresponding weights in B1:B10,** you can use SUMPRODUCT like this to get weighted average.

`=SUMPRODUCT(A1:A10,B1:B10)`

However, the above method works only if B1:B10 contains weights in %s totaling to 100%.

In the above average salary example, we dont have weights. But we have count of values. This is when you can use a variation of SUMPRODUCT Formula like this:

`=SUMPRODUCT(A1:A10,B1:B10)/SUM(B1:B10)`

In this workbook, you can find 3 examples on how to to calculate weighted average in excel. Go ahead and download it to understand the formulas better.

Here is a video with Weighted Average formula explained. Please watch it below to learn more. Alternatively, head to my YouTube page to see the weighted averages in Excel video.

What do you use it for? What kind of challenges you face? Do you apply any tweaks to weighted average calculations? **Please share your ideas / tips using comments.**

- Syntax and Examples of Excel Average Formula
- Formula for Average of Top 5 values
- Calculating Moving Average in Excel
- Using SUBTOTAL formula and calculating averages
- Showing Averages in Pivot Tables

The post Weighted Average in Excel [Formulas] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>You may know that Excel has a *physical* limit of 1 million rows (well, its 1,048,576 rows). But that doesn't mean you can't analyze more than a million rows in Excel.

**The trick is to use Data Model.**

The post How can you analyze 1mn+ rows data – Excel Interview Question – 02 appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>As part of our **Excel Interview Questions series**, today let’s look at another interesting challenge. **How-to handle more than million rows in Excel? **

You may know that Excel has a *physical* limit of 1 million rows (well, its 1,048,576 rows). But that doesn’t mean you can’t analyze more than a million rows in Excel.

**The trick is to use Data Model.**

Introduced in Excel 2013, Excel Data Model allows you to store and analyze data without having to look at it all the time. Think of Data Model as a black box where you can store data and Excel can *quickly *provide answers to you.

Because Data Model is held in your computer memory rather than spreadsheet cells, it doesn’t have one million row limitation. You can store any volume of data in the model. The speed and performance of this just depends on your computer processor and memory.

Let’s say you have a large data-set that you want to load in to Excel.

If you don’t have something handy, here is a list of 18 million random numbers, split into 6 columns, 3 million rows.

Go to Data ribbon and click on “Get Data”. Point to the source where your data is (CSV file / SQL Query / SSAS Cube etc.)

In Power Query Editor, do any transformations if needed. Once you are ready to load, click on “Close & Load To..” button.

Tell Power Query that you want to make a connection, but load data to model.

Now, your data model is buzzing with more than million cells.

Go and insert a pivot table (Insert > Pivot Table)

Excel automatically picks Workbook Data Model. You can now see all the fields in your data and analyze by calculating totals / averages etc.

You can also build measures (thru Power Pivot, another powerful feature of Excel) too.

Once you have a data model setup, you can use,

**Data > Queries & Connections:**to view and adjust connection settings**Relationships:**to set up and manage relationships between multiple tables in your data model**Manage Data Model:**to manage the data model using Power Pivot

Of course, Excel is not built for analyzing such large volumes of data. So, if possible, you should try to analyze such data with tools like Power BI [What is Power BI?] This gives you more flexibility, processing power and options.

I made a video explaining the interview question, answer and a quick demo of Excel data model with 2 million rows. Check it out below or **on my YouTube Channel**.

- How to set up data model and connect multiple tables
- A quick but detailed introduction to Power Query
- Create measures in Excel Pivot Tables – Quick example
- Create data model in Excel – Support.Office.com
- How to use Data Model in Excel – Excelgorilla.com

**What about you? **Do you use the data model option to analyze large volumes of data? What other methods do you rely on? Please post your tips & ideas in the comments section.

The post How can you analyze 1mn+ rows data – Excel Interview Question – 02 appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>Say you have some text (sentence / phrase / keyword etc.) in a cell and you want to extract the *nth word. *Unfortunately Excel doesn't have SPLIT() formula. So we end up writing obscenely long array formulas or use gazillion helper columns.

Here is the super sneaky trick. Use FILTERXML() instead.

The post #awesome trick – Extract word by position using FILTERXML() appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>Say you have some text (sentence / phrase / keyword etc.) in a cell and you want to extract the *nth word. *Unfortunately Excel doesn’t have SPLIT() formula. So we end up writing obscenely long array formulas or use gazillion helper columns.

Here is the super sneaky trick. Use FILTERXML() instead.

See this example

Say you have a long sentence (or keyword phrase) in cell C3.

**Step 1: Convert this to valid XML**

This sounds complicated that it is. All you need to do is prefix, insert and suffix some tags. Like this:

="<DATA><A>"& SUBSTITUTE(C3, " ", "</A><A>") & "</A></DATA>"

This will turn C3 in to a valid XML block with each word as <A> node.

**Step 2: Use FILTERXML on this to extract words**

Now that we have valid XML, you can say =FILTERXML(C5, “\\DATA\A[3]”)

to extract the 3rd word from our XML converted sentence.

**Step 3: **There are no more steps. Enjoy FILTERXML.

**Bonus trick: ** Use [last()] to get the last word. For example, =FILTERXML(C5, “\\DATA\A[last()]”) will get you the last word from sentence.

I was so excited to learn about this that I recorded a video in my robe. Rated A (for awesomeness), Do check it out below or **on my YouTube page**.

**Click here to download the example file for this tip. **Play with FILTERXML to learn more.

If you want to learn how XML and Xpath work, check out these pages.

- Xpath cheatsheet – devhints
- Xpath examples – w3 Schools

**FILTERXML works in Excel 2013 or above.** But if you are using an older version of Excel or Excel for Mac, then you can’t rely on this method. Check out below two examples to learn other ways to split and extract words from sentences.

I almost never use FILTERXML unless I am calculating distance between points or calling a webservice. But this use of splitting text is fun. Big thanks to **GraH** for posting this in the forum.

**What about you? **Have you used FILTERXML for any other out of box situations? Please share in the comments. Pretty please with sprinkles of conditional formatting icons on top

The post #awesome trick – Extract word by position using FILTERXML() appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>