Counting Formula

BrettOlbrys1 · May 10, 2021

Based on a week date, I want to assign a "1" to that date. The dates greater than that date, I want the value to count up by +1 and the dates less than that date, I want them to count down by -1. My file is not static though because based on when the file is opened, the numbers assigned to the static dates will move. I need a formula that will assign the numbers above the dates.

If today was 4/19
-5	-4	-3	-2	-1	0	1	2	3	4	5	6	7	8	9
3/8	3/15	3/22	3/29	4/5	4/12	4/19	4/26	5/3	5/10	5/17	5/24	5/31	6/7	6/14


If today was 5/10
-8	-7	-6	-5	-4	-3	-2	-1	0	1	2	3	4	5	6
3/8	3/15	3/22	3/29	4/5	4/12	4/19	4/26	5/3	5/10	5/17	5/24	5/31	6/7	6/14


If today was 5/24
-10	-9	-8	-7	-6	-5	-4	-3	-2	-1	0	1	2	3	4
3/8	3/15	3/22	3/29	4/5	4/12	4/19	4/26	5/3	5/10	5/17	5/24	5/31	6/7	6/14

BrettOlbrys1 · May 10, 2021

Here is what I am doing. I run weekly activity reports of a sales team and graph the corresponding results. Currently, I manually edit the graphs and choose which weeks to show on the graph, but I want to automate the graphing by always using the latest 12 weeks of data. I plan to do this by using an INDEX MATCH formula.

I have one sheet for all of 2021 with each column having a date, such as the Monday of every week (5/3, 5/10, 5/17, etc...). This sheet is where I place all of the RAW data for every week of the year. On a separate sheet, I have a table that will be auto-populated with the last 12 weeks of data based on whenever the file is opened because it will compare the TODAY () date against the sheet with the columns of raw data.

I need the table, that I will be using to graph, to auto-populate with whatever the last 12 weeks are. My plan was to place a "1", "2", "3", etc... in 12 corresponding columns. Based on the TODAY () date, the sheet will look to the raw data DATE sheet for each week and then assign the "1", "2", "3", etc... to those raw data columns. I will then use the INDEX MATCH to pull the 12 weeks of data into my graphing table.

Peter_SSs · May 10, 2021

BrettOlbrys1 said:
My plan was to place a "1", "2", "3", etc... in 12 corresponding columns. Based on the TODAY () date,

So my suggestion in post #9 doesn't do what you want? If not, can you explain exactly what should be different with those results given today's date of 11 may?

johnnyL · May 11, 2021

BrettOlbrys1 said:
Here is what I am doing. I run weekly activity reports of a sales team and graph the corresponding results. Currently, I manually edit the graphs and choose which weeks to show on the graph, but I want to automate the graphing by always using the latest 12 weeks of data. I plan to do this by using an INDEX MATCH formula.

@BrettOlbrys1 you mention wanting latest 12 weeks, but your original post here shows 15 weeks of results. ???

Peter_SSs · May 11, 2021

johnnyL said:
your original post here shows 15 weeks of results. ???

I suspect that might be an example snippet of ..

BrettOlbrys1 said:
I have one sheet for all of 2021 with each column having a date, such as the Monday of every week (5/3, 5/10, 5/17, etc...). This sheet is where I place all of the RAW data for every week of the year.

.. from which the OP is trying to extract the correct 12 weeks for the graph.

@BrettOlbrys1
Thanks for updating your profile.

Since you have 365 you might be going to too much trouble to insert these numbers then INDEX/MATCH to try to extract your 12 weeks of interest. I don't really know exactly what you have on the raw data sheet or what parts of it to extract but here is a small example to show how the data may possibly be extracted with a single formula in a single cell.

Here is my raw data sheet with some dummy data and dates (mine are in d/m/y format) for a whole year. I have hidden some columns to keep this sheet smallish for the forum

BrettOlbrys1.xlsm

A

B

C

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AY

AZ

2

5/01/21

12/01/21

19/01/21

4/05/21

11/05/21

18/05/21

25/05/21

1/06/21

8/06/21

15/06/21

22/06/21

29/06/21

6/07/21

13/07/21

20/07/21

27/07/21

3/08/21

21/12/21

28/12/21

3

data 1

data 2

data 3

data 18

data 19

data 20

data 21

data 22

data 23

data 24

data 25

data 26

data 27

data 28

data 29

data 30

data 31

data 51

data 52

4

other 1

other 2

other 3

other 18

other 19

other 20

other 21

other 22

other 23

other 24

other 25

other 26

other 27

other 28

other 29

other 30

other 31

other 51

other 52

Raw data

Now, suppose that we want 12 columns extracted starting at today's date (or if today's date is not in the raw sheet, starting at the most recent date before today) we can do that with this formula entered into A1 (or any cell you want) below.

BrettOlbrys1.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

1

11/05/21

18/05/21

25/05/21

1/06/21

8/06/21

15/06/21

22/06/21

29/06/21

6/07/21

13/07/21

20/07/21

27/07/21

2

data 19

data 20

data 21

data 22

data 23

data 24

data 25

data 26

data 27

data 28

data 29

data 30

3

other 19

other 20

other 21

other 22

other 23

other 24

other 25

other 26

other 27

other 28

other 29

other 30

4

12 weeks

Cell Formulas
Range	Formula
A1:L3	A1	=FILTER('Raw data'!A2:AZ4,('Raw data'!A2:AZ2>TODAY()-7)*('Raw data'!A2:AZ2<=TODAY()+77),"")
Dynamic array formulas.

