How many values are common in 2 lists? [homework]

How many values are common in 2 lists? [homework]:
Here is a formula challenge for you.

Lets say we have 2 lists of values in A1:A10 & B1:B10

Now, how do you find the number of common values in both lists?

We just want the count, not list of common values it self.

Go ahead and figure out the formula and post your answers here.
Bonus challenges:

1. Write the formula if 2 lists have non-numeric values (text for example) ?
2. Write the formula if 2 lists are not of same size (A1:A10 and B1:B15 for example) ?
3. Write the formula if we have 3 lists (all same size, numeric values) ?

Go ahead and share your answers. I am eager to see what you will come up with.
PS: For some inspiration, check out this forum discussion.
PPS: If you just want to highlight the common values, see this.

Interactive Pivot Table Calendar & Chart in Excel!

Interactive Pivot Table Calendar & Chart in Excel!:
Can we make a calendar using Pivot Tables?!?
Of course we can. Today let us learn a simple technique to create calendar style reports using Pivot tables.

Thanks to Rob for inspiration

Before making any progress, let me thank Rob from PowerPivotPro for the inspiration. Recently he wrote an article explaining how to use PowerPivot & DAX formulas to create calendar charts in Excel. I applied similar technique to Pivot tables.

Demo of Pivot Calendar

See a quick demo of pivot calendar chart before learning how to do this.

Creating a Pivot Table Calendar

Step 1: Set up an entire year of dates in a list

Lets assume, we want to make the calendar for year 2012. So write that in a cell (G3). Now, in a range of 366 cells, generate all the dates for the year (2012) using simple formulas.

• First date will be =DATE(G3,1,1)
• Next 365 dates will be previous date + 1

Step 2: Calculate Day, Month, Year and Weekday

Using DAY(), MONTH(), YEAR(), WEEKDAY() calculate the day, month, year and weekday for each of the 366 days.

Step 3: Determine the week number in a month

Now comes the tricky part. We need to find out which row each date should be displayed. First take a look at this illustration.

The logic for calculating row numbers is very simple:

• First day of a month is always in row number 1.
• If a day is not Sunday, we just use previous row number
• On Sundays, we just increment the previous row number and use it.

All of this can be expressed in a simple IF formula =IF(D7=D6,IF(F7=1,G6+1,G6),1)

• D7 contains this month, D6 is previous day’s month
• F7 contains weekday, will be 1 for Sunday and 7 for Saturday
• G6 contains previous row number (weeknum)

Step 4: Dealing with Leap years

So far we are good, except for a minor glitch. Certain years have 366 days (for example 2012) while others dont. That means, depending the year, we need to either use 365 rows or 366 rows of our data while generating the pivot report. To do this, we create a named range tblDates that refers to below formula:
=IF(Calcs!$D$3,Calcs!$B$5:$G$371,Calcs!$B$5:$G$370)
Note: D3 is TRUE when an year is leap year.

Step 5: Create pivot table that shows calendar

Now, we need to create a pivot table from the range tblDates.
Set up your pivot table like this:

Step 6: Add a slicer

To enable users to select a particular month interactively, just add a slicer on months. For this,

1. Select any cell in the pivot table and go to Options Ribbon > Insert Slicer
2. Select Month as field to insert a slicer.
3. Adjust slicer properties to show items in 6 columns (Slicer Options Ribbon > Columns)
4. Done!

At this point, you can interactively select a month & see the corresponding calendar.
Related: More examples on Slicers

Further Enhancements

Now that the basic Pivot Calendar is ready, try these ideas:

• Use a spin button / slider control to interactively adjust the year. Remember, when you do this, you need to refresh the pivot table in background using a simple macro.
• Adjust week start to Monday: Likewise, you can modify your formulas to adjust weekstart to Monday or any other day you fancy.

Using Pivot Calendar as a Chart

Of course, having a mere pivot calendar is not much fun. But you can apply this idea to create a calendar chart. See this:

Calendar Chart Demo:

How to create this Calendar chart?

To keep things simple, lets understand how to create this chart with just one metric – Employee productivity.

