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

Coding your first JavaScript application – BMI Calculator.

Error: BMI Calculator could not be displayed, please upgrade your browser.

         We already created a BMI (Body Mass Index) Calculator using Excel VBA last month. How difficult would it be to rewrite that calculator in JavaScript to embed it on any webpage for people to use? Not that difficult , thought I, remembering happy days of browsing the original, NOT corporate owned, HTML Goodies website in search of the coolest JS mouse-over effects, some 15 years ago. You can see the working calculator above of this post, but I would be lying saying that its creation was painless. Surely, “World’s Largest Web Development Site” provides more than enough reference material for all elements required: HTML and JavaScript, as well as optional, but handy styling language – CSS. However, tweaking my code and making it work, was a very time-consuming process. Looking at my final product and comparing it to top 10 Google search results for BMI Calculator makes it all worthwhile…. Not only it incorporates more enjoyable design, it is also equipped with error handling logic.
Continue reading

by ·0 Comments






Solving ModelOff Data Analysis problem using Microsoft Access SQL.

MS Access Solution for ModelOff 2013 Data Analysis problem

         Last week we solved ModelOff’s Data Analysis problem from their 2013 championship. Since the second round of 2014 Model Off competition takes place this Saturday, November 8th, let’s pay respect to the data superheroes making it thus far. Our previous ModelOff solution involved using PivotTable feature of Microsoft Excel. Would you believe that we can realistically conceive a solution to the Data analysis problem, using Microsoft Access, or even better, Microsoft’s flavor of the SQL language?

         I am as big of an Excel fan as the next guy, much bigger, actually, on the second thought. However, I also believe that when possible, using the right tools for the job will yield better, faster results, than duct-taping your workarounds. So, why Access? Why on Earth, SQL? Well, let’s go to the source: ” If you often have to view your data in a variety of ways, depending on changing conditions or events, Access might be the better choice for storing and working with your data. Access lets you use Structured Query Language (SQL) queries to quickly retrieve just the rows and columns of data that you want, whether the data is contained in one table or many tables. You can also use expressions in queries to create calculated fields.”
Continue reading