Calculate on every third Friday of the month and show results between positive & negative weeks

15minoffame

Board Regular
Joined
Nov 26, 2014
Messages
55
Hi everyone,
First, I'd like to wish Mr.Excel.com a belated 25th birthday! I know I can use the Mod function to calculate between every X number of rows, but how is it calculated when between every X week the number of weeks is not uniform? This spreadsheet shows the weekly return of Microsoft since 2021. Every third week of every month is the monthly expiration cycle. The problem is Yahoo starts each week on a Monday, but the expiration is every third Friday of the month. I highlighted a few in red so you get the idea.

I would like to know TWO averages.

1) The average of all the bold columns for only the red highlighted rows aka monthly expiration week.
2) In addition, is it even possible to calculate the avgs between positive & negative weeks? The reason for this is because the O-H (Open-High) is much higher when it's a positive return week while the O-L (Open-Low) is the higher number during the negative weeks.

Thank you!

DateOpenHighLowCloseG/L %PtsO-HO-H %O-LO-L %
12/28/2020224.45227.18219.68216.60292.73-4.77
1/4/2021222.53223211.94213.8761-1.26%-2.730.470.21%-10.59-4.76%
1/11/2021218.47218.91212.03207.0884-3.17%-6.790.440.20%-6.44-2.95%
1/18/2021213.75230.07212.63220.04066.25%12.9516.327.64%-1.12-0.52%
1/25/2021229.12242.64224.22225.89342.66%5.8513.525.90%-4.90-2.14%
2/1/2021235.06245.09232.43235.86564.41%9.9710.034.27%-2.63-1.12%
2/8/2021243.15245.92240.81238.58261.15%2.722.771.14%-2.34-0.96%
2/15/2021245.03246.13240.18234.6677-1.64%-3.911.100.45%-4.85-1.98%
2/22/2021237.42237.93227.88226.8236-3.34%-7.840.510.21%-9.54-4.02%
3/1/2021235.9237.47224.26226.0623-0.34%-0.761.570.67%-11.64-4.93%
3/8/2021231.37239.17227.13230.11311.79%4.057.803.37%-4.24-1.83%
3/15/2021234.96240.06229.35224.8422-2.29%-5.275.102.17%-5.61-2.39%
3/22/2021230.27241.05230.14230.82562.66%5.9810.784.68%-0.13-0.06%
3/29/2021236.59242.84231.1236.55532.48%5.736.252.64%-5.49-2.32%
4/5/2021242.76255.99242.7249.73255.57%13.1813.235.45%-0.06-0.02%
4/12/2021254.71261254.62254.50551.91%4.776.292.47%-0.09-0.04%
4/19/2021260.19261.78255.64254.90570.16%0.401.590.61%-4.55-1.75%
4/26/2021261.66263.19249246.1502-3.43%-8.761.530.58%-12.66-4.84%
5/3/2021253.4254.35244.69246.42350.11%0.270.950.37%-8.71-3.44%
5/10/2021250.87251.73238.07242.2166-1.71%-4.210.860.34%-12.80-5.10%
5/17/2021246.55248.33238.6239.3078-1.20%-2.911.780.72%-7.95-3.22%
5/24/2021247.79252.94247.51244.27272.07%4.965.152.08%-0.28-0.11%
5/31/2021251.23251.65243245.35870.44%1.090.420.17%-8.23-3.28%
6/7/2021249.98258.49249.81252.30492.83%6.958.513.40%-0.17-0.07%
6/14/2021257.9262.3254.42253.81160.60%1.514.401.71%-3.48-1.35%
6/21/2021259.82267.85257.92259.28052.15%5.478.033.09%-1.90-0.73%
6/28/2021266.19278265.91271.6374.77%12.3611.814.44%-0.28-0.11%
7/5/2021278.03280.69274.3271.92070.10%0.282.660.96%-3.73-1.34%
7/12/2021279.16284.1276.58274.66991.01%2.754.941.77%-2.58-0.92%
7/19/2021278.93289.99274.45283.39673.18%8.7311.063.97%-4.48-1.61%
7/26/2021289290.15282.95278.7398-1.64%-4.661.150.40%-6.05-2.09%
8/2/2021286.36289.63283.74283.19121.60%4.453.271.14%-2.62-0.91%
8/9/2021289.75292.9285.2286.50781.17%3.323.151.09%-4.55-1.57%
8/16/2021293.19305.84288.64297.76853.93%11.2612.654.31%-4.55-1.55%
8/23/2021303.25305.65296.83293.7904-1.34%-3.982.400.79%-6.42-2.12%
8/30/2021301.12305.19300.18295.18230.47%1.394.071.35%-0.94-0.31%
9/6/2021301.01302.14295.38289.8597-1.80%-5.321.130.38%-5.63-1.87%
9/13/2021297.55305.32294.08293.93741.41%4.087.772.61%-3.47-1.17%
9/20/2021296.33300.9289.52293.4277-0.17%-0.514.571.54%-6.81-2.30%
9/27/2021296.14296.47281.29283.3805-3.42%-10.050.330.11%-14.85-5.01%
10/4/2021287.4296.64280.25289.01681.99%5.649.243.22%-7.15-2.49%
10/11/2021292.92304.45292.35298.19153.17%9.1711.533.94%-0.57-0.19%
10/18/2021303.57311.09302.69303.04361.63%4.857.522.48%-0.88-0.29%
10/25/2021309.36332306.46325.05937.26%22.0222.647.32%-2.90-0.94%
11/1/2021331.36338.79326.37329.41151.34%4.357.432.24%-4.99-1.51%
11/8/2021337.3338.72329.92330.05840.20%0.651.420.42%-7.38-2.19%
11/15/2021337.54345.1334.03336.32191.90%6.267.562.24%-3.51-1.04%
11/22/2021344.62349.67328.12323.7488-3.74%-12.575.051.47%-16.50-4.79%
11/29/2021334.94339.28318.03317.1989-2.02%-6.554.341.30%-16.91-5.05%
12/6/2021323.95343319.23336.37756.05%19.1819.055.88%-4.72-1.46%
12/13/2021340.68343.79317.25317.9747-5.47%-18.403.110.91%-23.43-6.88%
12/20/2021320.05336.39317.57328.66883.36%10.6916.345.11%-2.48-0.77%
12/27/2021335.46344.3335.43330.26940.49%1.608.842.64%-0.03-0.01%
1/3/2022335.35338310.09308.3903-6.62%-21.882.650.79%-25.26-7.53%
1/10/2022309.49323.41303.75304.6193-1.22%-3.7713.924.50%-5.74-1.85%
1/17/2022304.07313.91295.61290.7043-4.57%-13.929.843.24%-8.46-2.78%
1/24/2022292.2308.5276.05302.71434.13%12.0116.305.58%-16.15-5.53%
1/31/2022308.95315.12299.96300.436-0.75%-2.286.172.00%-8.99-2.91%
2/7/2022306.17311.93294.22289.7321-3.56%-10.705.761.88%-11.95-3.90%
2/14/2022293.77300.87286.31282.7499-2.41%-6.987.102.42%-7.46-2.54%
2/21/2022285297.63271.52292.56493.47%9.8112.634.43%-13.48-4.73%
2/28/2022294.31303.13287.17285.2338-2.51%-7.338.823.00%-7.14-2.43%
3/7/2022288.53289.69270275.6001-3.38%-9.631.160.40%-18.53-6.42%
3/14/2022280.34301275.82295.63517.27%20.0420.667.37%-4.52-1.61%
3/21/2022298.89305.5294.9298.83331.08%3.206.612.21%-3.99-1.33%
3/28/2022304.33315.95304.33304.48171.89%5.6511.623.82%0.000.00%
4/4/2022310.09315.11296.28292.2304-4.02%-12.255.021.62%-13.81-4.45%
4/11/2022291.79292.61279.32275.3639-5.77%-16.870.820.28%-12.47-4.27%
4/18/2022278.91293.3273.38269.6565-2.07%-5.7114.395.16%-5.53-1.98%
4/25/2022273.29290.98270273.09081.27%3.4317.696.47%-3.29-1.20%
5/2/2022277.71290.88271.27270.3453-1.01%-2.7513.174.74%-6.44-2.32%
5/9/2022270.06273.75250.02256.9525-4.95%-13.393.691.37%-20.04-7.42%
5/16/2022259.96268.33246.44248.5291-3.28%-8.428.373.22%-13.52-5.20%
5/23/2022255.49273.34253.43269.50538.44%20.9817.856.99%-2.06-0.81%
5/30/2022272.53277.69261.6266.3293-1.18%-3.185.161.89%-10.93-4.01%
6/6/2022272.06274.18252.53249.5321-6.31%-16.802.120.78%-19.53-7.18%
6/13/2022245.11255.3241.51244.2651-2.11%-5.2710.194.16%-3.60-1.47%
6/20/2022250.26267.98249.51264.0418.10%19.7817.727.08%-0.75-0.30%
6/27/2022268.21268.3252.9256.032-3.03%-8.010.090.03%-15.31-5.71%
7/4/2022256.16269.06254.74264.00163.11%7.9712.905.04%-1.42-0.55%
7/11/2022265.65266.53245.94253.2111-4.09%-10.790.880.33%-19.71-7.42%
7/18/2022259.75265.33253.3256.80141.42%3.595.582.15%-6.45-2.48%
7/25/2022261282249.57276.90277.83%20.1021.008.05%-11.43-4.38%
8/1/2022277.82283.8272.38279.04320.77%2.145.982.15%-5.44-1.96%
8/8/2022284.05291.91277.61287.92013.18%8.887.862.77%-6.44-2.27%
8/15/2022291294.18285.56282.2388-1.97%-5.683.181.09%-5.44-1.87%
8/22/2022282.08282.46267.98264.987-6.11%-17.250.380.13%-14.10-5.00%
8/29/2022265.85267.4254.47253.0962-4.49%-11.891.550.58%-11.38-4.28%
9/5/2022256.2265.23251.94261.3993.28%8.309.033.52%-4.26-1.66%
9/12/2022265.78267.45242.06241.9073-7.46%-19.491.670.63%-23.72-8.92%
9/19/2022242.47247.66235.2235.1662-2.79%-6.745.192.14%-7.27-3.00%
9/26/2022237.05242.33232.73230.2043-2.11%-4.965.282.23%-4.32-1.82%
10/3/2022235.41250.58233.17231.52880.58%1.3215.176.44%-2.24-0.95%
10/10/2022233.05237.24219.13225.9145-2.42%-5.614.191.80%-13.92-5.97%
10/17/2022235.82243.93234.29239.31765.93%13.408.113.44%-1.53-0.65%
10/24/2022243.76251.04225.78233.1399-2.58%-6.187.282.99%-17.98-7.38%
10/31/2022233.76235.74213.43218.8275-6.14%-14.311.980.85%-20.33-8.70%
11/7/2022221.99247.99221.28244.249811.62%25.4226.0011.71%-0.71-0.32%
11/14/2022241.99247237.63238.428-2.38%-5.825.012.07%-4.36-1.80%
11/21/2022241.43248.7240.71245.31482.89%6.897.273.01%-0.72-0.30%
11/28/2022246.08256.12238.21252.77863.04%7.4610.044.08%-7.87-3.20%
12/5/2022252.01253.82242.21243.263-3.76%-9.521.810.72%-9.80-3.89%
12/12/2022247.45263.92243.51242.5394-0.30%-0.7216.476.66%-3.94-1.59%
12/19/2022244.86245.62233.87236.6318-2.44%-5.910.760.31%-10.99-4.49%
12/26/2022238.7241.92234.17237.71220.46%1.083.221.35%-4.53-1.90%
1/2/2023243.08245.75219.35222.9531-6.21%-14.762.671.10%-23.73-9.76%
1/9/2023226.45239.9226.41237.12746.36%14.1713.455.94%-0.04-0.02%
1/16/2023237.97242.38230.68238.10870.41%0.984.411.85%-7.29-3.06%
1/23/2023241.1249.83230.9245.97893.31%7.878.733.62%-10.20-4.23%
1/30/2023244.51264.69242.2256.07944.11%10.1020.188.25%-2.31-0.94%
2/6/2023257.44276.76254.78260.78761.84%4.7119.327.50%-2.66-1.03%
2/13/2023267.64274.97256255.7919-1.92%-5.007.332.74%-11.64-4.35%
2/20/2023254.48256.84248.1247.6483-3.18%-8.142.360.93%-6.38-2.51%
2/27/2023252.46255.62245.61253.68012.44%6.033.161.25%-6.85-2.71%
3/6/2023256.43260.12247.6247.0223-2.62%-6.663.691.44%-8.83-3.44%
3/13/2023247.4283.33245.73277.667812.41%30.6535.9314.52%-1.67-0.68%
3/20/2023276.98281.06269.52278.80070.41%1.134.081.47%-7.46-2.69%
3/27/2023280.5289.27272.05286.48192.76%7.688.773.13%-8.45-3.01%
4/3/2023286.52292.08282.03289.76111.14%3.285.561.94%-4.49-1.57%
4/10/2023289.21289.9281.64284.3355-1.87%-5.430.690.24%-7.57-2.62%
4/17/2023289.93291.76283.06283.9579-0.13%-0.381.830.63%-6.87-2.37%
4/24/2023282.09308.93275.37305.32247.52%21.3626.849.51%-6.72-2.38%
5/1/2023306.97311.97303.4308.69091.10%3.375.001.63%-3.57-1.16%
5/8/2023310.13313306.09307.0216-0.54%-1.672.870.93%-4.04-1.30%
5/15/2023309.1319.04307.59316.33253.03%9.319.943.22%-1.51-0.49%
5/22/2023318.6333.4312.61331.51394.80%15.1814.804.65%-5.99-1.88%
5/29/2023335.23337.5324.72334.01340.75%2.502.270.68%-10.51-3.14%
6/5/2023335.22338.56322.5325.4391-2.57%-8.573.341.00%-12.72-3.79%
6/12/2023328.58351.47325.16340.91484.76%15.4822.896.97%-3.42-1.04%
6/19/2023339.31342.08332.07333.635-2.14%-7.282.770.82%-7.24-2.13%
6/26/2023333.72342.73328.49339.13221.65%5.509.012.70%-5.23-1.57%
7/3/2023339.19342.99334.73335.8259-0.97%-3.313.801.12%-4.46-1.31%
7/10/2023334.6351.43327343.81272.38%7.9916.835.03%-7.60-2.27%
7/17/2023345.68366.78339.83342.3488-0.43%-1.4621.106.10%-5.85-1.69%
7/24/2023345.85351.89329.05336.9712-1.57%-5.386.041.75%-16.80-4.86%
7/31/2023336.92338.54325.95326.425-3.13%-10.551.620.48%-10.97-3.26%
8/7/2023328.37331.11319.21319.683-2.07%-6.742.740.83%-9.16-2.79%
8/14/2023321.39325.09311.55315.1717-1.41%-4.513.701.15%-9.84-3.06%
8/21/2023317.93332.98317.04322.32582.27%7.1515.054.73%-0.89-0.28%
8/28/2023325.66331.99321.72327.99431.76%5.676.331.94%-3.94-1.21%
9/4/2023329336.16328.66333.59291.71%5.607.162.18%-0.34-0.10%
9/11/2023337.24340.86329.65329.5511-1.21%-4.043.621.07%-7.59-2.25%
9/18/2023327.8330.4315316.3679-4.00%-13.182.600.79%-12.80-3.90%
9/25/2023316.59319.47309.45315.1104-0.40%-1.262.880.91%-7.14-2.26%
10/2/2023316.28329.19311.21326.59713.65%11.4912.914.08%-5.07-1.60%
10/9/2023324.75333.83323.18327.06620.14%0.479.082.80%-1.57-0.48%
10/16/2023331.05336.88325.45326.0083-0.32%-1.065.831.76%-5.60-1.69%
10/23/2023325.47346.2324.39329.1420.96%3.1320.736.37%-1.08-0.33%
10/30/2023333.41354.39331.83352.08546.97%22.9420.986.29%-1.58-0.47%
11/6/2023353.45370.1353.35368.92124.78%16.8416.654.71%-0.10-0.03%
11/13/2023368.22376.35365.9369.10090.05%0.188.132.21%-2.32-0.63%
11/20/2023371.22379.79371377.432.26%8.338.572.31%-0.22-0.06%
11/27/2023376.78384.3371.31374.51-0.77%-2.927.522.00%-5.47-1.45%
12/4/2023369.1374.46362.9374.23-0.07%-0.285.361.45%-6.20-1.68%
12/11/2023368.48377.64364.13370.73-0.94%-3.509.162.49%-4.35-1.18%
12/18/2023369.45376.03368.68374.581.04%3.856.581.78%-0.77-0.21%
12/25/2023375377.16372.81376.040.39%1.462.160.58%-2.19-0.58%
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
what version of excel are you using? There are functions available in later versions that may make your solution different from what others use?

