Formula Forensics No.004 – Fred’s Problem

Formula Forensics No.004 – Fred’s Problem:
About 6 months ago, Fred asked a question on the Chandoo.org Forums: I need idea on a simpler formula
In column A I have the total sum product of C to AU

Line one has all the names.

A2 = (B2*C2)+(D2*E2)+(F2*G2)+(H2*I2)+(J2*K2)+(L2*M2)+…+(AT2*AU2)

A3 = (B3*C3)+(D3*E3)+(F3*G3)+…+(AT3*AU3)
Is there is simpler way by line to do this without clicking each cell on line 2?
I tried Sumproduct but I think I have received a wrong answer during testing.

Hui offered a Sumproduct Formula as a solution
=SUMPRODUCT((B2:AT2) * MOD(COLUMN(B2:AT2) -1, 2), (C2:AU2) * MOD(COLUMN(C2:AU2), 2))
and then followed up with a simpler Sumproduct Formula a day later
=SUMPRODUCT(B2:AT2 * C2:AU2 * (MOD(COLUMN(B2:AT2), 2) =0))
Let’s take a look at this second solution.

Setup the Problem

Copy the numbers 10,20 into alternate Cells A2:U2 or download the example file here: Example File (Excel 97-2010)
Copy this formula into B6: =SUMPRODUCT(B2:U2 * C2:V2 * (MOD(COLUMN(B2:U2), 2)=0))

Pull The Formula Apart

Lets take a look inside this formula and see how it works.
=SUMPRODUCT(B2:U2*C2:V2*(MOD(COLUMN(B2:U2),2)=0))
We can see that in the above formula the main function used in the formula is a Sumproduct Function.
=SUMPRODUCT(B2:U2*C2:V2*(MOD(COLUMN(B2:U2),2)=0))
Within the Sumproduct function there are 3 arrays, which are multiplied together
Array 1: B2:U2
Array 2: C2:V2
Array 3: (MOD(COLUMN(B2:U2),2)=0)
What’s in these arrays?
Array 1 is simply the range from B2:U2
Array 2 is simply the range from C2:V2, note that it is offset from the first array by 1 Column.
This is so that the first value of the Second Array matches the first value of the First Array. That is they are both in position 1 within there respective arrays.
Array 3 is where all the action is.
Enter  =(MOD(COLUMN(B2:U2),2)=0) into a cell and press F9
Excel returns: ={TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE}
Lets look at what is happening here
=(MOD(COLUMN(B2:U2),2)=0)
Mod returns the remainder of the first value Column(B2:U2) after dividing it by the second value 2.
Column(B2:U2) returns the Column Number for each cell in the Range B2:U2.
Because this part of the formula is in a Sumproduct formula it is evaluated as an Array Formula and hence it does this for each cell in the range B2:U2, thus returning an Array as the answer.
We can see that if we enter =MOD(COLUMN(B2:U2),2) into a cell and evaluate it with F9
={0,1,0,1,0,1,0,1,0,1,0,1,0,1,0,1,0,1,0,1}
However in this case we want to convert this from an array of Numbers to an array of True/False
A simple addition of an =0 does the trick
=(MOD(COLUMN(B2:U2),2)=0)
Now causes the formula to return: ={TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE}
So How does this go together with the other 2 arrays?
We now have 3 arrays:
Array 1:  10,20,10,20,10,20,10,20,10,20,10,20,10,20,10,20,10,20,10
Array 2:  20,10,20,10,20,10,20,10,20,10,20,10,20,10,20,10,20,10,20
Array 3: TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE
When Multiplying arrays, Excel multiplies the first value of each array and then the second value of each array, etc, effectively doing
={10*20*True, 20*10*False , 10*20*True, 20*10*False , 10*20*True, 20*10*False , 10*20*True, 20*10*False , 10*20*True, 20*10*False , 10*20*True, 20*10*False , 10*20*True, 20*10*False , 10*20*True, 20*10*False , 10*20*True, 20*10*False , 10*20*True}
You can see above that when the 3 arrays are multiplied it will only be the Odd entries in Arrays 1 & 2 which are evaluated by the Sumproduct, as all the even entries are multiplied by False which is equivalent to Zero
So the above evaluates to
={200,0, 200,0, 200,0, 200,0, 200,0, 200,0, 200,0, 200,0, 200,0, 200}
Sumproduct then takes over and adds the values together returning, 2000, the correct answer.

Problem Extension

After solving the problem, Fred decided to add a column between each entry in the data set.
The solution is posted in the forum and is also shown in the example file.
You can work through that formula at your leisure, except to say that it is similar to the solution above.

Alternate Solution

Luke correctly pointed out that the data was poorly arranged and the solution would be much simpler had the data been more logically arranged.
However as an Excel practioner we are often called to solve other peoples dirty work.