• Once the pivot calendar is ready, we add extra rows between each line in the calendar.
  
  
• Now, lets say, we have our employee productivity details listed by date in a table.
• Then, using lookup formulas, we fetch productivity for each day in the cell below.
• Once all the values are fetched, just select all these cells and add conditional formatting > color scale to them.
• Format the color scale settings so that you get desired colors.
• And you are done!

More on Conditional Formatting

Video Explaining Pivot Calendar & Chart

Like this concept? Watch below video to understand how the whole thing is made.
[watch this video on our youtube channel]

Download Pivot Calendar Template

Click here to download pivot calendar & calendar chart templates. Play with them. Plug your own values and see what happens.
PS: You need Excel 2010 to view this file. Please enable macros to get full effect.

Do you like Pivot Calendar Idea?

I am very excited to try this out in a client project sometime soon. I think a set up like this can be used when analyzing monthly data like employee attendance, vacations, productivity, shipments, meeting schedules, project milestones etc. Since such data is represented in calendar format in real life, your audience would find calendar metaphor easy to understand. That said, any data like KPI trends, sales, visits, calls etc. should always be represented as a line /bar charts rather than calendar charts.This way, we can spot trends quickly and understand data better.
What about you? Do you like this idea? Are you planning to use a pivot calendar / calendar chart sometime in future? Please share your thoughts using comments.

Calendars & Similar ideas:

Please go thru below links to learn more about calendars & visualizing data:

• Project Milestone Chart template
• Perpetual Calendar in Excel – Template
• Employee Shift Tracker Template
• Visualizing Data Around the clock – Charting Technique
• More on Pivot Tables, Slicers & Conditional Formatting

A Spreadsheet walks in to a bar … [open mic]

A Spreadsheet walks in to a bar … [open mic]:
Lets have some fun this Friday.
Post your favorite Spreadsheet jokes using comments. Have a big smile.
I will start:
1. A pivot table walks in to a bar and orders a beer. It says, “Put me in the same tab, will ya?”

2. A slicer and pivot chart walk in to a bar. The bartender says, “look at those bast*rds, walking around with out a pivot!”.

3. Once Excel, Access and Windows were bragging to each other. Excel says, “I once crashed so hard, it took 5 minutes to recover”. Access says, “Oh thats nothing. I once crashed and took down an entire database. It took them 30 minutes to recover”. Windows doesn’t say anything. Excel pokes him in the arm asks “what about you?”, Windows jolts & replies, “Sorry, what did you say? I just crashed again.”

Now, your turn…

Post a spreadsheet related joke or one liner. Click here to post.
PS: For some spreadsheet jokes check out Genie in a lamp, Spreadsheet Jokes, Comments in DDoE and Dilbert Spreadsheet Cartoons.

Formula Forensics No. 028 – It’s Just a Jump to the Left

Formula Forensics No. 028 – It’s Just a Jump to the Left:
In 2010 Chandoo wrote a post about options to perform a VLookup to the left of the Reference Column
Mike one of Chandoo.org’s astute readers had noticed a post by DaddyLongLegs over at the Excel Forum which describes a technique for using VLookup to look to the left of your reference column. Mike made a comment to the effect at Mike’s Comment
Today at Formula Forensics we have a look at this technique and see why it works as well as looking at some extensions using the same idea.
As always at Formula Forensics you can follow along with a sample file here: Download 97-2013

VLOOKUP

Lets start with understanding what the Excel VLookup() function does.
VLookup uses the syntax:

So VLookup looks up a Lookup_Value in the first Column of the Table_Array and returns a matching value from the same position from another column in position Col_Index_No of the Table_Array.
Of note here is that the Table_Array is specified for the lookup area, not a Range.
A Table_Array can be a Range as specified in the Syntax above eg: A2:D8 or it can be a Named Formula or it can be a formula that returns a Range as a solution.
We can use this to trick Excel into accepting an Array which has Column 1 to the right of Column 2. Effectively meaning we are returning a value from the left of Column 1.

Mike’s Solution

Lets look at Chandoo’s first Question: Which person made sales = 1088?
Mike supplied the solution:
=VLOOKUP(1088,CHOOSE({2,1},$B$5:$B$17,$D$5:$D$17),2,0)
=John
Which we can manually see is correct and the answer is in fact to the left of the Lookup value of 1088.

