{"id":198,"date":"2014-09-22T22:35:18","date_gmt":"2014-09-22T20:35:18","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=198"},"modified":"2024-05-21T04:56:12","modified_gmt":"2024-05-21T02:56:12","slug":"sumproduct-weighted-averages","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/sumproduct-weighted-averages\/","title":{"rendered":"Sumproduct &#038; Weighted Averages"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_57_1 counter-flat ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li><a href=\"#\" class=\"scroll-to-download\">Free Practice Workbook<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/sumproduct-weighted-averages\/#Sumproduct_Weighted_Averages\" title=\"Sumproduct &amp; Weighted Averages\">Sumproduct &amp; Weighted Averages<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/sumproduct-weighted-averages\/#Array1\" title=\"Array1\">Array1<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/sumproduct-weighted-averages\/#Array2\" title=\"Array2\">Array2<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"Sumproduct_Weighted_Averages\"><\/span>Sumproduct &amp; Weighted Averages<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><em><strong>What does it do? <\/strong><\/em><\/p>\n<p>It returns the sum of the products of corresponding ranges or arrays<\/p>\n<p><em><strong>Formula breakdown: <\/strong><\/em><\/p>\n<p>=SUMPRODUCT(<span style=\"color: #0000ff;\">array1<\/span>, <span style=\"color: #ff6600;\">[array2]<\/span>, <span style=\"color: #008000;\">[array3]<\/span>&#8230;)<\/p>\n<p><em><strong>What it means: <\/strong><\/em><\/p>\n<p>=SUMPRODUCT(<span style=\"color: #0000ff;\">this array<\/span>, <span style=\"color: #ff6600;\">with that array<\/span>\u2026)<\/p>\n<hr \/>\n<p>A quick way to calculate the weighted average of two lists of data is to use the <strong>SUMPRODUCT<\/strong> formula.\u00a0 A <strong>weighted average<\/strong> can be used to determine the average salary of employees, the average grade of an exam or the average selling price of a company\u00b4s stock list, as can been seen below.<\/p>\n<div class=\"after-post-box\">\n<p class=\"line-two\"><a class=\"IRPP_minimalist icon-download button small\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2014\/09\/Sumproduct_Easy.xlsx\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2014\/09\/Sumproduct_Easy.xlsx\" data-leadbox-id=\"147284833f72a2:12f08263ab46dc\"><span class=\"ctaText px-3 text-capitalize\">Download excel workbook<i class=\"fa fa-download\"><\/i><\/span><span class=\"postTitle px-3\">Sumproduct_Easy.xlsx<\/span><\/a><\/p>\n<p class=\"line-two\">\n<\/div>\n<p>We want to get the average selling price of our total stock items. This is easily achievable with the <strong>SUMPRODUCT<\/strong> formula! We will use this to calculate the total value of the items, then<strong> divide this by the total number of units<\/strong> to get the <strong>average selling price<\/strong>.<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 1:<\/span><\/strong>\u00a0We need to enter the <strong>SUMPRODUCT<\/strong> function in a blank cell:<br \/>\n<code>=SUMPRODUCT(<\/code><br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-9840\" title=\"Sumproduct &amp; Weighted Averages\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2014\/09\/Sumproduct1.png\" alt=\"Sumproduct &amp; Weighted Averages\" width=\"894\" height=\"300\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2014\/09\/Sumproduct1.png 894w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2014\/09\/Sumproduct1-300x101.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2014\/09\/Sumproduct1-768x258.png 768w\" sizes=\"(max-width: 894px) 100vw, 894px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2:<\/strong><\/span> The <strong>SUMPRODUCT<\/strong> arguments:<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Array1\"><\/span><em>Array1<\/em><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><strong>What is the first array that contains the data?<\/strong><\/p>\n<p>We want to get the units sold so select those values.<br \/>\n<code>=SUMPRODUCT(<span style=\"color: #0000ff;\">C14:C17,<\/span><\/code><br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-9841\" title=\"Sumproduct &amp; Weighted Averages\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2014\/09\/Sumproduct2.png\" alt=\"Sumproduct &amp; Weighted Averages\" width=\"751\" height=\"185\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2014\/09\/Sumproduct2.png 751w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2014\/09\/Sumproduct2-300x74.png 300w\" sizes=\"(max-width: 751px) 100vw, 751px\" \/><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Array2\"><\/span><em>Array2<\/em><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><strong>What is the second array that contains the data?<\/strong><\/p>\n<p>We want to get the sale price sold so select those values. The values will be multiplied against the first array that we got.<br \/>\n<code>=SUMPRODUCT(C14:C17, <span style=\"color: #0000ff;\">D14:D17)<\/span><\/code><br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-9842\" title=\"Sumproduct &amp; Weighted Averages\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2014\/09\/Sumproduct3.png\" alt=\"Sumproduct &amp; Weighted Averages\" width=\"704\" height=\"185\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2014\/09\/Sumproduct3.png 704w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2014\/09\/Sumproduct3-300x79.png 300w\" sizes=\"(max-width: 704px) 100vw, 704px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 3:<\/strong> <\/span>Now we have the total value, we can easily get the average value by dividing by the total number of items.<br \/>\n<code>=SUMPRODUCT(C14:C17, D14:D17) <span style=\"color: #0000ff;\">\/ SUM(C14:C17)<\/span><\/code><br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-9843\" title=\"Sumproduct &amp; Weighted Averages\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2014\/09\/Sumproduct4.png\" alt=\"Sumproduct &amp; Weighted Averages\" width=\"805\" height=\"208\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2014\/09\/Sumproduct4.png 805w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2014\/09\/Sumproduct4-300x78.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2014\/09\/Sumproduct4-768x198.png 768w\" sizes=\"(max-width: 805px) 100vw, 805px\" \/><\/p>\n<p>With just this single formula, we are able to get the average selling price without the need of extra helper columns!<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-9844\" title=\"Sumproduct &amp; Weighted Averages\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2014\/09\/Sumproduct5.png\" alt=\"Sumproduct &amp; Weighted Averages\" width=\"631\" height=\"181\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2014\/09\/Sumproduct5.png 631w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2014\/09\/Sumproduct5-300x86.png 300w\" sizes=\"(max-width: 631px) 100vw, 631px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Sumproduct &amp; Weighted Averages What does it do? It returns the sum of the products of corresponding ranges or arrays Formula breakdown: =SUMPRODUCT(array1, [array2], [array3]&#8230;) What it means: =SUMPRODUCT(this array, with that array\u2026) A quick way to calculate the weighted average of two lists of data is to use the SUMPRODUCT formula.\u00a0 A weighted average [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":17496,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"Sumproduct & Weighted Averages","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[3,15,285],"tags":[43,46],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/198"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=198"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/198\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17496"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=198"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=198"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=198"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}