HINTS

You will notice that in the solution of this problem I have done a few small things that make solving the problem easier.

Use Smaller Subsets of the Data.

Instead of putting numbers from Columns B to AU as Fred has I have used a set from Column B to U.
This way I can see all the data on one Excel screen without scrolling as well as the formula links and extents when pressing F2 on the cell containing the formula.

Use Simple Numbers

Use numbers that you can manually check. In this example I can easily, manually, check that the answer should be 2000.

Evaluate

Where ever possible, enter sections of a formula in a cell and evaluate its output:
=(MOD(COLUMN(B2:U2),2)=0) and press F9
={TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE}
See how easily we can check that this section is returning the correct values.

OTHER POSTS IN THIS SERIES:

You can learn more how to pull Excel Formulas apart in the following posts:
http://chandoo.org/wp/category/formula-forensics/
The link above, will show you all the posts in this series

WHAT FORMULAS WOULD YOU LIKE EXAMINED?

If you have any formulas you would like explained please feel free to leave a post here or send me an email:
If the formula is already on Chandoo.org or Chandoo.org/Forums, simply send the link to the post and a Comment number if appropriate.
If sending emails please attach an Excel file with the formula and data

Maintenance Work Complete

Maintenance Work Complete:
Maintenance on the 18 month old, Data Tables, Monte-Carlo Simulations and Fractals in Excel – A Comprehensive Guide has been completed.
This was routine maintenance and included:

• Explanatory notes added/improved
• Added a VBA section on Data Tables
• Links upgraded
• Picture Upgrades
• Sample file improvements
• Spelling and Grammar improvements
• Tagging improvements

Please put any comments against the original post: http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/
Hui…

Christmas Gift Shopping List Template – Set budget, track your gifts using Excel

Christmas Gift Shopping List Template – Set budget, track your gifts using Excel:
Last year, Steven shared a beautiful Christmas Gift List template with all of us. It is packed with lots of Excel goodness. Just a few days ago, he emailed me another copy of his file with some improvements. So if you are planning for Christmas shopping and want a handy tracker, you don’t want to miss this.

How does this template work?

This template feels magical. To begin with,

Zebra lines thru Conditional Formatting

Alternative rows of the template are shaded in dull gray color so that the template is easy to use. And this is achieved by Conditional Formatting & SUBTOTAL formula. A very ingenious use of SUBTOTAL formula so that the zebra lines preserve even after filtering data.

Custom Cell Formatting to Show Budget vs. Actual Variance

Another simple yet elegant solution. To highlight the variance between budgeted & actual gift value, Steven used Custom Cell Formatting.

Awesome Formulas to Summarize the Gift List

The top right area of Steven’s shopping list shows a clear summary of your Christmas shopping list. Each of the values in the summary are calculated by a clever formula. For example, the formula to show how many gifts are over the budget vs. how many are on or under the budget is an intricately woven SUMPRODUCT formula with SUBTOTAL, OFFSET & ROW components. Go ahead and examine these formulas to learn more.

You can filter the list and analyze by segment

The beauty of this template is that you can filter the list and analyze by segment. For example, you can filter all the gifts you are giving to friends and see whether you are with-in budget in that segment, the progress of gift selection & purchase etc. Very useful.

Download Christmas Gift Shopping List Template

Click here to download the template and use it.
Go ahead and Enjoy your Christmas shopping.

Thanks to Steven

for sharing a beautiful & awesome template with all of us.

How do you like this template?

I really loved the simplicity and elegance of this template. It is easy to use, packed with lots of details and fun to poke around.
What about you? Do you like this template? How do you organize your Christmas spending? Do you use Excel, some other tools or rely on your gut feel? Please share using comments.

More Templates on Christmas & Thanksgiving

We, at chandoo.org celebrate holiday season by sharing useful templates, tips & ideas with you all. Here is a collection of holiday stuff for you:

• New year resolution template
• Perpetual Calendar template
• Personal Expense Trackers (7 templates)
• Find out the date of Thanksgiving & other holidays using Excel
• When does thanksgiving date occur on same date again? [Challenge]

Add Data to Charts with Copy Paste [Quick Tip]

Add Data to Charts with Copy Paste [Quick Tip]:
So how did your weekend go?
I did a bit of gardening, painted our car shed, played badminton (I am learning), attended 60th birthday of a close friend’s dad. Pretty hectic, but fun as usual.
To start this week, let me share a simple but fun way to add data to charts.
Lets say you have a chart that depicts Annual sales for last few years. And you want to add the data of Profits (or Expenses) to this chart. Here is a dead-simple way to do it.

1. Copy the profit data by selecting it and pressing CTRL+C
2. Select the chart
3. Paste by pressing CTRL+V
4. That is all!

See the demo alongside to understand how this works.