Mikes formula: =VLOOKUP(1088, CHOOSE({2,1}, $B$5:$B$17, $D$5:$D$17), 2, 0)
Is a standard VLookup with:
Lookup_Value: 1088
Table_Array: CHOOSE({2,1}, $B$5:$B$17, $D$5:$D$17)
Col_Index_No: 2
Range_Lookup: 0
So we can read this as lookup the value 1088 in Column 1 of the Table_Array and return the equivalent value from Column 2 of the Table_Array.
But what’s this Table_Array of: CHOOSE({2,1}, $B$5:$B$17, $D$5:$D$17) doing?
In a Blank cell say I19 enter: =CHOOSE({2,1}, $B$5:$B$17, $D$5:$D$17) press F9 not Enter
Excel responds with: ={1592,”Joseph”;1088,”John”;1680,”Josh”;2133,”Jamie”;1610,”Jackie”;1540,”Johnson”;1316,”Jonathan”;1799,”Jagjit”;1624,”Jairam”;726,”Jessy”;2277,”Javed”;714,”Jimmy”;2682,”Juno”}

We can see this is an array of the elements from Column B and Column D
The 1592 is the first value in Column D, and Joseph is the first value in Column B
Then 1088 is the second value in Column D and John is the second value in Column B
Then 1680 is the third value in Column D and Josh is the third value in Column B, etc
You can see that Excel uses the “,” to separate entries in different columns in the same row and then uses “;” to separate the different rows
So the Formula =CHOOSE({2,1}, $B$5:$B$17, $D$5:$D$17)
Has setup an array where Column 1 is Range D5:D17 and Column 2 is Range B5:B17

Back to VLookup

VLookup looks up the Lookup_Value from Column 1 of the Table array in this case we saw above that this is the Range: $D$5:$D$17
Vlookup finds the position of the Lookup value, 1088, in our case is position No 2. And the goes to Column 2, which is $B$5:$B$17 and returns the value from position 2 which is John.

Why has Mike Used {2,1} ?

Why has Mike Used {2,1} ?
As it turns out it doesn’t matter what order the array elements are listed as long as the Ranges listed in the Choose function match the array order
If Mike had used {1,2} instead he would be still able to rearrange the formula to make it work
=VLOOKUP(1088,CHOOSE({1,2},$D$5:$D$17, $B$5:$B$17),2,0)
Noting that Choose position 1 is still D5:D17 and Choose position 2 is still B5:B17

You can check that out for yourself at Cell I21

Extending this Technique

You can add any number of ranges of data to the Vlookup function by simply extending the Choose Function, ensuring that the Choose Array ranges matches the Ranges order in the Choose function.
So the following function will allow us to look up a value from Column D (Column 1) and return values from either Column B or C (Columns 2 & 3 respectively) by simply changing the Column_Index_No 3
=VLOOKUP(1088,CHOOSE({1,2,3},$D$5:$D$17,$B$5:$B$17,$C5:C17),3,0)
You can see here that Both Lookup Columns are to the left of the Lookup Column.
There are a number of such samples in the Extension Questions and Solutions section in the example file.

Download

You can download a copy of the above file and follow along, Download Here – Excel 97-2013.

Formula Forensics “The Series”

This is the 28th post in the Formula Forensics series.
You can learn more about how to pull Excel Formulas apart in the following posts: Formula Forensic Series

Formula Forensics Needs Your Help

I need more ideas for future Formula Forensics posts and so I need your help.
If you have a neat formula that you would like to share like above, try putting pen to paper and draft up a Post like above or;
If you have a formula that you would like explained, but don’t want to write a post, send it to Hui or Chandoo.

Show Decimal Points if needed [Quick Tip]

Show Decimal Points if needed [Quick Tip]:
Sometimes you want to turnoff decimal points if the value after point is 0. Mireya, Chandoo.org member had one such situation. She writes:

I am a complete beginner in excel, how can I keep the zeros when I am working with decimals and remove them when are not required, ie
Thanks for your kind help.

Easy way: Use General Formatting

