Formula with variable references depending on cell value

[formulafinder]

Hello! I have an issue writing a formula that I hope has an easy response.

I have a dataset with people who have multiple rows of data relating to them. If a certain number of rows meet criteria (eg, are within a certain time period of each other), I would like Value Y on that row to be blank. I've figured out the criteria with one hang-up: one of the criteria includes a number that can change -- the maximum count of rows meeting criteria. Once that number of rows has been hit, the row should not be blank. Here's an example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Maximum count of rows meeting criteria[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD]Person[/TD]
[TD]Value X[/TD]
[TD]Value Y[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]40[/TD]
[TD]40[/TD]
[/TR]
</tbody>[/TABLE]


Right now, my formula relies on directly referencing the cells above it =IF(AND(A1="",A2="") where if that max number increases, you'd have to add A3 and so on. Is there a way to have my formula reference the max count of rows cells and dynamically update, so if the max count was 4, A3 would be added to the formula, with a result like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Maximum count of rows meeting criteria[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD]Person[/TD]
[TD]Value X[/TD]
[TD]Value Y[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]40[/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]


I tried playing around with Indirect and wasn't able to make it work. I appreciate any thoughts/ideas/approaches. Also, apologies if there was a similar thread -- I tried searching around and couldn't find anything similar, but I'm likely just missing the best keywords.

 

[petertenthije]

[TABLE="width: 900"]
<tbody>[TR]
[TD]COLUMN
/ ROW[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Person[/TD]
[TD]Value_X[/TD]
[TD]Value_Y[/TD]
[TD][/TD]
[TD][/TD]
[TD]MAX_row_count[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]10[/TD]
[TD]=IF((COUNTIFS($A$1:$A2;$A2))/$F$2-(1/$F$2)=INT((COUNTIFS($A$1:$A2;$A2))/$F$2-(1/$F$2))=TRUE;B2;"")[/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]A[/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



To break down the formula:

=IF((COUNTIFS($A$1:$A2;$A2))/$F$2-(1/$F$2)=INT((COUNTIFS($A$1:$A2;$A2))/$F$2-(1/$F$2))=TRUE;B2;"")
Count how often en employee is listed.

=IF((COUNTIFS($A$1:$A2;$A2))/$F$2-(1/$F$2)=INT((COUNTIFS($A$1:$A2;$A2))/$F$2-(1/$F$2))=TRUE;B2;"")
Divides how often the employee is listed with the max row count as listed in cell F2.

=IF((COUNTIFS($A$1:$A2;$A2))/$F$2-(1/$F$2)=INT((COUNTIFS($A$1:$A2;$A2))/$F$2-(1/$F$2))=TRUE;B2;"")
I am skipping the blue bit, but will get back to that later.
This step determines if the amount of employees divided by the max row count is a full number (integer).

=IF((COUNTIFS($A$1:$A2;$A2))/$F$2-(1/$F$2)=INT((COUNTIFS($A$1:$A2;$A2))/$F$2-(1/$F$2))=TRUE;B2;"")
You will want to display value Y for all rows where the calculation returns a full number.
If the number is a multiple of max row count, then you can display Value Y.

=IF((COUNTIFS($A$1:$A2;$A2))/$F$2-(1/$F$2)=INT((COUNTIFS($A$1:$A2;$A2))/$F$2-(1/$F$2))=TRUE;B2;"")
To get back to the bit that was skipped earlier.
To make sure the formula displays the first entry, you have to add this bit to the calculation. Without it, every 4th entry would be displayed instead of every 1st entry (assuming value in F2 is 4).