Find the date (column 1) corresponding to the closest higher or same (>=) value (column 2)

[sbfernandes]

Greetings

I have a table with two columns: Date and Cumulative Value.

From the below calculations, 1 get 4 numbers:

max(Cumulative Value) *(1/4), 

max(Cumulative Value) *(2/4), 

max(Cumulative Value) *(3/4) and 

max(Cumulative Value) *(4/4)

I get 4 values.

I now want to get the date corresponding to the closest to these values from the table. The closest value must be greater than or equal to numbers

Eg:

Date	Cumulative Value
01/01/2020	32
09/01/2020	70
10/02/2020	95
14/02/2020	99
19/02/2020	100
20/02/2020	115
23/02/2020	123
05/03/2020	164
07/03/2020	188
11/03/2020	189
16/03/2020	201

Find date that corresponds closest to value without being lower (>=)

numbers	50.25	100.5	150.75	201
Closest Value  from table (>=)	70	115	164	201
FINAL OUTPUT: Date that corresponds to Closest Value	09/01/2020	20/02/2020	05/03/2020	16/03/2020

Thank you.

[tresesco · Accepted solution]

Try like:

=Only({<[Cumulative Value]={'$(=
Min({<[Cumulative Value]={">=$(=max([Cumulative Value])*(1/4))"}>} [Cumulative Value])
)'}>}
Date)

For various other values, you have to replace the highlighted portion.

[tresesco · Accepted solution]

Wait, I got an easier expression.

=FirstSortedValue(Date,If([Cumulative Value]>=$(=max([Cumulative Value])*(1/4)), [Cumulative Value]))

[sbfernandes]

Hi Tresesco

Many thanks for the quick response. Both expressions work fine.