Your dates start on Mondays, but you ask for 3rd fridays. What if the first friday is less than the 4th of the month .... what day is the 3rd Friday?
 
Upvote 0
The xl2bb add in won't allow a large paste. So, I've truncated it to 98 rows. Below the formula sections is a pivot table of averages.
I needed the helper column for the pivot, but it isn't necessary for the array formulas. It may not be needed for the pivot if you can somehow calculate it.

This only answers your first question. I'm not sure what you're asking for in the second.

Book1
ABCDEFGHIJKL
1Date3rd FridayOpenHighLowCloseG/L %PtsO-HO-H %O-LO-L %
2Mon 2020-12-28FALSE224.45227.18219.68216.60292.73-4.77
3Mon 2021-01-04FALSE222.53223.00211.94213.8761-1%-2.730.470%-10.59-5%
4Mon 2021-01-11TRUE218.47218.91212.03207.0884-3%-6.790.440%-6.44-3%
5Mon 2021-01-18FALSE213.75230.07212.63220.04066%12.9516.328%-1.12-1%
6Mon 2021-01-25FALSE229.12242.64224.22225.89343%5.8513.526%-4.90-2%
7Mon 2021-02-01FALSE235.06245.09232.43235.86564%9.9710.034%-2.63-1%
8Mon 2021-02-08FALSE243.15245.92240.81238.58261%2.722.771%-2.34-1%
9Mon 2021-02-15TRUE245.03246.13240.18234.6677-2%-3.911.100%-4.85-2%
10Mon 2021-02-22FALSE237.42237.93227.88226.8236-3%-7.840.510%-9.54-4%
11Mon 2021-03-01FALSE235.90237.47224.26226.06230%-0.761.571%-11.64-5%
12Mon 2021-03-08FALSE231.37239.17227.13230.11312%4.057.803%-4.24-2%
13Mon 2021-03-15TRUE234.96240.06229.35224.8422-2%-5.275.102%-5.61-2%
14Mon 2021-03-22FALSE230.27241.05230.14230.82563%5.9810.785%-0.130%
15Mon 2021-03-29FALSE236.59242.84231.10236.55532%5.736.253%-5.49-2%
16Mon 2021-04-05FALSE242.76255.99242.70249.73256%13.1813.235%-0.060%
17Mon 2021-04-12TRUE254.71261.00254.62254.50552%4.776.292%-0.090%
18Mon 2021-04-19FALSE260.19261.78255.64254.90570%0.401.591%-4.55-2%
19Mon 2021-04-26FALSE261.66263.19249.00246.1502-3%-8.761.531%-12.66-5%
20Mon 2021-05-03FALSE253.40254.35244.69246.42350%0.270.950%-8.71-3%
21Mon 2021-05-10FALSE250.87251.73238.07242.2166-2%-4.210.860%-12.80-5%
22Mon 2021-05-17TRUE246.55248.33238.60239.3078-1%-2.911.781%-7.95-3%
23Mon 2021-05-24FALSE247.79252.94247.51244.27272%4.965.152%-0.280%
24Mon 2021-05-31FALSE251.23251.65243.00245.35870%1.090.420%-8.23-3%
25Mon 2021-06-07FALSE249.98258.49249.81252.30493%6.958.513%-0.170%
26Mon 2021-06-14TRUE257.90262.30254.42253.81161%1.514.402%-3.48-1%
27Mon 2021-06-21FALSE259.82267.85257.92259.28052%5.478.033%-1.90-1%
28Mon 2021-06-28FALSE266.19278.00265.91271.63705%12.3611.814%-0.280%
29Mon 2021-07-05FALSE278.03280.69274.30271.92070%0.282.661%-3.73-1%
30Mon 2021-07-12TRUE279.16284.10276.58274.66991%2.754.942%-2.58-1%
31Mon 2021-07-19FALSE278.93289.99274.45283.39673%8.7311.064%-4.48-2%
32Mon 2021-07-26FALSE289.00290.15282.95278.7398-2%-4.661.150%-6.05-2%
33Mon 2021-08-02FALSE286.36289.63283.74283.19122%4.453.271%-2.62-1%
34Mon 2021-08-09FALSE289.75292.90285.20286.50781%3.323.151%-4.55-2%
35Mon 2021-08-16TRUE293.19305.84288.64297.76854%11.2612.654%-4.55-2%
36Mon 2021-08-23FALSE303.25305.65296.83293.7904-1%-3.982.401%-6.42-2%
37Mon 2021-08-30FALSE301.12305.19300.18295.18230%1.394.071%-0.940%
38Mon 2021-09-06FALSE301.01302.14295.38289.8597-2%-5.321.130%-5.63-2%
39Mon 2021-09-13TRUE297.55305.32294.08293.93741%4.087.773%-3.47-1%
40Mon 2021-09-20FALSE296.33300.90289.52293.42770%-0.514.572%-6.81-2%
41Mon 2021-09-27FALSE296.14296.47281.29283.3805-3%-10.050.330%-14.85-5%
42Mon 2021-10-04FALSE287.40296.64280.25289.01682%5.649.243%-7.15-2%
43Mon 2021-10-11TRUE292.92304.45292.35298.19153%9.1711.534%-0.570%
44Mon 2021-10-18FALSE303.57311.09302.69303.04362%4.857.522%-0.880%
45Mon 2021-10-25FALSE309.36332.00306.46325.05937%22.0222.647%-2.90-1%
46Mon 2021-11-01FALSE331.36338.79326.37329.41151%4.357.432%-4.99-2%
47Mon 2021-11-08FALSE337.30338.72329.92330.05840%0.651.420%-7.38-2%
48Mon 2021-11-15TRUE337.54345.10334.03336.32192%6.267.562%-3.51-1%
49Mon 2021-11-22FALSE344.62349.67328.12323.7488-4%-12.575.051%-16.50-5%
50Mon 2021-11-29FALSE334.94339.28318.03317.1989-2%-6.554.341%-16.91-5%
51Mon 2021-12-06FALSE323.95343.00319.23336.37756%19.1819.056%-4.72-1%
52Mon 2021-12-13TRUE340.68343.79317.25317.9747-5%-18.403.111%-23.43-7%
53Mon 2021-12-20FALSE320.05336.39317.57328.66883%10.6916.345%-2.48-1%
54Mon 2021-12-27FALSE335.46344.30335.43330.26940%1.608.843%-0.030%
55Mon 2022-01-03FALSE335.35338.00310.09308.3903-7%-21.882.651%-25.26-8%
56Mon 2022-01-10FALSE309.49323.41303.75304.6193-1%-3.7713.925%-5.74-2%
57Mon 2022-01-17TRUE304.07313.91295.61290.7043-5%-13.929.843%-8.46-3%
58Mon 2022-01-24FALSE292.20308.50276.05302.71434%12.0116.306%-16.15-6%
59Mon 2022-01-31FALSE308.95315.12299.96300.4360-1%-2.286.172%-8.99-3%
60Mon 2022-02-07FALSE306.17311.93294.22289.7321-4%-10.705.762%-11.95-4%
61Mon 2022-02-14TRUE293.77300.87286.31282.7499-2%-6.987.102%-7.46-3%
62Mon 2022-02-21FALSE285.00297.63271.52292.56493%9.8112.634%-13.48-5%
63Mon 2022-02-28FALSE294.31303.13287.17285.2338-3%-7.338.823%-7.14-2%
64Mon 2022-03-07FALSE288.53289.69270.00275.6001-3%-9.631.160%-18.53-6%
65Mon 2022-03-14TRUE280.34301.00275.82295.63517%20.0420.667%-4.52-2%
66Mon 2022-03-21FALSE298.89305.50294.90298.83331%3.206.612%-3.99-1%
67Mon 2022-03-28FALSE304.33315.95304.33304.48172%5.6511.624%0.000%
68Mon 2022-04-04FALSE310.09315.11296.28292.2304-4%-12.255.022%-13.81-4%
69Mon 2022-04-11TRUE291.79292.61279.32275.3639-6%-16.870.820%-12.47-4%
70Mon 2022-04-18FALSE278.91293.30273.38269.6565-2%-5.7114.395%-5.53-2%
71Mon 2022-04-25FALSE273.29290.98270.00273.09081%3.4317.696%-3.29-1%
72Mon 2022-05-02FALSE277.71290.88271.27270.3453-1%-2.7513.175%-6.44-2%
73Mon 2022-05-09FALSE270.06273.75250.02256.9525-5%-13.393.691%-20.04-7%
74Mon 2022-05-16TRUE259.96268.33246.44248.5291-3%-8.428.373%-13.52-5%
75Mon 2022-05-23FALSE255.49273.34253.43269.50538%20.9817.857%-2.06-1%
76Mon 2022-05-30FALSE272.53277.69261.60266.3293-1%-3.185.162%-10.93-4%
77Mon 2022-06-06FALSE272.06274.18252.53249.5321-6%-16.802.121%-19.53-7%
78Mon 2022-06-13TRUE245.11255.30241.51244.2651-2%-5.2710.194%-3.60-1%
79Mon 2022-06-20FALSE250.26267.98249.51264.04108%19.7817.727%-0.750%
80Mon 2022-06-27FALSE268.21268.30252.90256.0320-3%-8.010.090%-15.31-6%
81Mon 2022-07-04FALSE256.16269.06254.74264.00163%7.9712.905%-1.42-1%
82Mon 2022-07-11TRUE265.65266.53245.94253.2111-4%-10.790.880%-19.71-7%
83Mon 2022-07-18FALSE259.75265.33253.30256.80141%3.595.582%-6.45-2%
84Mon 2022-07-25FALSE261.00282.00249.57276.90278%20.1021.008%-11.43-4%
85Mon 2022-08-01FALSE277.82283.80272.38279.04321%2.145.982%-5.44-2%
86Mon 2022-08-08FALSE284.05291.91277.61287.92013%8.887.863%-6.44-2%
87Mon 2022-08-15TRUE291.00294.18285.56282.2388-2%-5.683.181%-5.44-2%
88Mon 2022-08-22FALSE282.08282.46267.98264.9870-6%-17.250.380%-14.10-5%
89Mon 2022-08-29FALSE265.85267.40254.47253.0962-4%-11.891.551%-11.38-4%
90Mon 2022-09-05FALSE256.20265.23251.94261.39903%8.309.034%-4.26-2%
91Mon 2022-09-12TRUE265.78267.45242.06241.9073-7%-19.491.671%-23.72-9%
92Mon 2022-09-19FALSE242.47247.66235.20235.1662-3%-6.745.192%-7.27-3%
93Mon 2022-09-26FALSE237.05242.33232.73230.2043-2%-4.965.282%-4.32-2%
94Mon 2022-10-03FALSE235.41250.58233.17231.52881%1.3215.176%-2.24-1%
95Mon 2022-10-10FALSE233.05237.24219.13225.9145-2%-5.614.192%-13.92-6%
96Mon 2022-10-17TRUE235.82243.93234.29239.31766%13.408.113%-1.53-1%
97Mon 2022-10-24FALSE243.76251.04225.78233.1399-3%-6.187.283%-17.98-7%
98Mon 2022-10-31FALSE233.76235.74213.43218.8275-6%-14.311.981%-20.33-9%
99Mon 2022-11-07FALSE221.99247.99221.28244.249812%25.4226.0012%-0.710%
100
101274.18280.43266.59267.59
102
103
104
1053rd FridayTrue
106
107Average of OpenAverage of HighAverage of LowAverage of Close
108274.1795455280.4290909266.5904545267.5913318
Sheet4
Cell Formulas
RangeFormula
B2:B99B2=AND((DAY((A2)+4)<=21)*(DAY((A2)+4)>=15)*(WEEKDAY((A2)+4,2)=5))
B101:E101B101=SUM((DAY(($A2:$A99)+4)<=21)*(DAY(($A2:$A99)+4)>=15)*(WEEKDAY(($A2:$A99)+4,2)=5)*(C2:C99)) / SUM((DAY(($A2:$A99)+4)<=21)*(DAY(($A2:$A99)+4)>=15)*(WEEKDAY(($A2:$A99)+4,2)=5))
 
