Calculate Formula for 7 days rolling and then median of that result by day

[PSB]

Step 1: Calculate Formula based on last 7 days data for different "Market"

Each of the value is created with sum of previous 7 days values. Sample in table below.

Forumula column in Date 11/7 should caluculate below value based on data from 11/1 to 11/7

Formula: =(SUM(C2:C8)+SUM(D2:D8)-SUM(E2:E8))/(SUM(F2:F8)+SUM(G2:G8))

Date	Market	A	B	C	D	E	F	Formula
11/1/2024	New Jersey	12	345	33	54	66	442
11/2/2024	New Jersey	34	575	2	244	345	22
11/3/2024	New Jersey	442	23	223	64	41	66
11/4/2024	New Jersey	22	45	44	33	432	345
11/5/2024	New Jersey	66	7	356	2	345	41
11/6/2024	New Jersey	345	54	634	223	575	432
11/7/2024	New Jersey	41	244	546	66	23	345	0.17
11/8/2024	New Jersey	432	64	432	345	45	575	0.06
11/9/2024	New Jersey	345	33	345	41	7	23	-0.19
11/10/2024	New Jersey	575	2	575	432	54	45	-0.25
11/1/2024	Boston	23	223	23	345	244	7
11/2/2024	Boston	45	66	45	575	64	54
11/3/2024	Boston	7	345	7	23	345	244
11/4/2024	Boston	54	41	54	45	575	64
11/5/2024	Boston	244	432	244	66	23	33
11/6/2024	Boston	64	345	223	345	45	2
11/7/2024	Boston	33	575	44	41	7	546	0.68
11/8/2024	Boston	2	23	356	432	23	432	0.50
11/9/2024	Boston	223	45	634	44	45	345	0.42
11/10/2024	Boston	44	7	546	356	7	575	0.02
11/1/2024	NYC	356	23	674	634	54	23
11/2/2024	NYC	634	45	634	546	223	45
11/3/2024	NYC	546	7	546	432	44	7
11/4/2024	NYC	674	54	674	345	356	54
11/5/2024	NYC	345	244	345	575	634	244
11/6/2024	NYC	56	64	56	432	546	223
11/7/2024	NYC	565	33	565	345	432	44	0.03
11/8/2024	NYC	56	2	54	575	345	356	0.08
11/9/2024	NYC	56	223	244	23	575	634	0.08
11/10/2024	NYC	878	44	64	453	23	34	0.23

Step 2: once we have above result.

I need median by date for each day

Date
11/1/2024
11/2/2024
11/3/2024
11/4/2024
11/5/2024
11/6/2024
11/7/2024	0.17
11/8/2024	0.08
11/9/2024	0.08
11/10/2024	0.02

[Ahmedx · Accepted solution]

pls try

[Ahmedx]

and try median

[Ahmedx · Accepted solution]

pls try

[PSB]

STEP 1 is perfactly working but not step 2.

For each market, I have this "NSA_DCR_Median" value, now I need median for this values on daily level. "=MEDIAN(C2:C28)" 

final result for below data should be (this example is only for one day.

PERIOD_START_TIME	NSA_DCR_Median
11/30/2023 0:00	0.62

Data

PERIOD_START_TIME	MARKET	NSA_DCR_Median
11/30/2023 0:00	Albuquerque	1.08
11/30/2023 0:00	Atlanta	0.58
11/30/2023 0:00	Central PA	0.37
11/30/2023 0:00	Connecticut	0.62
11/30/2023 0:00	Hawaii	0.66
11/30/2023 0:00	Jacksonville	0.45
11/30/2023 0:00	LA North	0.91
11/30/2023 0:00	Las Vegas	1.42
11/30/2023 0:00	Long Island	0.60
11/30/2023 0:00	Los Angeles	0.97
11/30/2023 0:00	Miami	0.64
11/30/2023 0:00	New England	0.60
11/30/2023 0:00	New Jersey	0.50
11/30/2023 0:00	New York	0.61
11/30/2023 0:00	North Carolina	0.75
11/30/2023 0:00	Orlando	0.46
11/30/2023 0:00	Philadelphia	0.57
11/30/2023 0:00	Sacramento	0.65
11/30/2023 0:00	Salt Lake City	0.86
11/30/2023 0:00	San Diego	0.69
11/30/2023 0:00	San Francisco	0.64
11/30/2023 0:00	South Carolina	0.57
11/30/2023 0:00	Southern California	1.01
11/30/2023 0:00	Tampa	0.61
11/30/2023 0:00	Upstate NY	0.88
11/30/2023 0:00	Virginia	0.62
11/30/2023 0:00	Washington DC	0.58

[Ahmedx · Accepted solution]

try this

[PSB]

when I tried by calculating it from previous measure, it's not giving correct result.

[Ahmedx]

I'm no longer interested in solving all your problems in this scenario.

[PSB]

thanks for your time and effort. 

[Ahmedx]

WHAT IS THIS AND WHERE IS COLUMN
G2:G8

[PSB]

=(SUM(C2:C8)+SUM(D2:D8)-SUM(E2:E8))/(SUM(F2:F8)+SUM(E2:E8))

Date	Market	A	B	C	D	E	F	Formula
11/1/2024	New Jersey	12	345	33	54	66	442
11/2/2024	New Jersey	34	575	2	244	345	22
11/3/2024	New Jersey	442	23	223	64	41	66
11/4/2024	New Jersey	22	45	44	33	432	345
11/5/2024	New Jersey	66	7	356	2	345	41
11/6/2024	New Jersey	345	54	634	223	575	432
11/7/2024	New Jersey	41	244	546	66	23	345	0.17
11/8/2024	New Jersey	432	64	432	345	45	575	0.05
11/9/2024	New Jersey	345	33	345	41	7	23	-0.12
11/10/2024	New Jersey	575	2	575	432	54	45	-0.16
11/1/2024	Boston	23	223	23	345	244	7
11/2/2024	Boston	45	66	45	575	64	54
11/3/2024	Boston	7	345	7	23	345	244
11/4/2024	Boston	54	41	54	45	575	64
11/5/2024	Boston	244	432	244	66	23	33
11/6/2024	Boston	64	345	223	345	45	2
11/7/2024	Boston	33	575	44	41	7	546	0.89
11/8/2024	Boston	2	23	356	432	23	432	0.52
11/9/2024	Boston	223	45	634	44	45	345	0.34
11/10/2024	Boston	44	7	546	356	7	575	0.01
11/1/2024	NYC	356	23	674	634	54	23
11/2/2024	NYC	634	45	634	546	223	45
11/3/2024	NYC	546	7	546	432	44	7
11/4/2024	NYC	674	54	674	345	356	54
11/5/2024	NYC	345	244	345	575	634	244
11/6/2024	NYC	56	64	56	432	546	223
11/7/2024	NYC	565	33	565	345	432	44	0.02
11/8/2024	NYC	56	2	54	575	345	356	0.07
11/9/2024	NYC	56	223	244	23	575	634	0.08
11/10/2024	NYC	878	44	64	453	23	34	0.27

[PSB]

I have this type of data for 100 different Market. I need 7 days rolling by this formula for each market and then calculate Median based on values for each of market on daily basis.

[PSB]

@Ahmedx could you please help?