Forum Discussion

Francisco77's avatar
Francisco77
Copper Contributor
Oct 28, 2023

Formula to find number

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)?

 

DateValue
18-10-232,6
19-10-236,3
20-10-232,6
21-10-236,5
22-10-236,0
23-10-235,5
24-10-232,8
25-10-234,8
26-10-233,9
27-10-236,7
28-10-236,6
29-10-237,1
30-10-236,1
31-10-232,8
01-11-238,9
02-11-2310,7
03-11-238,5
04-11-236,9
05-11-234,9
06-11-237,2
07-11-235,8
08-11-239,8
09-11-235,8
10-11-235,3
11-11-235,1
12-11-235,6
13-11-234,4
14-11-235,4
15-11-238,1
16-11-2311,0
17-11-2311,6
18-11-2310,7
19-11-2311,0
20-11-239,8
21-11-239,3
22-11-2311,0
23-11-2311,9
24-11-2314,3
25-11-2314,0
26-11-2312,5
27-11-2313,8
28-11-2311,4
29-11-2311,3
30-11-239,4
01-12-238,5
02-12-2310,4
03-12-2310,5
04-12-2310,7
05-12-2312,8
06-12-2310,4
07-12-2312,8

 

Thanks,

 

Francisco

Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Nov 01, 2023

    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)
)

     

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Oct 31, 2023

    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.

Resources