Last edited:
Upvote 0
Hi,
I have Excel 2016. Do I need the B2 column showing the True & False? I plugged in the B101 formula under the 'G/L %' column but got a "value" error. I would like the avg of columns G-L for only the True rows.
 
Upvote 0
Regarding column B (True False). I mentioned that I only created that to make the PIVOT Table at the bottom (xl2bb doesn't copy PIVOT table functionality only the cells so you can't tell it is a pivot unless I've stated that).

As far as using the average column formula for your percentage columns. You can not just copy that formula to get your averages unless you actually want the percentage of averages. But, if that is what you want try what is below. But, I suggest you calculate yourself manually to be sure your expectation matches a formula.
I'm not going to guess how to do the row by row calculations. Please post the calculation formulas for cells in columns F-K?

But, Dragging the formulas over seems to work for me. Check your cell references. If the formulas have been altered then debug on your end.

Book1
ABCDEFGHIJK
1DateOpenHighLowCloseG/L %PtsO-HO-H %O-LO-L %
22020-12-28224.45227.18219.68216.60292.73-4.77
32021-01-04222.53223211.94213.8761-1.26%-2.730.470.21%-10.59-4.76%
42021-01-11218.47218.91212.03207.0884-3.17%-6.790.440.20%-6.44-2.95%
52021-01-18213.75230.07212.63220.04066.25%12.9516.327.64%-1.12-0.52%
62021-01-25229.12242.64224.22225.89342.66%5.8513.525.90%-4.9-2.14%
72021-02-01235.06245.09232.43235.86564.41%9.9710.034.27%-2.63-1.12%
82021-02-08243.15245.92240.81238.58261.15%2.722.771.14%-2.34-0.96%
92021-02-15245.03246.13240.18234.6677-1.64%-3.911.10.45%-4.85-1.98%
102021-02-22237.42237.93227.88226.8236-3.34%-7.840.510.21%-9.54-4.02%
112021-03-01235.9237.47224.26226.0623-0.34%-0.761.570.67%-11.64-4.93%
122021-03-08231.37239.17227.13230.11311.79%4.057.83.37%-4.24-1.83%
132021-03-15234.96240.06229.35224.8422-2.29%-5.275.12.17%-5.61-2.39%
142021-03-22230.27241.05230.14230.82562.66%5.9810.784.68%-0.13-0.06%
152021-03-29236.59242.84231.1236.55532.48%5.736.252.64%-5.49-2.32%
162021-04-05242.76255.99242.7249.73255.57%13.1813.235.45%-0.06-0.02%
172021-04-12254.71261254.62254.50551.91%4.776.292.47%-0.09-0.04%
182021-04-19260.19261.78255.64254.90570.16%0.41.590.61%-4.55-1.75%
192021-04-26261.66263.19249246.1502-3.43%-8.761.530.58%-12.66-4.84%
202021-05-03253.4254.35244.69246.42350.11%0.270.950.37%-8.71-3.44%
212021-05-10250.87251.73238.07242.2166-1.71%-4.210.860.34%-12.8-5.10%
222021-05-17246.55248.33238.6239.3078-1.20%-2.911.780.72%-7.95-3.22%
232021-05-24247.79252.94247.51244.27272.07%4.965.152.08%-0.28-0.11%
242021-05-31251.23251.65243245.35870.44%1.090.420.17%-8.23-3.28%
252021-06-07249.98258.49249.81252.30492.83%6.958.513.40%-0.17-0.07%
262021-06-14257.9262.3254.42253.81160.60%1.514.41.71%-3.48-1.35%
272021-06-21259.82267.85257.92259.28052.15%5.478.033.09%-1.9-0.73%
282021-06-28266.19278265.91271.6374.77%12.3611.814.44%-0.28-0.11%
292021-07-05278.03280.69274.3271.92070.10%0.282.660.96%-3.73-1.34%
302021-07-12279.16284.1276.58274.66991.01%2.754.941.77%-2.58-0.92%
312021-07-19278.93289.99274.45283.39673.18%8.7311.063.97%-4.48-1.61%
322021-07-26289290.15282.95278.7398-1.64%-4.661.150.40%-6.05-2.09%
332021-08-02286.36289.63283.74283.19121.60%4.453.271.14%-2.62-0.91%
342021-08-09289.75292.9285.2286.50781.17%3.323.151.09%-4.55-1.57%
352021-08-16293.19305.84288.64297.76853.93%11.2612.654.31%-4.55-1.55%
362021-08-23303.25305.65296.83293.7904-1.34%-3.982.40.79%-6.42-2.12%
372021-08-30301.12305.19300.18295.18230.47%1.394.071.35%-0.94-0.31%
382021-09-06301.01302.14295.38289.8597-1.80%-5.321.130.38%-5.63-1.87%
392021-09-13297.55305.32294.08293.93741.41%4.087.772.61%-3.47-1.17%
402021-09-20296.33300.9289.52293.4277-0.17%-0.514.571.54%-6.81-2.30%
412021-09-27296.14296.47281.29283.3805-3.42%-10.050.330.11%-14.85-5.01%
422021-10-04287.4296.64280.25289.01681.99%5.649.243.22%-7.15-2.49%
432021-10-11292.92304.45292.35298.19153.17%9.1711.533.94%-0.57-0.19%
442021-10-18303.57311.09302.69303.04361.63%4.857.522.48%-0.88-0.29%
452021-10-25309.36332306.46325.05937.26%22.0222.647.32%-2.9-0.94%
462021-11-01331.36338.79326.37329.41151.34%4.357.432.24%-4.99-1.51%
472021-11-08337.3338.72329.92330.05840.20%0.651.420.42%-7.38-2.19%
482021-11-15337.54345.1334.03336.32191.90%6.267.562.24%-3.51-1.04%
492021-11-22344.62349.67328.12323.7488-3.74%-12.575.051.47%-16.5-4.79%
502021-11-29334.94339.28318.03317.1989-2.02%-6.554.341.30%-16.91-5.05%
512021-12-06323.95343319.23336.37756.05%19.1819.055.88%-4.72-1.46%
522021-12-13340.68343.79317.25317.9747-5.47%-18.43.110.91%-23.43-6.88%
532021-12-20320.05336.39317.57328.66883.36%10.6916.345.11%-2.48-0.77%
542021-12-27335.46344.3335.43330.26940.49%1.68.842.64%-0.03-0.01%
552022-01-03335.35338310.09308.3903-6.62%-21.882.650.79%-25.26-7.53%
562022-01-10309.49323.41303.75304.6193-1.22%-3.7713.924.50%-5.74-1.85%
572022-01-17304.07313.91295.61290.7043-4.57%-13.929.843.24%-8.46-2.78%
582022-01-24292.2308.5276.05302.71434.13%12.0116.35.58%-16.15-5.53%
592022-01-31308.95315.12299.96300.436-0.75%-2.286.172.00%-8.99-2.91%
602022-02-07306.17311.93294.22289.7321-3.56%-10.75.761.88%-11.95-3.90%
612022-02-14293.77300.87286.31282.7499-2.41%-6.987.12.42%-7.46-2.54%
622022-02-21285297.63271.52292.56493.47%9.8112.634.43%-13.48-4.73%
632022-02-28294.31303.13287.17285.2338-2.51%-7.338.823.00%-7.14-2.43%
642022-03-07288.53289.69270275.6001-3.38%-9.631.160.40%-18.53-6.42%
652022-03-14280.34301275.82295.63517.27%20.0420.667.37%-4.52-1.61%
662022-03-21298.89305.5294.9298.83331.08%3.26.612.21%-3.99-1.33%
672022-03-28304.33315.95304.33304.48171.89%5.6511.623.82%00.00%
682022-04-04310.09315.11296.28292.2304-4.02%-12.255.021.62%-13.81-4.45%
692022-04-11291.79292.61279.32275.3639-5.77%-16.870.820.28%-12.47-4.27%
702022-04-18278.91293.3273.38269.6565-2.07%-5.7114.395.16%-5.53-1.98%
712022-04-25273.29290.98270273.09081.27%3.4317.696.47%-3.29-1.20%
722022-05-02277.71290.88271.27270.3453-1.01%-2.7513.174.74%-6.44-2.32%
732022-05-09270.06273.75250.02256.9525-4.95%-13.393.691.37%-20.04-7.42%
742022-05-16259.96268.33246.44248.5291-3.28%-8.428.373.22%-13.52-5.20%
752022-05-23255.49273.34253.43269.50538.44%20.9817.856.99%-2.06-0.81%
762022-05-30272.53277.69261.6266.3293-1.18%-3.185.161.89%-10.93-4.01%
772022-06-06272.06274.18252.53249.5321-6.31%-16.82.120.78%-19.53-7.18%
782022-06-13245.11255.3241.51244.2651-2.11%-5.2710.194.16%-3.6-1.47%
792022-06-20250.26267.98249.51264.0418.10%19.7817.727.08%-0.75-0.30%
802022-06-27268.21268.3252.9256.032-3.03%-8.010.090.03%-15.31-5.71%
812022-07-04256.16269.06254.74264.00163.11%7.9712.95.04%-1.42-0.55%
822022-07-11265.65266.53245.94253.2111-4.09%-10.790.880.33%-19.71-7.42%
832022-07-18259.75265.33253.3256.80141.42%3.595.582.15%-6.45-2.48%
842022-07-25261282249.57276.90277.83%20.1218.05%-11.43-4.38%
852022-08-01277.82283.8272.38279.04320.77%2.145.982.15%-5.44-1.96%
862022-08-08284.05291.91277.61287.92013.18%8.887.862.77%-6.44-2.27%
872022-08-15291294.18285.56282.2388-1.97%-5.683.181.09%-5.44-1.87%
882022-08-22282.08282.46267.98264.987-6.11%-17.250.380.13%-14.1-5.00%
892022-08-29265.85267.4254.47253.0962-4.49%-11.891.550.58%-11.38-4.28%
902022-09-05256.2265.23251.94261.3993.28%8.39.033.52%-4.26-1.66%
912022-09-12265.78267.45242.06241.9073-7.46%-19.491.670.63%-23.72-8.92%
922022-09-19242.47247.66235.2235.1662-2.79%-6.745.192.14%-7.27-3.00%
932022-09-26237.05242.33232.73230.2043-2.11%-4.965.282.23%-4.32-1.82%
942022-10-03235.41250.58233.17231.52880.58%1.3215.176.44%-2.24-0.95%
952022-10-10233.05237.24219.13225.9145-2.42%-5.614.191.80%-13.92-5.97%
962022-10-17235.82243.93234.29239.31765.93%13.48.113.44%-1.53-0.65%
972022-10-24243.76251.04225.78233.1399-2.58%-6.187.282.99%-17.98-7.38%
982022-10-31233.76235.74213.43218.8275-6.14%-14.311.980.85%-20.33-8.70%
992022-11-07221.99247.99221.28244.249811.62%25.422611.71%-0.71-0.32%
100274.18280.43266.59267.59-0.83%-2.346.252.26%-7.59-2.75%
Sheet1
Cell Formulas
RangeFormula
B100:K100B100=SUM((DAY(($A2:$A99)+4)<=21)*(DAY(($A2:$A99)+4)>=15)*(WEEKDAY(($A2:$A99)+4,2)=5)*(B2:B99)) / SUM((DAY(($A2:$A99)+4)<=21)*(DAY(($A2:$A99)+4)>=15)*(WEEKDAY(($A2:$A99)+4,2)=5))
 
Upvote 0
For the life of me, I can't figure out why I'm still getting the "Value" error since I copied your formula. I thought at first, it's the way I formatted the "Date" but I still get the same error when I changed it to your format.

Here's the formula: =SUM((DAY(($A2:$A99)+4)<=21)*(DAY(($A2:$A99)+4)>=15)*(WEEKDAY(($A2:$A99)+4,2)=5)*(B2:B99)) / SUM((DAY(($A2:$A99)+4)<=21)*(DAY(($A2:$A99)+4)>=15)*(WEEKDAY(($A2:$A99)+4,2)=5))

DateOpenHighLowCloseAdj CloseVolumeG/L %PtsPts ABSUpW StreakO-HO-H %O-LO-L %
12/28/20​
224.45​
227.18​
219.68​
222.42​
216.6029​
76551100​
2.73-4.77
1/4/21​
222.53​
223​
211.94​
219.62​
213.8761​
1.48E+08​
-1.26%-2.732.73000.470.21%-10.59-4.76%
1/11/21​
218.47​
218.91​
212.03​
212.65​
207.0884​
1.28E+08​
-3.17%-6.796.79000.440.20%-6.44-2.95%
1/18/21​
213.75​
230.07​
212.63​
225.95​
220.0406​
1.29E+08​
6.25%12.9512.951116.327.64%-1.12-0.52%
1/25/21​
229.12​
242.64​
224.22​
231.96​
225.8934​
2.44E+08​
2.66%5.855.851213.525.90%-4.90-2.14%
2/1/21​
235.06​
245.09​
232.43​
242.2​
235.8656​
1.3E+08​
4.41%9.979.971310.034.27%-2.63-1.12%
2/8/21​
243.15​
245.92​
240.81​
244.99​
238.5826​
1E+08​
1.15%2.722.72142.771.14%-2.34-0.96%
2/15/21​
245.03​
246.13​
240.18​
240.97​
234.6677​
90570200​
-1.64%-3.913.91001.100.45%-4.85-1.98%
2/22/21​
237.42​
237.93​
227.88​
232.38​
226.8236​
1.7E+08​
-3.34%-7.847.84000.510.21%-9.54-4.02%
3/1/21​
235.9​
237.47​
224.26​
231.6​
226.0623​
1.69E+08​
-0.34%-0.760.76001.570.67%-11.64-4.93%
3/8/21​
231.37​
239.17​
227.13​
235.75​
230.1131​
1.51E+08​
1.79%4.054.05117.803.37%-4.24-1.83%
3/15/21​
234.96​
240.06​
229.35​
230.35​
224.8422​
1.65E+08​
-2.29%-5.275.27005.102.17%-5.61-2.39%
3/22/21​
230.27​
241.05​
230.14​
236.48​
230.8256​
1.47E+08​
2.66%5.985.981110.784.68%-0.13-0.06%
3/29/21​
236.59​
242.84​
231.1​
242.35​
236.5553​
1.24E+08​
2.48%5.735.73126.252.64%-5.49-2.32%
4/5/21​
242.76​
255.99​
242.7​
255.85​
249.7325​
1.31E+08​
5.57%13.1813.181313.235.45%-0.06-0.02%
4/12/21​
254.71​
261​
254.62​
260.74​
254.5055​
1.25E+08​
1.91%4.774.77146.292.47%-0.09-0.04%
4/19/21​
260.19​
261.78​
255.64​
261.15​
254.9057​
1.14E+08​
0.16%0.400.40151.590.61%-4.55-1.75%
4/26/21​
261.66​
263.19​
249​
252.18​
246.1502​
1.69E+08​
-3.43%-8.768.76001.530.58%-12.66-4.84%
5/3/21​
253.4​
254.35​
244.69​
252.46​
246.4235​
1.28E+08​
0.11%0.270.27110.950.37%-8.71-3.44%
5/10/21​
250.87​
251.73​
238.07​
248.15​
242.2166​
1.53E+08​
-1.71%-4.214.21000.860.34%-12.80-5.10%
5/17/21​
246.55​
248.33​
238.6​
245.17​
239.3078​
1.15E+08​
-1.20%-2.912.91001.780.72%-7.95-3.22%
5/24/21​
247.79​
252.94​
247.51​
249.68​
244.2727​
99583800​
2.07%4.964.96115.152.08%-0.28-0.11%
5/31/21​
251.23​
251.65​
243​
250.79​
245.3587​
93208800​
0.44%1.091.09120.420.17%-8.23-3.28%
6/7/21​
249.98​
258.49​
249.81​
257.89​
252.3049​
1.07E+08​
2.83%6.956.95138.513.40%-0.17-0.07%
6/14/21​
257.9​
262.3​
254.42​
259.43​
253.8116​
1.29E+08​
0.60%1.511.51144.401.71%-3.48-1.35%
6/21/21​
259.82​
267.85​
257.92​
265.02​
259.2805​
1.18E+08​
2.15%5.475.47158.033.09%-1.90-0.73%
6/28/21​
266.19​
278​
265.91​
277.65​
271.637​
1.04E+08​
4.77%12.3612.361611.814.44%-0.28-0.11%
7/5/21​
278.03​
280.69​
274.3​
277.94​
271.9207​
1.03E+08​
0.10%0.280.28172.660.96%-3.73-1.34%
7/12/21​
279.16​
284.1​
276.58​
280.75​
274.6699​
1.17E+08​
1.01%2.752.75184.941.77%-2.58-0.92%
7/19/21​
278.93​
289.99​
274.45​
289.67​
283.3967​
1.3E+08​
3.18%8.738.731911.063.97%-4.48-1.61%
7/26/21​
289​
290.15​
282.95​
284.91​
278.7398​
1.29E+08​
-1.64%-4.664.66001.150.40%-6.05-2.09%
8/2/21​
286.36​
289.63​
283.74​
289.46​
283.1912​
80827200​
1.60%4.454.45113.271.14%-2.62-0.91%
8/9/21​
289.75​
292.9​
285.2​
292.85​
286.5078​
81500400​
1.17%3.323.32123.151.09%-4.55-1.57%
8/16/21​
293.19​
305.84​
288.64​
304.36​
297.7685​
1.35E+08​
3.93%11.2611.261312.654.31%-4.55-1.55%
8/23/21​
303.25​
305.65​
296.83​
299.72​
293.7904​
1.01E+08​
-1.34%-3.983.98002.400.79%-6.42-2.12%
8/30/21​
301.12​
305.19​
300.18​
301.14​
295.1823​
92650700​
0.47%1.391.39114.071.35%-0.94-0.31%
9/6/21​
301.01​
302.14​
295.38​
295.71​
289.8597​
71787600​
-1.80%-5.325.32001.130.38%-5.63-1.87%
9/13/21​
297.55​
305.32​
294.08​
299.87​
293.9374​
1.35E+08​
1.41%4.084.08117.772.61%-3.47-1.17%
9/20/21​
296.33​
300.9​
289.52​
299.35​
293.4277​
1.21E+08​
-0.17%-0.510.51004.571.54%-6.81-2.30%
9/27/21​
296.14​
296.47​
281.29​
289.1​
283.3805​
1.56E+08​
-3.42%-10.0510.05000.330.11%-14.85-5.01%
10/4/21​
287.4​
296.64​
280.25​
294.85​
289.0168​
1.22E+08​
1.99%5.645.64119.243.22%-7.15-2.49%
10/11/21​
292.92​
304.45​
292.35​
304.21​
298.1915​
1.13E+08​
3.17%9.179.171211.533.94%-0.57-0.19%
10/18/21​
303.57​
311.09​
302.69​
309.16​
303.0436​
91315900​
1.63%4.854.85137.522.48%-0.88-0.29%
10/25/21​
309.36​
332​
306.46​
331.62​
325.0593​
1.59E+08​
7.26%22.0222.021422.647.32%-2.90-0.94%
11/1/21​
331.36​
338.79​
326.37​
336.06​
329.4115​
1.22E+08​
1.34%4.354.35157.432.24%-4.99-1.51%
11/8/21​
337.3​
338.72​
329.92​
336.72​
330.0584​
1.08E+08​
0.20%0.650.65161.420.42%-7.38-2.19%
11/15/21​
337.54​
345.1​
334.03​
343.11​
336.3219​
1.01E+08​
1.90%6.266.26177.562.24%-3.51-1.04%
11/22/21​
344.62​
349.67​
328.12​
329.68​
323.7488​
1.07E+08​
-3.74%-12.5712.57005.051.47%-16.50-4.79%
11/29/21​
334.94​
339.28​
318.03​
323.01​
317.1989​
1.77E+08​
-2.02%-6.556.55004.341.30%-16.91-5.05%
12/6/21​
323.95​
343​
319.23​
342.54​
336.3775​
1.46E+08​
6.05%19.1819.181119.055.88%-4.72-1.46%
12/13/21​
340.68​
343.79​
317.25​
323.8​
317.9747​
1.92E+08​
-5.47%-18.4018.40003.110.91%-23.43-6.88%
12/20/21​
320.05​
336.39​
317.57​
334.69​
328.6688​
97516400​
3.36%10.6910.691116.345.11%-2.48-0.77%
12/27/21​
335.46​
344.3​
335.43​
336.32​
330.2694​
84645800​
0.49%1.601.60128.842.64%-0.03-0.01%
1/3/22​
335.35​
338​
310.09​
314.04​
308.3903​
1.74E+08​
-6.62%-21.8821.88002.650.79%-25.26-7.53%
1/10/22​
309.49​
323.41​
303.75​
310.2​
304.6193​
1.93E+08​
-1.22%-3.773.770013.924.50%-5.74-1.85%
1/17/22​
304.07​
313.91​
295.61​
296.03​
290.7043​
1.82E+08​
-4.57%-13.9213.92009.843.24%-8.46-2.78%
1/24/22​
292.2​
308.5​
276.05​
308.26​
302.7143​
3.52E+08​
4.13%12.0112.011116.305.58%-16.15-5.53%
1/31/22​
308.95​
315.12​
299.96​
305.94​
300.436​
2.03E+08​
-0.75%-2.282.28006.172.00%-8.99-2.91%
2/7/22​
306.17​
311.93​
294.22​
295.04​
289.7321​
1.77E+08​
-3.56%-10.7010.70005.761.88%-11.95-3.90%
2/14/22​
293.77​
300.87​
286.31​
287.93​
282.7499​
1.6E+08​
-2.41%-6.986.98007.102.42%-7.46-2.54%
2/21/22​
285​
297.63​
271.52​
297.31​
292.5649​
1.69E+08​
3.47%9.819.811112.634.43%-13.48-4.73%
2/28/22​
294.31​
303.13​
287.17​
289.86​
285.2338​
1.57E+08​
-2.51%-7.337.33008.823.00%-7.14-2.43%
3/7/22​
288.53​
289.69​
270​
280.07​
275.6001​
1.84E+08​
-3.38%-9.639.63001.160.40%-18.53-6.42%
3/14/22​
280.34​
301​
275.82​
300.43​
295.6351​
1.77E+08​
7.27%20.0420.041120.667.37%-4.52-1.61%
3/21/22​
298.89​
305.5​
294.9​
303.68​
298.8333​
1.29E+08​
1.08%3.203.20126.612.21%-3.99-1.33%
3/28/22​
304.33​
315.95​
304.33​
309.42​
304.4817​
1.49E+08​
1.89%5.655.651311.623.82%0.000.00%
4/4/22​
310.09​
315.11​
296.28​
296.97​
292.2304​
1.43E+08​
-4.02%-12.2512.25005.021.62%-13.81-4.45%
4/11/22​
291.79​
292.61​
279.32​
279.83​
275.3639​
1.16E+08​
-5.77%-16.8716.87000.820.28%-12.47-4.27%
4/18/22​
278.91​
293.3​
273.38​
274.03​
269.6565​
1.25E+08​
-2.07%-5.715.710014.395.16%-5.53-1.98%
4/25/22​
273.29​
290.98​
270​
277.52​
273.0908​
2.16E+08​
1.27%3.433.431117.696.47%-3.29-1.20%
5/2/22​
277.71​
290.88​
271.27​
274.73​
270.3453​
1.76E+08​
-1.01%-2.752.750013.174.74%-6.44-2.32%
5/9/22​
270.06​
273.75​
250.02​
261.12​
256.9525​
2.22E+08​
-4.95%-13.3913.39003.691.37%-20.04-7.42%
5/16/22​
259.96​
268.33​
246.44​
252.56​
248.5291​
1.65E+08​
-3.28%-8.428.42008.373.22%-13.52-5.20%
5/23/22​
255.49​
273.34​
253.43​
273.24​
269.5053​
1.43E+08​
8.44%20.9820.981117.856.99%-2.06-0.81%
5/30/22​
272.53​
277.69​
261.6​
270.02​
266.3293​
1.35E+08​
-1.18%-3.183.18005.161.89%-10.93-4.01%
6/6/22​
272.06​
274.18​
252.53​
252.99​
249.5321​
1.2E+08​
-6.31%-16.8016.80002.120.78%-19.53-7.18%
6/13/22​
245.11​
255.3​
241.51​
247.65​
244.2651​
1.84E+08​
-2.11%-5.275.270010.194.16%-3.60-1.47%
6/20/22​
250.26​
267.98​
249.51​
267.7​
264.041​
1.16E+08​
8.10%19.7819.781117.727.08%-0.75-0.30%
6/27/22​
268.21​
268.3​
252.9​
259.58​
256.032​
1.27E+08​
-3.03%-8.018.01000.090.03%-15.31-5.71%
7/4/22​
256.16​
269.06​
254.74​
267.66​
264.0016​
87284100​
3.11%7.977.971112.905.04%-1.42-0.55%
7/11/22​
265.65​
266.53​
245.94​
256.72​
253.2111​
1.4E+08​
-4.09%-10.7910.79000.880.33%-19.71-7.42%
7/18/22​
259.75​
265.33​
253.3​
260.36​
256.8014​
1.13E+08​
1.42%3.593.59115.582.15%-6.45-2.48%
7/25/22​
261​
282​
249.57​
280.74​
276.9027​
1.72E+08​
7.83%20.1020.101221.008.05%-11.43-4.38%
8/1/22​
277.82​
283.8​
272.38​
282.91​
279.0432​
1.03E+08​
0.77%2.142.14135.982.15%-5.44-1.96%
8/8/22​
284.05​
291.91​
277.61​
291.91​
287.9201​
1.1E+08​
3.18%8.888.88147.862.77%-6.44-2.27%
8/15/22​
291​
294.18​
285.56​
286.15​
282.2388​
92198200​
-1.97%-5.685.68003.181.09%-5.44-1.87%
8/22/22​
282.08​
282.46​
267.98​
268.09​
264.987​
1.05E+08​
-6.11%-17.2517.25000.380.13%-14.10-5.00%
8/29/22​
265.85​
267.4​
254.47​
256.06​
253.0962​
1.14E+08​
-4.49%-11.8911.89001.550.58%-11.38-4.28%
9/5/22​
256.2​
265.23​
251.94​
264.46​
261.399​
87859500​
3.28%8.308.30119.033.52%-4.26-1.66%
9/12/22​
265.78​
267.45​
242.06​
244.74​
241.9073​
1.47E+08​
-7.46%-19.4919.49001.670.63%-23.72-8.92%
9/19/22​
242.47​
247.66​
235.2​
237.92​
235.1662​
1.47E+08​
-2.79%-6.746.74005.192.14%-7.27-3.00%
9/26/22​
237.05​
242.33​
232.73​
232.9​
230.2043​
1.47E+08​
-2.11%-4.964.96005.282.23%-4.32-1.82%
10/3/22​
235.41​
250.58​
233.17​
234.24​
231.5288​
1.42E+08​
0.58%1.321.321115.176.44%-2.24-0.95%
10/10/22​
233.05​
237.24​
219.13​
228.56​
225.9145​
1.55E+08​
-2.42%-5.615.61004.191.80%-13.92-5.97%
10/17/22​
235.82​
243.93​
234.29​
242.12​
239.3176​
1.23E+08​
5.93%13.4013.40118.113.44%-1.53-0.65%
10/24/22​
243.76​
251.04​
225.78​
235.87​
233.1399​
2.23E+08​
-2.58%-6.186.18007.282.99%-17.98-7.38%
10/31/22​
233.76​
235.74​
213.43​
221.39​
218.8275​
1.71E+08​
-6.14%-14.3114.31001.980.85%-20.33-8.70%
11/7/22​
221.99​
247.99​
221.28​
247.11​
244.2498​
1.7E+08​
11.62%25.4225.421126.0011.71%-0.71-0.32%
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
#VALUE!​
 
Upvote 0
Posting a table really does not help me debug.

Try posting the solution I gave you in a BRAND NEW WORKBOOK. Do not change a thing. If the error still persists, then you really need to post the formulas and column and row heading.
 
Upvote 0
It's still giving me the "Value" error in a new workbook. I downloaded XL2BB but is says "this file is not supported in protected view". I was hoping I could get it to you to troubleshoot it. I have the formula in B100.
1705002318206.png
 
Upvote 0
One suggestion that may work since you don't say what version of excel you use (Nevermind, you answered this earlier, but please update your profile with this so the forum doesn't have to ask you).
Change all of the SUM functions to SUMPRODUCT.