Bonus Tips:

1. While pasting, if you go for Paste Special (CTRL+ALT+V or ALT+E S) you can tell whether the data should be added as a new series or new points and several other things.
2. To remove a series of data from a chart, just select the series and hit DEL key.
3. To extend a series (ie add new points to it), select the series. Now you will see that Excel has highlighted the range of cells corresponding to that series. Just point your mouse at the bottom-right corner and resize the range to add new points to the chart.

More Quick Tips on Excel Charting

There are a ton of things you can do in Excel with a click of mouse or press of few keys. Whenever we learn something that is simple yet very useful, we share it as a quick tip. Browse thru these to learn more on Excel Charting.

• Highlight points in line charts & scatter plots
• Display Alert Symbols in Dashboards using Conditional Formatting
• Change Data Labels in Charts to whatever you want
• Use Paste Special to Speed up Chart Formatting
• Hide “0″ in Excel Chart’s Axis
• Create a combination chart, add secondary axis
• More Quick Tips & Chart Formatting Tips

Formula Forensics No. 003 – Lukes Reward

Formula Forensics No. 003 – Lukes Reward:
Hello everyone. My name is Luke M, and I’ve been coming to Chandoo’s site for about a year now.
I love solving problems, and helping people out on the forums.
Due partly to Hui’s challenge about submitting articles, and 3G’s comment the other day about this particular formula, I decided to try my hand at writing an article.
I Hope it helps explain some cool Excel tricks that I like to use.

The Problem

Often, I see a request from an individual asking for a formula that will be able to generate a list based off of some criteria, with no spaces/blanks.
As Chandoo & Hui have often pointed out, there’s not much Excel can’t do if you know the right functions to use.
Today, we’ll take a look at how we can accomplish this particular task.

Suppose I want to be able to generate a list of all the Vegetables.
In cell E2, I put this array formula:
=IF(COUNTIF(A:A,$D$2) < ROWS($E$2:E2), “”,  INDEX(B:B,  SMALL( IF($A$2:$A$10 =$D$2, ROW( $A$2:$A$10)), ROW(A1))))
Remember, array formulas need to be confirmed using Ctrl+Shift+Enter, and will have curly brackets { } around the formula if done correctly.
I then copy the cell downward as far as I think will ever be necessary to display all the records (i.e. E7).

Lets Look Inside

Let’s take a closer look at how the formula works.

Front Half

First, let’s look at the IF function’s logic check.
=IF(COUNTIF(A:A,$D$2)<ROWS($E$2:E2),”", INDEX(B:B, SMALL( IF($A$2:$A$10 = $D$2, ROW($A$2:$A$10)), ROW(A1))))
We’re using the COUNTIF function to determine the total number of records that meet our criteria. We’re then comparing this to a ROWS function. The ROWS function simply returns the number of rows given in the argument. Note that the first part of the range callout uses an absolute reference and will not change, while the latter part is relative and will change as the formula is copied down. Thus, in the first cell, the ROWS function evaluates to 1. The next cell, it will evaluate to 2, then 3, and so on. So, the IF statement is checking to see if the number of records returned so far (i.e., formula used) is greater than the total number of possible records. If this is true, return a blank (i.e., “”).

Back Half

The latter half of the formula is where things get tricky.
For this part, let’s work our way from the inside out.
We start off with another IF function:
INDEX(B:B,SMALL(IF($A$2:$A$10=$D$2, ROW($B$2:$B$10)), ROW(A1))))
This section compares A2:A10 with our criteria given in cell D2. So, the array if A2:A10 starts off looking like this:
{Fruit, Fruit, Vegetable, Vegetable, Fruit, Vegetable, “”, “”, “”}
When we compare it with the criteria, it becomes this:
{False, False, True, True, False, True, False, False, False}
Looking at the return values in our IF function, we see that only a True result is stated, the ROW.

INDEX(B:B,SMALL(IF($A$2:$A$10=$D$2, ROW($A$2:$A$10)), ROW(A1))))
So, each True value from the array above will be replaced with the corresponding Row value.
This causes the array to become this:
{False, False, 4, 5, False, 7, False, False, False}
Now that we have a nice array with some numbers in it, this gets fed into the SMALL function.
INDEX(B:B,SMALL(IF($A$2:$A$10=$D$2, ROW($A$2:$A$10)), ROW(A1))))

The ROW function at the end will serve as a type of counter.
In E2, where we initially place the formula, this will evaluate to 1, thus telling the SMALL function to return the 1^st smallest number.
In E3, it will evaluate to 2, and the SMALL function will return the 2^nd smallest number, and so.
So, taking the 1^st smallest number from our array, we get the number 4.
We then take this to the INDEX function
INDEX(B:B,SMALL(IF($A$2:$A$10=$D$2, ROW($A$2:$A$10)), ROW(A1))))
Note that we need to callout the entire column, since we are plugging in row numbers.
The 4^th row in column B leads us to the value “Broccoli”.
The next formula will return the 5^th row, “Spinach”.
The 3^rd formula will return the 7^th row, “Peas”.

