Formulas: Sumifs, and Index & Match

When creating financial or other data models in excel, formulas as provided by excel functionality can become indefensible in creating an efficient model that is potentially reusable. In a prior internship, I exhausted the use of the (1) 'SUMIFS', (2) 'INDEX', and 'MATCH' formulas (index and match are used in conjunction with eachother).
(Note, this blog is not a tutorial but rather to provide examples for each formula mentioned.

SUMIFS
This formula works great in summing an array of numbers based on defined conditions. An easy example would be if you had financial output that was displayed on a month by month basis. But say that you wanted to summarize this detailed information into quarterly or yearly numbers. Use of this formula does not need to some a set of numbers; it can be used to single out a particular number based on a defined selection criteria. I've heard it referred to as 'poor mans SQL'.

Here's an example of SUMIFS and changing monthly into quarterly data. Cell G6 shows the formula used in cells G3:G6.

INDEX & MATCH
This combination can be used to select data that has a X and Y axis and linearize it. I get the most use of this formula when linearizing government data which is presented in an Year by Month format.
The INDEX syntax is: INDEX(array, row_num, [column_num])

• array is the selection area (not including the columns and row names such as year and month)
• row-num is the row to pull the selection from
• column_num is the column to pull the selection from

You can think of this formula as creating a cross-hair on the exact cell that you want to pull a value from. 

MATCH is used in the row_num and column_num to dynamically select the correct row and column. Otherwise, a number (position) has to be manually input. 

The MATCH syntax is: MATCH(lookup_value, lookup_array, [match_type])

• lookup_value is the value that you want to match in either column or the row, whichever you are targeting in the INDEX formula
• lookup_array is either the column or row names such as the years as the rows or the months (or quarters) as the headers. 

See below for an example:

Hope this helps you think of ways to better your financial models and cut down on time fooling with excel!

Seaside, Florida in the Truman Show

Recently we watched a video in Dr. Peterson's class about Seaside, Florida. It is a unique master-planned community based on New Urbanism concepts. Seaside exists on 80-acres of Florida's Gulf Coast. It has become an example of New Urbanist development throughout the planning and architecture disciplines called the poster child of New Urbanism by some. Features include walkability, sustainable development practices, anti-sprawl disposition, compactness, landscape designed as multi-use (ex. amphitheater/retention-pond). 

When I think about Seaside, I think of old-school, traditional, comfortable, and simple living. Many features are desirable such as the walkability and access to commercial and entertainment, but the community still lacks the density and diversity that I would like out of a big city. But that's my taste. To each their own! 

The real point of this blog is, interestingly enough, as we were watching the clip in Peterson's class, it dawned on me that this community was very similar to what I remember from the 1998 movie the Truman Show starring Jim Carrey. I checked it out, and sure enough it was the same community used as the setting! The director is said to have desired a location that would create a dreamlike quality in a real life place. The movie definitely portrays the community as simple and traditional as previously stated, but it would be interesting to experience the ambiance first hand to see if the portrayal is actually the perceived vibe received in a 'real-life'.