The default cell formatting in Excel is General. When you set a cell’s formatting to General, you are telling Excel,
Don’t bother me. Just figure it out.

And being a good Samaritan, Excel shows decimal point if there is something after it, else omits it.
See the demo aside to understand this.

What if your numbers are results of a calculation?

It doesn’t matter. General formatting takes good care of the cells. It shows and hides decimal point depending on the result of your formulas.

What if you want something fancy like accounting format, but turn off decimal values

Now you are talking. The General Formatting option shows numbers as typed (or calculated). So 124578395 would look like 124578395 instead of $ 12,45,78,395.
So how do you show $1,245 and $1,246.34?
Aside: You should always show decimal points if some values have them and others don’t. The below technique is useful when data is a result of calculation. For example: In a dynamic KPI report, for certain KPIs you may want to show decimal points, and omit for others.

To show decimal point if there is something after it

Just follow below steps:

1. Select the cell(s) where you want this formatting.
2. Go to Conditional Formatting > New rule from home ribbon.
3. Select rule type as “Use a formula…”
4. Check if there is a value after decimal point using a formula like =Mod(A1,1)>0
5. Click the format button
6. Go to “Number” tab and Apply formatting with 2 decimal places.
7. Click OK & You are done!

Now, if the cell has a decimal value, it shows, otherwise the decimal point is omitted.
Related: Conditional formatting Basics

Do you deal with such situations when formatting numbers?

Often when making reports (or dashboards), I have a cell where any data can go, based on user selection. In such cases, I use conditional formatting to define how it looks based on the data. Sometimes, I also use TEXT formula to format the data. This is more suitable when data is displayed in a text box rather than a cell.
What about you? Do you face situations like this? How often you rely on General formatting? Please share your experience and tips using comments.

More on Number formatting in Excel

Understanding how Excel formats numbers (and other values) can save you lots of time when you are designing dashboards, reports or workbooks that need to presented. Check out below articles to get few more tips.

• Introduction to number formatting in Excel and 10 tips
• Preserving leading zeros in a number using formatting
• Display decimals only if the value is less than 1
• How to hide “0″ in chart axis labels?
• How to hide cell’s contents using format codes?
• Adding colors to your chart labels using custom format
• Showing Indian Currency Format in Excel [and more on this]
• Develop & understand custom number formats
• Chart Axis formatting – Part 1 & Part 2

Excel Links – Going to Togo Edition

Excel Links – Going to Togo Edition:
After a long time, I am writing one more Excel links post. First let me share a quick personal update.
I am invited to Lome, Togo to spend a day at Ecobank – CFO conference in 3rd week of September. I will be conducting a one day masterclass on Advanced Excel & Dashboard Reporting. I am excited about this trip as I have never been to any African country. I will post some pics and tell you how it went once I am done with the conference.
Moving on to Excel Links for today,
Calendar Chart using PowerPivot, DAX and pixie dust
Rob at PowerPivotPro created this awesome calendar chart using powerpivot, DAX formulas, conditional formatting and probably lots of coffee. It is a clever idea. I will be shamelessly adopting it for some of my reports
Multiple Criteria Lookups – INDEX or SUMPRODUCT?
Often we need to lookup a value that meets multiple criteria. Back when we did that lookup week, we had a round up of various techniques for doing mulch-criteria lookups (index+match, sumproduct, sumifs etc.). Doug at YourSumBuddy discusses more about 2 important approaches – INDEX+MATCH vs. INDEX+SUMPRODUCT
How to create a Panel Chart
Debra shares a simple yet wonderful recipe for cooking panel charts. Go check it out and impress someone today.
Checklist in Excel that works
Robert shares an excel check list template that use a bit of VBA magic to automatically check / uncheck items upon clicking the cell. Very nifty.
Using Arrows in percentage format
Mike at Bacon bits shows us how to get up and down arrow symbols in cells that have percentage change, using custom cell formatting codes. The best thing is the same formatting is applied to status bar as well, pretty cool.
That is all for now.
Got any Excel links that you want to share with us?
I am always looking for new websites and resources to learn Excel & Visualization. So if you came across something that is awesome and want to share it with us, send it to me at chandoo.d @ gmail.com or drop a comment here. Thanks in advance.