# How to calculate weighted average using slide_index_dbl()?

In R I’m looking to create a row calculation that averages prices based on Product type and a date range based on Order_Date. Additionally, I need to weight the average using quantity. In the example from Excel, I’m using a SUMPRODUCT function that has three criteria: 1) Product type, 2) beginning date, and 3) ending date. For example Product type is --(A:A=A4). These three criteria then calculate a sum product of Price and Quantity. Lastly, I’m dividing the sum product by the sum of Quantity based on the previously mentioned criteria.

Here’s my dataset:

structure(list(Product = c("A", "B", "B", "F", "D", "F", "A",
"F", "D", "A", "A", "D", "C", "C", "A", "B", "C", "A", "B", "A",
"A", "E", "D", "F", "B", "B", "E", "F", "F", "E", "F", "A", "A",
"D", "F", "C", "C", "C", "A", "D", "D", "E", "D", "B", "C", "B",
"D", "F", "C", "A", "A", "F", "D", "E", "B", "B", "A", "E", "A",
"D", "E", "C", "C", "C", "E", "D", "F", "E", "B", "E", "D", "B",
"A", "B", "D", "F", "C", "C", "E", "A", "A", "F", "D", "D", "A",
"F", "C", "A", "A", "F", "A", "B", "A", "D", "C", "C", "A", "B",
"E", "B", "D", "B", "F", "F", "B", "C", "B", "B", "C", "F", "A",
"B", "A", "E", "C", "E", "E", "E", "D", "B", "C", "D", "B", "C",
"F", "F", "C", "F", "D", "F", "A", "D", "B", "B", "C", "E", "B"
), Price = c(6.502, 4.526, 2.272, 9.097, 0.594, 5.41, 9.85, 1.197,
5.09, 7.343, 3.339, 1.107, 7.993, 7.922, 4.558, 4.75, 1.278,
9.55, 8.223, 6.195, 0.668, 9.741, 9.679, 3.488, 4.159, 3.756,
6.233, 7.658, 8.896, 9.724, 6.582, 7.422, 1.172, 2.734, 4.917,
0.784, 9.284, 0.7, 6.869, 3.054, 9.945, 4.173, 5.217, 6.016,
5.559, 5.247, 7.024, 8.845, 8.436, 7.482, 8.609, 3.675, 2.76,
5.357, 4.125, 3.199, 7.736, 5.255, 5.581, 5.282, 2.753, 1.568,
2.083, 8.938, 7.562, 7.513, 9.493, 8.404, 5.266, 9.992, 3.813,
5.522, 6.295, 5.385, 1.91, 3.597, 4.105, 9.484, 6.697, 4.818,
1.644, 2.699, 0.608, 9.6, 0.447, 4.123, 2.997, 5.085, 0.903,
5.455, 1.869, 4.053, 8.843, 1.171, 8.491, 9.236, 5.642, 6.565,
3.168, 4.367, 6.008, 6.267, 8.363, 0.318, 5.226, 6.597, 2.932,
3.149, 8.578, 1.814, 9.288, 9.96, 8.44, 3.514, 2.832, 5.881,
4.57, 7.646, 2.19, 5.446, 5.318, 3.674, 6.235, 9.414, 7.201,
9.846, 0.824, 0.757, 4.928, 0.499, 9.165, 1.564, 6.944, 3.474,
8.537, 5.412, 7.142), Quantity = c(25, 33, 25, 12, 39, 28, 24,
2, 8, 22, 18, 14, 38, 42, 41, 7, 20, 5, 48, 40, 8, 11, 48, 34,
45, 14, 27, 2, 36, 33, 36, 37, 5, 30, 44, 41, 4, 42, 29, 41,
32, 47, 22, 48, 30, 39, 44, 15, 34, 33, 41, 43, 3, 34, 8, 40,
28, 10, 16, 40, 33, 37, 1, 17, 21, 25, 26, 50, 37, 43, 8, 22,
45, 13, 19, 27, 39, 42, 10, 39, 45, 43, 23, 22, 38, 38, 31, 49,
46, 13, 27, 33, 23, 5, 20, 21, 27, 27, 12, 31, 38, 12, 28, 39,
23, 50, 6, 11, 6, 19, 9, 3, 2, 39, 37, 45, 34, 46, 33, 34, 46,
46, 34, 45, 50, 17, 12, 22, 50, 37, 43, 6, 21, 27, 29, 12, 40
), Order_Date = structure(c(19208, 19210, 19337, 19288, 19339,
19318, 19352, 19290, 19245, 19317, 19352, 19177, 19306, 19300,
19196, 19314, 19270, 19212, 19232, 19199, 19238, 19274, 19265,
19311, 19257, 19344, 19294, 19331, 19181, 19348, 19317, 19332,
19334, 19338, 19184, 19274, 19240, 19350, 19195, 19306, 19198,
19283, 19260, 19263, 19177, 19197, 19186, 19205, 19264, 19328,
19268, 19190, 19210, 19300, 19273, 19188, 19280, 19264, 19211,
19241, 19296, 19350, 19260, 19201, 19254, 19262, 19176, 19175,
19335, 19179, 19196, 19190, 19253, 19178, 19190, 19234, 19253,
19310, 19323, 19325, 19258, 19292, 19213, 19269, 19225, 19299,
19350, 19224, 19193, 19250, 19242, 19260, 19340, 19276, 19211,
19222, 19305, 19305, 19273, 19268, 19306, 19277, 19225, 19188,
19190, 19181, 19268, 19291, 19272, 19238, 19184, 19228, 19199,
19237, 19205, 19251, 19357, 19238, 19288, 19301, 19331, 19260,
19320, 19175, 19253, 19312, 19354, 19357, 19176, 19187, 19225,
19281, 19352, 19315, 19246, 19308, 19356), class = "Date"), Weighted_Mean = c(4.86222058823529,
4.91654166666667, 4.63969072164948, 4.04514736842105, 1.5244347826087,
5.89505982905983, 7.69063076923077, 4.04514736842105, 4.61877586206897,
6.47659493670886, 7.69063076923077, 4.78144881889764, 8.48185245901639,
8.48185245901639, 4.94103365384615, 5.4376511627907, 3.90273529411765,
5.61707339449541, 8.32517647058824, 5.04922535211268, 4.75870476190476,
4.9231, 6.86458333333333, 5.4318954248366, 4.67877914110429,
5.37029197080292, 4.59054248366013, 6.11739393939394, 4.32138666666667,
7.10853731343284, 5.89505982905983, 6.79381761006289, 6.70562773722628,
1.5244347826087, 4.32138666666667, 3.90273529411765, 6.17783333333333,
1.55910655737705, 4.887703125, 4.47491139240506, 5.55313178294574,
4.61875384615385, 6.94804093567251, 4.75871584699454, 7.362,
4.60912738853503, 5.72447904191617, 5.01206896551724, 3.95868085106383,
6.79381761006289, 5.8291320754717, 4.6041125, 5.62133333333333,
4.77567924528302, 4.95427536231884, 4.70277372262774, 8.25473913043478,
6.27438383838384, 5.44942084942085, 5.25, 4.59054248366013, 1.55910655737705,
4.73968823529412, 5.76418918918919, 6.26311842105263, 6.86458333333333,
4.32138666666667, 9.13823655913978, 4.69287272727273, 9.13823655913978,
6.6261320754717, 4.70277372262774, 4.61464457831325, 4.48619387755102,
5.72447904191617, 4.94255405405405, 6.85711214953271, 8.48185245901639,
5.99609090909091, 6.79381761006289, 5.4215572519084, 4.04514736842105,
5.87075862068965, 6.68180459770115, 5.0480251572327, 4.59084246575342,
1.55910655737705, 5.0480251572327, 4.887703125, 5.67598780487805,
4.238525, 4.65287134502924, 6.78867597765363, 6.84263309352518,
6.53164210526316, 8.87258536585366, 6.40571428571429, 5.25782857142857,
4.9231, 4.75871584699454, 4.47491139240506, 5.03948275862069,
4.94255405405405, 4.32138666666667, 4.70277372262774, 7.362,
4.75871584699454, 5.62216666666667, 3.90273529411765, 5.81543065693431,
4.64604191616766, 8.32517647058824, 5.04922535211268, 5.79543846153846,
5.76418918918919, 5.99229192546584, 7.10853731343284, 5.79543846153846,
1.98884090909091, 4.94439622641509, 5.318, 6.94804093567251,
5.38738636363636, 7.362, 5.67598780487805, 5.4318954248366, 1.55910655737705,
0.757, 4.78144881889764, 4.32138666666667, 5.0480251572327, 4.52589393939394,
5.40888, 5.4376511627907, 6.1217397260274, 4.94131034482759,
6.45450666666667)), row.names = c(NA, -137L), class = "data.frame")

