Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I would appreciate some assistance, as i keep getting stuck with calculation of rolling balances.
The following is employee data for hours worked on a Monday across several periods.
I need to calculate the MEDIAN and the AVERAGE across several periods, 4 , 13, 24, and 52
However, when applying the following calculation, i seem to get the same answer for all reference periods - 8.50 Median and 6.38 Average
RangeAvg(Above(Median([Monday (Worked)]), 0, 4))
RangeAvg(Above(Median([Monday (Worked)]), 0, 13))
RangeAvg(Above(Median([Monday (Worked)]), 0, 24))
RangeAvg(Above(Median([Monday (Worked)]), 0, 52))
RangeAvg(Above(Avg([Monday (Worked)]), 0, 4))
RangeAvg(Above(Avg([Monday (Worked)]), 0, 13))
RangeAvg(Above(Avg([Monday (Worked)]), 0, 24))
RangeAvg(Above(Avg([Monday (Worked)]), 0, 52))
I should be getting the following answers below:
# of pay periods | Monday (Worked) | Median | Average |
1 | 0.0 | ||
2 | 9.0 | ||
3 | 8.5 | ||
4 | 9.0 | 8.75 | 6.63 |
5 | 9.0 | ||
6 | 8.3 | ||
7 | 8.5 | ||
8 | 9.0 | ||
9 | 4.3 | ||
10 | 10.3 | ||
11 | 10.3 | ||
12 | 8.8 | ||
13 | 9.3 | 9.00 | 8.00 |
14 | 4.5 | ||
15 | 0.0 | ||
16 | 8.8 | ||
17 | 9.0 | ||
18 | 9.3 | ||
19 | 9.0 | ||
20 | 4.5 | ||
21 | 4.0 | ||
22 | 9.0 | ||
23 | 9.3 | ||
24 | 8.8 | 8.88 | 7.50 |
25 | 3.0 | ||
26 | 3.0 | ||
27 | 0.8 | ||
28 | 2.0 | ||
29 | 1.5 | ||
30 | 3.5 | ||
31 | 7.0 | ||
32 | 9.0 | ||
33 | 9.0 | ||
34 | 8.8 | ||
35 | 4.0 | ||
36 | 8.5 | ||
37 | 9.0 | ||
38 | 9.0 | ||
39 | 4.8 | ||
40 | 0.0 | ||
41 | 0.0 | ||
42 | 8.0 | ||
43 | 3.5 | 8.50 | 6.38 |
Is this what you need
Mark, did Sunny's post help you get things working as you wished? If so, please be sure to circle back here and click the Accept as Solution button on his reply to give him credit for the help. If you used something else, it would be helpful to others to post that and mark that as the solution. If you still require further assistance, provide an update of where you stand.
Regards,
Brett