by ·5 Comments

7 Ways to conditionally calculate sum of values in Excel.


Conditional SUM functions in Excel

         Excel offers different ways to accomplish the same task. This is especially evident in the case of using Excel functions, where we can simply choose the one that offers the best solution, or more realistically, the one that we are more comfortable using. As an example, let’s solve the following scenario: We are offering online Excel courses both: on our internal website, as well as on Udemy’s platform. Udemy charges us 50% fee on all course sales, and also offers promotional rates to increase our volume. As a result, we are selling the same content at different prices. Looking at Thanksgiving week sales performance, let’s calculate our total Net Sales for all of Udemy transactions (highlighted). Let’s use different Excel functions to perform calculation required.

Continue reading

by ·0 Comments

Using SUBTOTAL Excel function

SUBTOTAL Excel Function

         Let’s talk about benefits of using SUBTOTAL Excel function. This function offers too many features to be overlooked. On the surface, it appears that this function does nothing more than its name suggests: calculates a subtotal of your references: constants, individual cells, or ranges of cells. The reality is that this versatile function calculates 11 different subtotals, (think 11 functions in one): AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR, VARP. This function works similarly to your typical SUM or AVERAGE, where you need to reference your data range to be used in the calculation, however, you also need to specify function type. As you are typing the name of this function in your formula bar, Excel will automatically display the list of possible function types, or you can look up function types from the table below. The function’s syntax is very simple: = SUBTOTAL(FUNCTION_TYPE, RANGE)

Continue reading

by ·0 Comments

VLOOKUP() vs. INDEX(MATCH()), which should you use?!

VLOOKUP() vs. INDEX(MATCH())

         I recently read an excellent article, where its Excel MVP-author makes a very compelling, albeit biased argument towards using INDEX(MATCH()) function combo, instead of taking the VLOOKUP() route. That article jokingly proclaimed the end of the VLOOKUP vs. INDEX(MATCH()) debate. Interestingly enough, some of the heavy-weight Excel MVPs and expert users weighed in on this issue, in essence continuing the debate. One of the commentators, published an equally brilliant rebuttal, skewing the victory towards the VLOOKUP function. Being a biased VLOOKUP proponent myself, I concur with his conclusion completely. In addition, I’d like to throw in a crazy idea of using SQL as a better, faster, more flexible, and easier solution, comparing to either: VLOOKUP(), or INDEX(MATCH()).

Popularity

         I believe there is no disagreement here: VLOOKUP enjoys tremendous popularity. People have different opinions whether this popularity is justified. I belong to the “ease of use” and “ease of learning” VLOOKUP camp: two of many factors that help us explain high adoption rate of this function among all Excel users.
Continue reading

by ·0 Comments

Solving ModelOff Data Analysis problem from the second round of 2013 competition.

Solution for ModelOff 2013 Data Analysis problem


         As some of you might know, Model Off 2014, championship kicked off last Saturday, October 25th. With the first round of this modeling competition behind us, I thought that this might be a good time to tackle one of their older problems. I might be biased here, but starting with their Data Analysis problem made a perfect sense to me. Luckily enough, this could arguably be one of the easiest problems they have ever presented. You can download both: Questions/Answers worksheet and Excel workbook with source data for this problem directly from their website.

         As usual, I will not pretend to have the best possible, optimal solution, I simply offer one which works. Since in the real world, we are not constrained by challenging time restrictions, imposed on ModelOff contestants, I was not concerned about the most efficient solution, but rather the one that is more presentable. As an example, my solution heavily relies on Top Ten filtering feature available for PivotTables, this might seem like an extra step comparing to simply presenting all records and then sorting them in order of preference. In my humble opinion this is a better way to present top 1/3/5/n results, rather than looking at extra records of data.

Continue reading