Here’s my Excel function:

Here’s what I’ve tried in R:

df <- df %>%
arrange(Order_Date) %>%
mutate(
WAVG = slide_index_dbl(
Price,
Order_Date,
~weighted.mean(
Price,
Quantity
),
.before = days(15), .after = days(15)
),
.by = Product
)

While WAVG does calculate, it doesn’t tie-out to my Weighted_Mean column from my Excel spreadsheet (column E). Here is the output from the code:

c(6.3222426035503, 5.6334269005848, 4.89748936170213, 4.90580536912752,
4.90580536912752, 5.6334269005848, 5.22891776798825, 6.3222426035503,
4.89748936170213, 5.6334269005848, 4.89748936170213, 5.55218692810458,
4.90580536912752, 4.89748936170213, 5.22891776798825, 4.89748936170213,
4.89748936170213, 5.22891776798825, 4.90580536912752, 5.22891776798825,
5.55218692810458, 5.55218692810458, 5.55218692810458, 4.90580536912752,
5.22891776798825, 4.90580536912752, 5.55218692810458, 5.55218692810458,
5.6334269005848, 4.89748936170213, 5.6334269005848, 5.55218692810458,
5.22891776798825, 4.90580536912752, 5.55218692810458, 5.6334269005848,
5.55218692810458, 4.90580536912752, 5.6334269005848, 5.55218692810458,
5.55218692810458, 4.89748936170213, 5.55218692810458, 5.22891776798825,
5.22891776798825, 4.89748936170213, 6.3222426035503, 5.55218692810458,
4.89748936170213, 6.3222426035503, 5.6334269005848, 4.90580536912752,
5.55218692810458, 4.90580536912752, 5.6334269005848, 4.89748936170213,
6.3222426035503, 5.55218692810458, 5.6334269005848, 4.89748936170213,
6.3222426035503, 5.22891776798825, 5.55218692810458, 4.90580536912752,
5.6334269005848, 5.22891776798825, 4.90580536912752, 4.90580536912752,
5.22891776798825, 5.6334269005848, 6.3222426035503, 4.90580536912752,
5.55218692810458, 5.22891776798825, 5.22891776798825, 4.90580536912752,
5.6334269005848, 5.6334269005848, 5.22891776798825, 6.3222426035503,
6.3222426035503, 5.6334269005848, 4.90580536912752, 5.22891776798825,
5.55218692810458, 4.90580536912752, 6.3222426035503, 4.89748936170213,
4.90580536912752, 4.89748936170213, 5.22891776798825, 4.89748936170213,
6.3222426035503, 6.3222426035503, 4.89748936170213, 5.6334269005848,
6.3222426035503, 5.22891776798825, 5.55218692810458, 5.22891776798825,
5.6334269005848, 4.90580536912752, 4.90580536912752, 6.3222426035503,
5.6334269005848, 4.89748936170213, 4.89748936170213, 5.22891776798825,
5.22891776798825, 5.55218692810458, 4.89748936170213, 4.89748936170213,
5.22891776798825, 6.3222426035503, 5.55218692810458, 5.55218692810458,
4.89748936170213, 5.6334269005848, 5.55218692810458, 5.55218692810458,
5.22891776798825, 5.22891776798825, 4.90580536912752, 4.90580536912752,
5.55218692810458, 5.22891776798825, 6.3222426035503, 5.6334269005848,
5.6334269005848, 5.6334269005848, 5.55218692810458, 5.55218692810458,
5.22891776798825, 5.6334269005848, 5.22891776798825, 6.3222426035503,
4.89748936170213)

Here is the View(df):

### >Solution :

df %>%
arrange(Order_Date) %>%
mutate(
WAVG = slide_index2_dbl(
Price,
Quantity,
Order_Date,
~sum(.x * .y) / sum(.y),
.before = days(15), .after = days(15)
),
.by = Product
)