formulafinder
New Member
- Joined
- Sep 3, 2019
- Messages
- 1
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:
<tbody>
</tbody>
<tbody>
</tbody>
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:
<tbody>
</tbody>
<tbody>
</tbody>
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.
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:
Maximum count of rows meeting criteria | 3 |
<tbody>
</tbody>
Person | Value X | Value Y |
A | 10 | 10 |
A | 20 | |
A | 30 | |
A | 40 | 40 |
<tbody>
</tbody>
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:
Maximum count of rows meeting criteria | 4 |
<tbody>
</tbody>
Person | Value X | Value Y |
A | 10 | 10 |
A | 20 | |
A | 30 | |
A | 40 |
<tbody>
</tbody>
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.