This method can be adapted for use with multiple criteria. We would just need to expand the IF function logic checks so that only the correct rows are returned.

PS. If it gets too confusing, the first part of the formula can be omitted.
=IF(COUNTIF(A:A,$D$2)<ROWS($E$2:E2),”", INDEX(B:B, SMALL( IF($A$2:$A$10 = $D$2, ROW($A$2:$A$10)), ROW(A1))))
It is just there to hide any unwanted #NUM errors after all the pertinent records have been displayed.

DOWNLOAD

You can download a copy of the above file and follow along, Download Here.

OTHER POSTS IN THIS SERIES

You can learn more about how to pull Excel Formulas apart in the following posts
Formula Forensic 001 – Tarun’s Problem
Formula Forensic 002 – Joyce’s Question

THANK-YOU and a CHALLENGE

Firstly a Congratulations to Luke M on taking up the challenge and on your First Post at Chandoo.org.
Thank-you for explaining to us all how this formula, which has appeared a number of times on the Chandoo.org Forums, works.
The contents of the Post are published as Luke submitted it with only minor formatting changes.

My Challenge to you is this:
If you have a neat formula that you would like to share and explain, try putting pen to paper and draft up a Post as Luke Did above.
If you have a formula that you would like explained but don’t want to write a post also send it in to Chandoo or Hui.
Send it to Chandoo or Hui.

Making Small Multiples in Excel [Charting Technique]

Making Small Multiples in Excel [Charting Technique]:
My friend Paresh writes excellent commentary on charts on his blog Visual Quest. Last week he gave a home work, asking his readers to recreate the small multiples chart shown below.

I found this quite interesting. Small multiples, also called as panel charts, are a powerful way to depict multidimensional data and bring out insights. They are easy to read too.
So, today, let us learn how to create such charts using Excel.

Step 1: Arrange your data

Almost any chart or visualization worth its salt must begin with proper arrangement of data. Since I could not get the data for the unemployment chart, I made up a few numbers for a fictional Confectionery Company. The data is shown below.

So, we have the data for years 2007 thru 2010, for the regions – South, West & North and for the product lines – Chocolates & Biscuits

Step 2: Select Products for one region & make an area chart

This is simple. Just select data for chocolates & biscuits for one region and make an area chart. You should have something like this:

Step 3: Resize the area chart & format it

Now, we need to make this area chart closer to what we want.

1. Select the bottom area series and fill it with white color.
2. Now resize the chart so that we can fit 3 of them in the area you got.

Step 4: Add same data to the chart

Now, select the same region data, press CTRL+C to copy it. Select the chart and paste it by pressing CTRL+V. See below demo to understand how to do this.

We are doing this because we want to have lines with markers on our chart. But the area chart lines cannot show markers. So we are going to add the same data one more time, but this time format it to be shown as a line.

Step 5: Select the new series and format them as line charts

Select each of the new area series and format as line chart with markers.
You should have something like this at the end.

Step 6: Format the chart

This is where you unleash the creativity. In order to match the look of NYTimes chart, here is what you can do.

• Set the fill color between lines to something dull.
• Format 2 lines in distinct colors.
• Format gridlines & axis lines to something dull.
• Set axis maximum to 25 (as all charts in small-multiples should have same axis settings)
• Set axis major unit to 5.

Step 7: Repeat this for other regions

Now, just copy and paste this chart a couple of times. Just adjust the data source so that we have new charts using this technique.

Note: Learn how you can add descriptive labels to charts.
That is all. You just made a small multiples chart that looks awesome. Congratulations.

Download Small Multiples Example Workbook

Click here to download the example workbook and play with it. You can see the steps for making one of the charts in the workbook as well.

Do you use Small Multiples or Panel Charts?

I really love to use small multiples or panel charts whenever I am analyzing data or presenting results of the same. They offer excellent value per pixel. That said, they take some time to construct. Also, you must tweak axis settings and plot area to get the perfect result. That is why I prefer the in-cell variation of these charts. They are quick to setup and easy to wow (for more on these techniques, see below).
What about you? Do you use Small Multiples or Panel charts? How do you find them? Please share using comments.

Interested to learn more? Read these

As you can guess, small multiples is one of my favorite ways to explore and present data. So we have written quite a few articles explaining this technique. Read these to learn more.

• Introduction to Panel Charts. What are they and how to create them using Excel?
• Facebook Privacy Policies – Visualized in a panel chart
• In-cell Panel Chart to visualize Survey Data
• In-cell Dot Plot to visualize Survey Data