# Formula to find number

Copper Contributor

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

 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 Replies

# Re: Formula to find number

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!

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

This will help all forum participants.

# Re: Formula to find number

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

``````=LET(