Formula to find number

[Francisco77]

Hi Community,

I have this table that shows values ​​by date. How could I generate a formula in which, indicating a start date, it shows me the date on which the accumulated sum of values ​​reaches 100, or the value closest to 100 (for example, if I indicate 10-21-23 as the start, it returns me the date 05-11-23)?

Date	Value
18-10-23	2,6
19-10-23	6,3
20-10-23	2,6
21-10-23	6,5
22-10-23	6,0
23-10-23	5,5
24-10-23	2,8
25-10-23	4,8
26-10-23	3,9
27-10-23	6,7
28-10-23	6,6
29-10-23	7,1
30-10-23	6,1
31-10-23	2,8
01-11-23	8,9
02-11-23	10,7
03-11-23	8,5
04-11-23	6,9
05-11-23	4,9
06-11-23	7,2
07-11-23	5,8
08-11-23	9,8
09-11-23	5,8
10-11-23	5,3
11-11-23	5,1
12-11-23	5,6
13-11-23	4,4
14-11-23	5,4
15-11-23	8,1
16-11-23	11,0
17-11-23	11,6
18-11-23	10,7
19-11-23	11,0
20-11-23	9,8
21-11-23	9,3
22-11-23	11,0
23-11-23	11,9
24-11-23	14,3
25-11-23	14,0
26-11-23	12,5
27-11-23	13,8
28-11-23	11,4
29-11-23	11,3
30-11-23	9,4
01-12-23	8,5
02-12-23	10,4
03-12-23	10,5
04-12-23	10,7
05-12-23	12,8
06-12-23	10,4
07-12-23	12,8

Thanks,

Francisco

[#2]

@Francisco77 

To find the date on which the accumulated sum of values reaches 100 or the value closest to 100, you can use a formula in Excel. You can use the following formula to achieve this:

Assuming your dates are in column A and the values are in column B, and you want to start counting from cell C1 (you can change this reference based on your needs), enter this array formula in a cell:

=INDEX(A1:A40,MATCH(TRUE,SUMIF(OFFSET($B$1,0,0,ROW(B1:B40)-MIN(ROW(B1:B40)),1),">="&100)>=100,0),0)

This formula finds the date closest to or exactly reaching the accumulated sum of 100 by checking the cumulative sum of values.

For your example data, it should return "05-11-23" as the result since the accumulated sum at that date is the closest to 100 (which is 100.1). The text was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

[#3]

@Francisco77 

I recommend tabling the source data to set yourself up with dynamic items and then do some accumulation:

=LET(
    discard, XMATCH(DateInput, Table1[Date]) - 1,
    Values, DROP(Table1[Value], discard),
    Dates, DROP(Table1[Date], discard),
    Accumulate, LAMBDA(a, v, a + v),
    AccArray, SCAN(0, Values, Accumulate),
    XLOOKUP(100, AccArray, Dates, "", -1)
)