creating table

[whynot]

I would like to create a table with information i have in two columns.

The issue I have is that there are rows that do not have information and i do not want to include that in the table.

The following screenshots illustrate the appearance of the data set and what i would like it to look like.


The current data set

What i would like to create

Really appreciate the help.

 

[jmacleary]

What about filtering on column F or H?

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I
1
2	31/10/2015	-1.412558921			Saturday			01/11/2015	-1.386801
3	01/11/2015	-1.36104291			Sunday	-1.386800915		08/11/2015	-1.997973
4	02/11/2015	-2.629211911			Monday			15/11/2015	-2.232602
5	03/11/2015	-1.396562134			Tuesday			22/11/2015	-2.241532
6	04/11/2015	-1.831855859			Wednesday			29/11/2015	-2.131678
7	05/11/2015	-2.799223002			Thursday			06/12/2015	-2.091476
8	06/11/2015	-1.872631499			Friday			13/12/2015	0
9	07/11/2015	-1.277691243			Saturday			20/12/2015	0
10	08/11/2015	-2.178635784			Sunday	-1.997973062		27/12/2015	0
11	09/11/2015	-2.921114103			Monday			03/01/2016	0
12	10/11/2015	-2.680514736			Tuesday
13	11/11/2015	-1.036175935			Wednesday
14	12/11/2015	-1.244940138			Thursday
15	13/11/2015	-2.695439554			Friday
16	14/11/2015	-2.664679119			Saturday
17	15/11/2015	-2.385352769			Sunday	-2.232602336
18	16/11/2015	-2.702402518			Monday
19	17/11/2015	-1.861110877			Tuesday
20	18/11/2015	-2.825728753			Wednesday
21	19/11/2015	-2.601115017			Thursday
22	20/11/2015	-2.432868797			Friday
23	21/11/2015	-1.648590788			Saturday
24	22/11/2015	-1.618909957			Sunday	-2.241532387
25	23/11/2015	-1.782181522			Monday
26	24/11/2015	-2.876539358			Tuesday
27	25/11/2015	-1.58492922			Wednesday
28	26/11/2015	-2.321220428			Thursday
29	27/11/2015	-2.081107275			Friday
30	28/11/2015	-2.538257542			Saturday
31	29/11/2015	-1.73750879			Sunday	-2.131677733
32	30/11/2015	-1.472958623			Monday
33	01/12/2015	-2.709993854			Tuesday
Data

Cell Formulas
Range	Formula
E2:E33	E2	=TEXT(A2,"dddd")
F2:F33	F2	=IF(E2="Sunday",AVERAGE(FILTER($B$2:$B$33,ISOWEEKNUM($A$2:$A$33)=ISOWEEKNUM(A2))),"")
H2:H11	H2	=SEQUENCE(10,,WORKDAY.INTL(A2-1,1,"1111110"),7)
I2:I11	I2	=AVERAGE(FILTER($B$2:$B$33,ISOWEEKNUM($A$2:$A$33)=ISOWEEKNUM(H2),0))
Dynamic array formulas.

 

[whynot]

That works!! Thank you!!

What formulas would I use to get data from column C for the same time period and I wanted to create a table from the data i have in column k - ADP qtrly average.

Sorry for so many questions.

I really appreciate the help!!

 

[Fluff]

Just use the same formula as I2 but change the filter range from B to C

 

[whynot]

Thank you very much for the help.

one issue i am running into is that i have data that goes to row 25,000

when i change the formula in i2 and change the range from 33 to 25,000 the formula does not work.

what else would i need to change.

thank you.

 

[Fluff]

I grabbed the wrong formula from you previous thread it should be

=AVERAGE(FILTER($B$2:$B$25000,(ISOWEEKNUM($A$2:$A$25000)=ISOWEEKNUM(H2))*(YEAR($A$2:$A$25000)=YEAR(H2))))

 

[whynot]

Thank you for the help.

what formula would i use for creating a similar table with information from column k - ADP qtrly average.

thank you very much!!!

 

[Fluff]

You can use the formula you already have. Just list the relevant dates & point the formula at that.

 

[whynot]

Got it. Thank you.

what formula would i use to only have the dates that are applicable for each quarter - 3/31/1960, 6/30/1960, 9/30/1960,12/31/1960, ....... 3/31/20, 6/30/20

Thank you again for your help.