Sumproduct Vs. Excel Pivot Tables: Which Method is Best for Calculating Highest Monthly Production Scrap Value

Getting the answers to your most important business questions is imperative for achieving success. Being able to find the answers to your questions can quickly give you insight into what business processes and procedures need to be fixed in order for you to lower your costs and maximize your returns. In this post we look at using Excel Pivot Tables versus the SumProduct formula to calculate which month had the highest production scrap within a given year. Being able to get this information can allow management to dig deeper into why a given month had such a high volume of scrap and help address issues that may have caused the spike.

WHAT EXCEL FORMULAS AND CONCEPTS WILL I LEARN?

HOW TO USE THE RAND WITH INT FORMULA TO GENERATE AUTOMATIC SCRAP QUANTITIES BETWEEN 1 AND 10.

HOW TO USE THE RANDBETWEEN WITH DATE FORMULA TO GENERATE AUTOMATIC TRANSACTION DATES WITHIN A GIVEN TIME RANGE.

HOW TO USE THE SUMPRODUCT FORMULA WITH THE IF AND MONTH FORMULA TO CALCULATE THE TOTAL SCRAP VALUE FOR EACH MONTH WITHIN OUR TRANSACTION DATE TIME FRAME.

HOW TO USE THE MAX/MIN FORMULAS TO CALCULATE THE MONTH WITHIN OUR TIME RANGE WITH THE HIGHEST/LOWEST SCRAP VALUE.

HOW TO USE THE INDEX/MATCH FORMULA TO FIND THE CORRESPONDING MONTH WITH THE HIGHEST/LOWEST SCRAP VALUE.

HOW TO CREATE A PIVOT TABLE.

HOW TO USE PIVOT TABLE GROUPING OPTION TO CHANGE TRANSACTION DATES TO MONTHS AND SUM THAT INFORMATION.

HOW TO USE FILTERING (PARTICULARLY) THE TOP 10 FILTER OPTION WITHIN PIVOT TABLES TO CALCULATE THE LOWEST AND THE HIGHEST VALUE OF SCRAP.

CREATING THE DATA…

Unfortunately, I don’t have an open source database with tons and tons of sample data sets to use. For that reason I usually have to create my own data sets and here are a couple of handy formulas/functions that you can use to do this.

= RANDBETWEEN(bottom,top) This formula is used to generate a random value/date between a user defined range. Bottom of course would be your lowest value or earliest date and top would be your highest value or latest date.

=DATE(year,month,day) The date formula will allow you to to specify a date. This is done in the following format: January 1, 2013 would be: =DATE(2013,1,1)

=TODAY() This formula will return today’s date. If you created the spreadsheet yesterday and you opened it today, the cell/date would update to today’s date. The format of the formula is used exactly as shown above (=TODAY( )).

=RAND( ) This formula returns a random number between 0 and 1. Using a multiplier (e.g.10, 100, 1000), you can auto-generate values greater than 1. In this post, I’ve also incorporated the INT( ) formula which ensures that the number is rounded down to the nearest integer.

=INT( ) As mentioned above, this formula allows you to round down the value to the nearest integer.

Alright now that we’ve reviewed the formulas, I’ll show you a couple of screen shots with the actual examples to give you an idea of how I did this.

To auto-generate the assembly unit cost, I used the RANDBETWEEN formula again with values ranging between 5 and 400. As for the total scrap cost, I’m sure you guessed it by now but it was a multiplication of the scrap quantity by the assembly unit cost for each record.

That wasn’t hard was it. Now we’re going to look at a couple of different ways we can find the month that generated the highest scrap value using our newly created data set.

USING THE SUMPRODUCT FORMULA TO FIND THE MONTH THAT PRODUCED THE HIGHEST SCRAP VALUE

In this section, we are going to look at how you can use the sumproduct formula to build a table with the sum of the total scrap value for each month in the past year (in our case right now 2013). We created an Excel table of raw data in the last section with four different fields. The reason I used an Excel table (more on Excel Tables here) is because we can use our field names as our ranges which will allow us to add new records to our raw data set and have our new summary data table update automatically. Once we build this summary table, we will be able to determine which month we produced the most scrap in. The reason I use the sumproduct formula instead of the sumif formula is because I needed to incorporate the month formula into my statement which looked at the entire array as my criteria (sumproduct is used specifically for arrays). The sumif formula only allows you to look at one value not an array of values.

=SUMPRODUCT(array1,array2,array3,…) This returns the sum of the products corresponding ranges or arrays. This is useful for adding and multiplying arrays together that are the same size. We are using this formula because of its capabilities with arrays (ranges of cells as opposed to just a single cell).

=IF(logical test,value if true, value if false) The IF formula will be nested within the sumproduct formula and will check to see the if each transaction date is equal to a given month (using a nested month formula). If the value is true, it will sum the scrap value for each of the records. If it is false, it will add 0 value to our running total.

=MONTH ( ) The month formula will be used within the if statement to check if the transaction date is equal to the month in question. January = 1, February = 2, March = 3, etc..

Now that we’ve built our summary table, let’s have a look at using the max/min formulas to find the month where we produced the highest value of scrap.

(For more on the index match formula)

There you have it, we’ve found the month with the highest scrap value produced using the sumproduct formula. This value will be dynamic as you add more data to your raw data set table. Let’s match this method up against using an Excel Pivot Table.

USING EXCEL PIVOT TABLES TO FIND THE MONTH THAT PRODUCED THE HIGHEST SCRAP VALUE

Pivot tables are an extremely easy and quick way to summarize large sets of data. Here were are going to look at how to create a pivot table using a raw data table to find the month within the year that produced the highest value of scrap.

That’s it. We were able to come to the same conclusion using both methods. From my own personal experience, the Pivot table is the much better option when it comes to trying to summarize large sets of data instead of manually creating a summary table. What are your thoughts- which method do you prefer?

Download the Free Excel Workbook!

GET IT FREE!