Count Days until a minimum value has been reached

andyb123

New Member
Joined
Oct 31, 2018
Messages
8
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]
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
ABCDEFGHIJ
2Today31/10
3
4ThresholdNameTop Speed31/1030/1029/1028/1027/1026/1025/10
50A2929.12423272829.321
65B30222324272630.222
70C2727201816272226
81D282128.22328.2221718
Sheet1
Cell Formulas
RangeFormula
A5=MATCH(TRUE,INDEX(INDEX(D5:J5,MATCH($B$2,$D$4:$J$4,0)):J5>=C5,0),0)-1
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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
ABCDEFGHIJ
4Days SinceNameThreshold31/1030/1029/1028/1027/1026/1025/10
52A3022253122232119
62B30223119232217
70A2929.12423272829.321
85B30222324272630.222
90C2727201816272226
101D282128.22328.2221718
Sheet1
Cell Formulas
RangeFormula
A5=MATCH(TRUE,INDEX(INDEX(D5:J5,MATCH($B$2,$D$4:$J$4,0)):J5>=C5,0),0)-1
 
Upvote 0
Days SinceNameThreshold
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.
 
Upvote 0
Hi, I think your blank cells are not really blank - here is an option that should help with that.


Excel 2013/2016
ABCDEFGHIJ
231/10
3
4Days SinceNameThreshold31/1030/1029/1028/1027/1026/1025/10
52A3017312531222321
63B30223128192322
Sheet1
Cell Formulas
RangeFormula
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)
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top