CODEWITHSUNDEEP
excelexcel-formula
CrimsonRain
CrimsonRain

Reputation: 125

Computing most recent smaller value

I have an excel sheet with dates (sorted) in one column and values in another. Ex:

1/1/2019     10
1/2/2019     12
1/3/2019     8
1/4/2019     20
1/10/2019    8
1/12/2019    22

I want to compute in a third column, the most recent date such that value was less than or equal to the current value (if the current is the lowest, then use the current date). So, for the sample data above,

1/1/2019     10    1/1/2019
1/2/2019     12    1/1/2019
1/3/2019     8     1/3/2019
1/4/2019     20    1/3/2019
1/10/2019    8     1/3/2019
1/12/2019    22    1/10/2019

Is there a way of accomplishing this without VBA macros?

Upvotes: 1

Views: 71

Answers (2)

Black cat
Black cat

Reputation: 6192

The formula in cell C10 and drag down

=INDEX($A$1:$A$15,MAX(IF(MIN($B$10:B10)=$B$10:$B10,ROW($B$10:$B10),0)))

enter image description here

The result in row 14 is the actual date since this is the smallest, and therefore refers to the actual date.

The INDEX range always start in cell A1 for easier referencing the required row.

In case the actual row value is the minimum and exist in a previous row (as on the screenshot in the post) , which should be returned, then apply this formula:

=INDEX($A$1:$A$15,IF(AND(MAX(IF(MIN($B$10:B10)=$B$10:$B10,ROW($B$10:$B10),0))=ROW(),SUM(MIN($B$10:B10)=$B$10:$B10)=1),ROW(), MAX(IF(MIN($B9:B$10)=$B9:$B$10,ROW($B9:$B$10),0))))

enter image description here

Upvotes: 0

Paul Canniff
Paul Canniff

Reputation: 51

Here's a way. Paste these in and copy down the column. 

Column C:  =IF(COUNTIF(B2:B6,D1)=0,A1,MINIFS(A2:A6,B2:B6,D1))

Column D:  =CONCATENATE("<",TEXT(VALUE(B1),"#"))

You can hide column D to make it prettier. It's the criteria being used by the COUNTIF and MINIFS. Column C is the output. 

1/1/2019    10  1/3/2019    <10
1/2/2019    12  1/3/2019    <12
1/3/2019    8   1/3/2019    <8
1/4/2019    20  1/10/2019   <20
1/10/2019   8   1/10/2019   <8
1/12/2019   22  1/12/2019   <22

Formula view:

43466   10  =IF(COUNTIF(B2:B6,D1)=0,A1,MINIFS(A2:A6,B2:B6,D1))  =CONCATENATE("<",TEXT(VALUE(B1),"#"))
43467   12  =IF(COUNTIF(B3:B7,D2)=0,A2,MINIFS(A3:A7,B3:B7,D2))  =CONCATENATE("<",TEXT(VALUE(B2),"#"))
43468   8   =IF(COUNTIF(B4:B8,D3)=0,A3,MINIFS(A4:A8,B4:B8,D3))  =CONCATENATE("<",TEXT(VALUE(B3),"#"))
43469   20  =IF(COUNTIF(B5:B9,D4)=0,A4,MINIFS(A5:A9,B5:B9,D4))  =CONCATENATE("<",TEXT(VALUE(B4),"#"))
43475   8   =IF(COUNTIF(B6:B10,D5)=0,A5,MINIFS(A6:A10,B6:B10,D5))   =CONCATENATE("<",TEXT(VALUE(B5),"#"))
43477   22  =IF(COUNTIF(B7:B11,D6)=0,A6,MINIFS(A7:A11,B7:B11,D6))   =CONCATENATE("<",TEXT(VALUE(B6),"#"))

This is a little sloppy in that you could use a named value or absolute value for the end of the range, e.g. B$6. Otherwise you're going to be looking at cells below your table, which is fine as long as they're empty, but kind of sloppy. 

Column C:  =IF(COUNTIF(B2:B$6,D1)=0,A1,MINIFS(A2:A$6,B2:B$6,D1))

Upvotes: 1

Related Questions