Count Days until a minimum value has been reached

[andyb123]

Hi,

I'm looking for some help please to create an equation that will count the number of days since a minimum value has been reached.

I have a list of names in a pivot table that produces a data point of top speed reached each day. I would like to be able to count the number of days it has been since each individual has reached their individual top speed value.

Please note that this is not to be an accumulation of of day 1 + day 2 etc, each day they essentially start from 0.

I've made a table below to try to illustrate what I'm working with. I'd like Days since value to produce the result in red if possible, based on today being 31/10.

Thanks in advance!

[TABLE="width: 500"]
<tbody>[TR]
[TD]Days
Since
Value[/TD]
[TD]Name[/TD]
[TD]Top Speed
Threshold[/TD]
[TD]31/10[/TD]
[TD]30/10[/TD]
[TD]29/10[/TD]
[TD]28/10[/TD]
[TD]27/10[/TD]
[TD]26/10[/TD]
[TD]25/10[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]A[/TD]
[TD]29[/TD]
[TD]29.1[/TD]
[TD]24[/TD]
[TD]23[/TD]
[TD]27[/TD]
[TD]28[/TD]
[TD]29.3[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]B[/TD]
[TD]30[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]27[/TD]
[TD]26[/TD]
[TD]30.2[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]C[/TD]
[TD]27[/TD]
[TD]27[/TD]
[TD]20[/TD]
[TD]18[/TD]
[TD]16[/TD]
[TD]27[/TD]
[TD]22[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]D[/TD]
[TD]28[/TD]
[TD]21[/TD]
[TD]28.2[/TD]
[TD]23[/TD]
[TD]28.2[/TD]
[TD]22[/TD]
[TD]17[/TD]
[TD]18[/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

Hi, welcome to the forum!

Here is something you can try:

based on today being 31/10

In this example the value that indicates "today" is in cell B2 (note, I disagree with your expected result for the second row).

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J
2	Today	31/10
3
4	Threshold	Name	Top Speed	31/10	30/10	29/10	28/10	27/10	26/10	25/10
5	0	A	29	29.1	24	23	27	28	29.3	21
6	5	B	30	22	23	24	27	26	30.2	22
7	0	C	27	27	20	18	16	27	22	26
8	1	D	28	21	28.2	23	28.2	22	17	18
Sheet1

Cell Formulas
Range	Formula
A5	=MATCH(TRUE,INDEX(INDEX(D5:J5,MATCH($B$2,$D$4:$J$4,0)):J5>=C5,0),0)-1

 

[andyb123]

Thanks, that has worked! Well spotted with my mistake on player B as well!

One little thing that could make it perfect, if there is a way to do it. It's not counting blank cells, any way I can get it to do this?

 

[FormR]

It's not counting blank cells, any way I can get it to do this?

Hi, can you post an example table (just like you did in post#1) that includes some blank cells along with the expected results.

 

[andyb123]

Hi, can you post an example table (just like you did in post#1) that includes some blank cells along with the expected results.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Days Since[/TD]
[TD]Name[/TD]
[TD]Threshold[/TD]
[TD]31/10[/TD]
[TD]30/10[/TD]
[TD]29/10[/TD]
[TD]28/10[/TD]
[TD]27/10[/TD]
[TD]26/10[/TD]
[TD]25/10[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A[/TD]
[TD]30[/TD]
[TD]22[/TD]
[TD]25[/TD]
[TD]31[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]21[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[TD]30[/TD]
[TD]22[/TD]
[TD][/TD]
[TD]31[/TD]
[TD]19[/TD]
[TD]23[/TD]
[TD]22[/TD]
[TD]17[/TD]
[/TR]
</tbody>[/TABLE]


Currently, it would work out as person B being 2 days since.


Thanks!

 

[FormR]

Currently, it would work out as person B being 2 days since.

Hi, based on your previous examples shouldn't it return 2 for both?

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J
4	Days Since	Name	Threshold	31/10	30/10	29/10	28/10	27/10	26/10	25/10
5	2	A	30	22	25	31	22	23	21	19
6	2	B	30	22		31	19	23	22	17
7	0	A	29	29.1	24	23	27	28	29.3	21
8	5	B	30	22	23	24	27	26	30.2	22
9	0	C	27	27	20	18	16	27	22	26
10	1	D	28	21	28.2	23	28.2	22	17	18
Sheet1

Cell Formulas
Range	Formula
A5	=MATCH(TRUE,INDEX(INDEX(D5:J5,MATCH($B$2,$D$4:$J$4,0)):J5>=C5,0),0)-1

 

[andyb123]

Days Since	Name	Threshold
30
30

<tbody>
[TD="align: right"]31/10[/TD]
[TD="align: right"]30/10[/TD]
[TD="align: right"]29/10[/TD]
[TD="align: right"]28/10[/TD]
[TD="align: right"]27/10[/TD]
[TD="align: right"]26/10[/TD]
[TD="align: right"]25/10[/TD]

[TD="align: center"]2
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]A[/TD]

[TD="align: right"]17[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]19[/TD]

[TD="align: center"] 3 [/TD]
[TD="align: right"]B[/TD]

[TD="align: right"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Hi,


Sorry - yes you are correct, I'm not very good at giving these examples!

Hopefully the above gives a better picture. At the moment, it is counting both A and B as two days since, where I would like B to say 3 days.

 

[FormR]

Hi, I think your blank cells are not really blank - here is an option that should help with that.

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J
2		31/10
3
4	Days Since	Name	Threshold	31/10	30/10	29/10	28/10	27/10	26/10	25/10
5	2	A	30	17	31	25	31	22	23	21
6	3	B	30	22		31	28	19	23	22
Sheet1

Cell Formulas
Range	Formula
A5	=MATCH(1,INDEX((ISNUMBER(INDEX(D5:J5,MATCH($B$2,$D$4:$J$4,0)):J5))*(INDEX(D5:J5,MATCH($B$2,$D$4:$J$4,0)):J5>C5),0),0)

 

[andyb123]

Thanks very much, that has worked!!