BrettOlbrys1 · May 11, 2021

Thanks guys. I am going to use what you have provided and see how each proposed solution works and report back. Is it possible for me to attach a spreadsheet that I have formatted versus only attaching these embedded tables above because I cannot install anything since this is a company laptop and they have it locked down?

Peter_SSs · May 11, 2021

BrettOlbrys1 said:
Is it possible for me to attach a spreadsheet that I have formatted versus only attaching these embedded tables ...

No, you cannot attach an actual workbook. The other alternatives are

- copy/paste directly but that lacks most formatting, formulas etc. eg

5/01/21	12/01/21	19/01/21
data 1	data 2	data 3
other 1	other 2	other 3

- upload a small sample dummy file to DropBox, OneDrive etc and provide a shared link here, though some forum helpers choose not to download such files or, like you, are prevented from doing so by workplace restrictions.

BrettOlbrys1 · May 11, 2021

OK, so let me try to be a little more clear. Here is an actual set of data that I have in my worksheet:

1/4	1/11	1/18	1/25	2/1	2/8	2/15	2/22	3/1	3/8	3/15	3/22	3/29	4/5	4/12	4/19	4/26	5/3	5/10
58	60	49	58	54	59	48	53	61	61	51	53	42	42	50	46	47	34	30

Each date shows the amt. of activity for that week and I always graph the latest 12 weeks of data. For example, this week I would graph 2/22 through 5/10 because those are the most recent 12 weeks of data. Last week I would have graphed 2/15 through 5/3 and the week before I would have graphed 2/8 through 4/26, you get the idea.

When I produce my report, I manually edit the graph to select the latest 12 weeks of data, but I want to automate this function so the graph will always display the latest 12 weeks of data. My working concept was to have the raw data table (like the above) and then have a second table where only 12 data points could be written (see below). Each of the 12 columns will always have an assigned value (1-12). In row 2, the sheet would place the latest 12 DATES from the above table and in row 3, the sheet would place that week's corresponding VALUE from the above table.

Where I am getting stuck is how to identify the latest 12 weeks of data as that will change based on when I run the report. I thought I could write a formula that would assign a value (1, 2, 3 ,4, etc.) to each column in the raw data table (above) based on the dates listed for each column and based on when the report is run. Based on those values (1 - 12), I could simply do an INDEX MATCH and then populate the graphing table below with the columns that have the values of 1 - 12.

Does this make more sense?

	A	B	C	D	E	F	G	H	I	J	K	L
1	1	2	3	4	5	6	7	8	9	10	11	12
2
3

BrettOlbrys1 · May 11, 2021

@Peter_SSs

Peter, it appears your suggestion in post #9 will work, but I have a couple of questions to understand what you did and learn how to use this formula:

1) it is taking today's date and providing the next 11 columns of data, but I want it to provide the previous 11 columns of data. How do I modify the formula to do this?
2) What does the +77 value represent and why that number?

Thanks,

Brett

Peter_SSs · May 11, 2021

BrettOlbrys1 said:
1) it is taking today's date and providing the next 11 columns of data, but I want it to provide the previous 11 columns of data. How do I modify the formula to do this?
2) What does the +77 value represent and why that number?

Let's try again. It is now 12 May here.

BTW, you never answered my question:

Peter_SSs said:
Your dates are all one week apart and all of your examples have "today's date" as one of the values in the row. What should happen if today's date is not actually in the list?

Here is (part of) my new Raw data sheet

BrettOlbrys1.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

2

6/01/21

3

data 1

data 2

data 3

data 4

data 5

data 6

data 7

data 8

data 9

data 10

data 11

data 12

data 13

data 14

data 15

data 16

data 17

data 18

data 19

data 20

data 21

4

other 1

other 2

other 3

other 4

other 5

other 6

other 7

other 8

other 9

other 10

other 11

other 12

other 13

other 14

other 15

other 16

other 17

other 18

other 19

other 20

other 21

Raw data

Cell Formulas
Range	Formula
B2:U2	B2	=A2+7

The -84 in the formula below is the number of days to allow for 12 weeks of results.

BrettOlbrys1.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

1

24/02/21

3/03/21

10/03/21

17/03/21

24/03/21

31/03/21

7/04/21

14/04/21

21/04/21

28/04/21

5/05/21

12/05/21

2

data 8

data 9

data 10

data 11

data 12

data 13

data 14

data 15

data 16

data 17

data 18

data 19

3

other 8

other 9

other 10

other 11

other 12

other 13

other 14

other 15

other 16

other 17

other 18

other 19

4

12 weeks

Cell Formulas
Range	Formula
A1:L3	A1	=FILTER('Raw data'!A2:AZ4,('Raw data'!A2:AZ2<=TODAY())*('Raw data'!A2:AZ2>TODAY()-84),"")
Dynamic array formulas.

johnnyL · May 11, 2021

BrettOlbrys1 said:
@Peter_SSs

Peter, it appears your suggestion in post #9 will work, but I have a couple of questions to understand what you did and learn how to use this formula:

1) it is taking today's date and providing the next 11 columns of data, but I want it to provide the previous 11 columns of data. How do I modify the formula to do this?

11 weeks previous of today's date, today's date, 11 weeks after today's date:

The following would set '0' to the current date. If you still want the current date to reflect a '1' value then change the formula to '7 + 1'

Book1