Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
Wait, I got an easier expression.
=FirstSortedValue(Date,If([Cumulative Value]>=$(=max([Cumulative Value])*(1/4)), [Cumulative Value]))
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.
Wait, I got an easier expression.
=FirstSortedValue(Date,If([Cumulative Value]>=$(=max([Cumulative Value])*(1/4)), [Cumulative Value]))
Hi Tresesco
Many thanks for the quick response. Both expressions work fine.