I have done this on the xl2bb mini sheet below.

If that doesn't work, again....

Please post the formula so it can be copied. If there is an error in your formula I could reverse error and get it to work and not know i changed your formula.

You have not addressed the calculation of the values in columns F:K, are my guesses correct? Shouldn't the same formula be used for your thrice weekly averages insteaded of averaging of percentages?

You also seem to have columns in your workbook now that were not in the other. Yet you do not post an image of the mini worksheet I asked you to?
Please don't change your scenario and expect the forum to just figure it out.

Please do what I asked in Post #7. Do not post an image. Post a table if you can't use xl2bb.
... put column and row headers (letters and numbers) on the table.
.... copy and paste the FORMULAS for all the errors you have, and tell what cell the formula is in.

Make no changes, insert no columns.
Do not change formulas.

Thanks in advance.


Book1
ABCDEFGHIJK
1DateOpenHighLowCloseG/L %PtsO-HO-H %O-LO-L %
22020-12-28224.45227.18219.68216.60292.73-4.77
32021-01-04222.53223.00211.94213.8761-1.26%-2.730.470.21%-10.59-4.76%
42021-01-11218.47218.91212.03207.0884-3.17%-6.790.440.20%-6.44-2.95%
52021-01-18213.75230.07212.63220.04066.25%12.9516.327.64%-1.12-0.52%
62021-01-25229.12242.64224.22225.89342.66%5.8513.525.90%-4.90-2.14%
72021-02-01235.06245.09232.43235.86564.41%9.9710.034.27%-2.63-1.12%
82021-02-08243.15245.92240.81238.58261.15%2.722.771.14%-2.34-0.96%
92021-02-15245.03246.13240.18234.6677-1.64%-3.911.100.45%-4.85-1.98%
102021-02-22237.42237.93227.88226.8236-3.34%-7.840.510.21%-9.54-4.02%
112021-03-01235.90237.47224.26226.0623-0.34%-0.761.570.67%-11.64-4.93%
122021-03-08231.37239.17227.13230.11311.79%4.057.803.37%-4.24-1.83%
132021-03-15234.96240.06229.35224.8422-2.29%-5.275.102.17%-5.61-2.39%
142021-03-22230.27241.05230.14230.82562.66%5.9810.784.68%-0.13-0.06%
152021-03-29236.59242.84231.10236.55532.48%5.736.252.64%-5.49-2.32%
162021-04-05242.76255.99242.70249.73255.57%13.1813.235.45%-0.06-0.02%
172021-04-12254.71261.00254.62254.50551.91%4.776.292.47%-0.09-0.04%
182021-04-19260.19261.78255.64254.90570.16%0.401.590.61%-4.55-1.75%
192021-04-26261.66263.19249.00246.1502-3.43%-8.761.530.58%-12.66-4.84%
202021-05-03253.40254.35244.69246.42350.11%0.270.950.37%-8.71-3.44%
212021-05-10250.87251.73238.07242.2166-1.71%-4.210.860.34%-12.80-5.10%
222021-05-17246.55248.33238.60239.3078-1.20%-2.911.780.72%-7.95-3.22%
232021-05-24247.79252.94247.51244.27272.07%4.965.152.08%-0.28-0.11%
242021-05-31251.23251.65243.00245.35870.44%1.090.420.17%-8.23-3.28%
252021-06-07249.98258.49249.81252.30492.83%6.958.513.40%-0.17-0.07%
262021-06-14257.90262.30254.42253.81160.60%1.514.401.71%-3.48-1.35%
272021-06-21259.82267.85257.92259.28052.15%5.478.033.09%-1.90-0.73%
282021-06-28266.19278.00265.91271.63704.77%12.3611.814.44%-0.28-0.11%
292021-07-05278.03280.69274.30271.92070.10%0.282.660.96%-3.73-1.34%
302021-07-12279.16284.10276.58274.66991.01%2.754.941.77%-2.58-0.92%
312021-07-19278.93289.99274.45283.39673.18%8.7311.063.97%-4.48-1.61%
322021-07-26289.00290.15282.95278.7398-1.64%-4.661.150.40%-6.05-2.09%
332021-08-02286.36289.63283.74283.19121.60%4.453.271.14%-2.62-0.91%
342021-08-09289.75292.90285.20286.50781.17%3.323.151.09%-4.55-1.57%
352021-08-16293.19305.84288.64297.76853.93%11.2612.654.31%-4.55-1.55%
362021-08-23303.25305.65296.83293.7904-1.34%-3.982.400.79%-6.42-2.12%
372021-08-30301.12305.19300.18295.18230.47%1.394.071.35%-0.94-0.31%
382021-09-06301.01302.14295.38289.8597-1.80%-5.321.130.38%-5.63-1.87%
392021-09-13297.55305.32294.08293.93741.41%4.087.772.61%-3.47-1.17%
402021-09-20296.33300.90289.52293.4277-0.17%-0.514.571.54%-6.81-2.30%
412021-09-27296.14296.47281.29283.3805-3.42%-10.050.330.11%-14.85-5.01%
422021-10-04287.40296.64280.25289.01681.99%5.649.243.22%-7.15-2.49%
432021-10-11292.92304.45292.35298.19153.17%9.1711.533.94%-0.57-0.19%
442021-10-18303.57311.09302.69303.04361.63%4.857.522.48%-0.88-0.29%
452021-10-25309.36332.00306.46325.05937.26%22.0222.647.32%-2.90-0.94%
462021-11-01331.36338.79326.37329.41151.34%4.357.432.24%-4.99-1.51%
472021-11-08337.30338.72329.92330.05840.20%0.651.420.42%-7.38-2.19%
482021-11-15337.54345.10334.03336.32191.90%6.267.562.24%-3.51-1.04%
492021-11-22344.62349.67328.12323.7488-3.74%-12.575.051.47%-16.50-4.79%
502021-11-29334.94339.28318.03317.1989-2.02%-6.554.341.30%-16.91-5.05%
512021-12-06323.95343.00319.23336.37756.05%19.1819.055.88%-4.72-1.46%
522021-12-13340.68343.79317.25317.9747-5.47%-18.403.110.91%-23.43-6.88%
532021-12-20320.05336.39317.57328.66883.36%10.6916.345.11%-2.48-0.77%
542021-12-27335.46344.30335.43330.26940.49%1.608.842.64%-0.03-0.01%
552022-01-03335.35338.00310.09308.3903-6.62%-21.882.650.79%-25.26-7.53%
562022-01-10309.49323.41303.75304.6193-1.22%-3.7713.924.50%-5.74-1.85%
572022-01-17304.07313.91295.61290.7043-4.57%-13.929.843.24%-8.46-2.78%
582022-01-24292.20308.50276.05302.71434.13%12.0116.305.58%-16.15-5.53%
592022-01-31308.95315.12299.96300.4360-0.75%-2.286.172.00%-8.99-2.91%
602022-02-07306.17311.93294.22289.7321-3.56%-10.705.761.88%-11.95-3.90%
612022-02-14293.77300.87286.31282.7499-2.41%-6.987.102.42%-7.46-2.54%
622022-02-21285.00297.63271.52292.56493.47%9.8112.634.43%-13.48-4.73%
632022-02-28294.31303.13287.17285.2338-2.51%-7.338.823.00%-7.14-2.43%
642022-03-07288.53289.69270.00275.6001-3.38%-9.631.160.40%-18.53-6.42%
652022-03-14280.34301.00275.82295.63517.27%20.0420.667.37%-4.52-1.61%
662022-03-21298.89305.50294.90298.83331.08%3.206.612.21%-3.99-1.33%
672022-03-28304.33315.95304.33304.48171.89%5.6511.623.82%0.000.00%
682022-04-04310.09315.11296.28292.2304-4.02%-12.255.021.62%-13.81-4.45%
692022-04-11291.79292.61279.32275.3639-5.77%-16.870.820.28%-12.47-4.27%
702022-04-18278.91293.30273.38269.6565-2.07%-5.7114.395.16%-5.53-1.98%
712022-04-25273.29290.98270.00273.09081.27%3.4317.696.47%-3.29-1.20%
722022-05-02277.71290.88271.27270.3453-1.01%-2.7513.174.74%-6.44-2.32%
732022-05-09270.06273.75250.02256.9525-4.95%-13.393.691.37%-20.04-7.42%
742022-05-16259.96268.33246.44248.5291-3.28%-8.428.373.22%-13.52-5.20%
752022-05-23255.49273.34253.43269.50538.44%20.9817.856.99%-2.06-0.81%
762022-05-30272.53277.69261.60266.3293-1.18%-3.185.161.89%-10.93-4.01%
772022-06-06272.06274.18252.53249.5321-6.31%-16.802.120.78%-19.53-7.18%
782022-06-13245.11255.30241.51244.2651-2.11%-5.2710.194.16%-3.60-1.47%
792022-06-20250.26267.98249.51264.04108.10%19.7817.727.08%-0.75-0.30%
802022-06-27268.21268.30252.90256.0320-3.03%-8.010.090.03%-15.31-5.71%
812022-07-04256.16269.06254.74264.00163.11%7.9712.905.04%-1.42-0.55%
822022-07-11265.65266.53245.94253.2111-4.09%-10.790.880.33%-19.71-7.42%
832022-07-18259.75265.33253.30256.80141.42%3.595.582.15%-6.45-2.48%
842022-07-25261.00282.00249.57276.90277.83%20.1021.008.05%-11.43-4.38%
852022-08-01277.82283.80272.38279.04320.77%2.145.982.15%-5.44-1.96%
862022-08-08284.05291.91277.61287.92013.18%8.887.862.77%-6.44-2.27%
872022-08-15291.00294.18285.56282.2388-1.97%-5.683.181.09%-5.44-1.87%
882022-08-22282.08282.46267.98264.9870-6.11%-17.250.380.13%-14.10-5.00%
892022-08-29265.85267.40254.47253.0962-4.49%-11.891.550.58%-11.38-4.28%
902022-09-05256.20265.23251.94261.39903.28%8.309.033.52%-4.26-1.66%
912022-09-12265.78267.45242.06241.9073-7.46%-19.491.670.63%-23.72-8.92%
922022-09-19242.47247.66235.20235.1662-2.79%-6.745.192.14%-7.27-3.00%
932022-09-26237.05242.33232.73230.2043-2.11%-4.965.282.23%-4.32-1.82%
942022-10-03235.41250.58233.17231.52880.58%1.3215.176.44%-2.24-0.95%
952022-10-10233.05237.24219.13225.9145-2.42%-5.614.191.80%-13.92-5.97%
962022-10-17235.82243.93234.29239.31765.93%13.408.113.44%-1.53-0.65%
972022-10-24243.76251.04225.78233.1399-2.58%-6.187.282.99%-17.98-7.38%
982022-10-31233.76235.74213.43218.8275-6.14%-14.311.980.85%-20.33-8.70%
992022-11-07221.99247.99221.28244.249811.62%25.4226.0011.71%-0.71-0.32%
100274.18280.43266.59267.5913-0.83%-2.346.252.26%-7.59-2.75%
Sheet1
Cell Formulas
RangeFormula
B100:K100B100=SUMPRODUCT((DAY(($A2:$A99)+4)<=21)*(DAY(($A2:$A99)+4)>=15)*(WEEKDAY(($A2:$A99)+4,2)=5)*(B2:B99)) / SUMPRODUCT((DAY(($A2:$A99)+4)<=21)*(DAY(($A2:$A99)+4)>=15)*(WEEKDAY(($A2:$A99)+4,2)=5))
 
Last edited:
Upvote 0
I downloaded XL2BB but is says "this file is not supported in protected view". I was hoping I could get it to you to troubleshoot it.
in my footer is a link to the xl2bb testing area for the forum. That is the only thing I can tell you. But, you may need to look in your OPTIONS and enable certain add ins that you have saved into your trusted locations. I am not good at troubleshooting how add ins work.
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,179
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top