Incrementing a calculated cell by if it meets a set of criteria

[furness121]

Hi,
I am trying to create an attendance streaks set of data.
I have columns with how many sessions a student has attended that week (10 is maximum)

I need to have a column that runs a current total that increments by 10 if a student has attended all week but it needs to increment the original value in the cell

e.g if Week 18 = 10, then increment current total (held in the cell) by 10 otherwise set it back to 0 as they have missed a day and there streak needs to start again

It is telling me that it cannot create a circular reference - does anyone know a way around it?

I have attached a picture with the data I need in that cell from the numbers visible - I've tried using a variety of different functions but cannot get it to increment!

Thanks

 

[awoohaw]

You can't have a two different "things" in a cell (1 formula and 1 value, 2 values, or 2 formulas) at the same time.
You could do what you ask with VBA.

My question is how do you know what week is current? If you have that you may be able to work backwards to get one formula to get your solution.

Also, Mr. Excel has a tool called xl2bb add in (link below) that allows users to share mini worksheets of their workbook. Please use that to post your data. If you are unable to use that, then please post your data as a table. Expected results (multiple scenarios) are always extremely healthy!

 

[furness121]

Thank you for your reply - I had used offset to move the column along each week (manually change the column number in the offset)

Attendance data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Name	Reg	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13	Week 14	Week 15	Half term	Week 16	Week 17	Week 18	Week 19	Week 20		Rolling total	Current Total	Current Streak
2	Possible sessions attendend		10	10	10	10	10	8	10	4		10	10	10	10	10		122
3	Student 1		10	10	10	10	10	8	10	3		10	4	10	10	10		115	0
4	Student 2		10	10	10	10	10	8	10	4		10	10	10	10	10		122
5	Student 3		4	10	10	10	10	8	10	4		10	10	10	10	8		114
6	Student 4		10	7	5	10	10	6	10	4		10	4	10	10	10		106
7	Student 5		10	10	8	10	10	8	10	2		6	10	8	8	10		110
8	Student 6		9	10	10	10	10	8	10	4		10	10	4	4	4		103
9
Sheet1

Cell Formulas
Range	Formula
S3	S3	=IF(OFFSET(A2,1,13)=10,S3+10,0)
R2:R8	R2	=SUM(C2:P2)

 

[awoohaw]

Thank you for your reply - I had used offset to move the column along each week (manually change the column number in the offset)

What do you mean manually, each week you would change your formulas? Do you have any kind of date or progress counter?
Or maybe a list of "Number of Weeks Completed/Processed"?

 

[furness121]

A new column will be added each week and then I would change the offset column from say 13 to 14 so it looks up the most recent.

Thanks