CODEWITHSUNDEEP
rexcelsum
Kangmin
Kangmin

Reputation: 53

Calculation of sum of numbers to reach a certain point

I have different decimal numbers each day and want to know how many days do you need to reach 1 or higher than 1 when you start adding percentages from different days.

My data looks like this:

days    percentage
1   0.187207822
2   0.221165007
3   0.002773814
4   0
5   0
6   0.159115287
7   0
8   0
9   0
10  0.080871794
11  0.080299951
12  0.011975184
13  0
14  0.197308657
15  0.272780872
16  0.167274086
17  0.142972819
18  0.07437692
19  0
20  0
21  0
22  0.079398371
23  0.075284697
24  0
25  0
26  0.06247718
27  0
28  0
29  0.167250066
30  0.189849856
31  0.048080715
32  0
33  0.024319067
34  0.031437684
35  0.083643198

When you start different days, you will need different days to reach 1 or higher. I want to know how to do it in R or in Excel. Thank you!

Upvotes: 1

Views: 980

Answers (2)

thelatemail
thelatemail

Reputation: 93843

Something like below perhaps, which works through the first day to the maximum day, finding how long it takes to reach a cumulative sum of >=1:

result <- sapply(
  dat$days,
  function(x) which(cumsum(dat$percentage[x:length(dat$percentage)]) >= 1)[1]
)

result
# [1] 15 14 15 14 13 12 12 11 10  9 12 12 11 10 15 16 NA NA NA NA NA NA NA
#[24] NA NA NA NA NA NA NA NA NA NA NA NA

As @MatthewLundberg below notes, the result vector gives how many days need to elapse from the start point until a cumulative sum of >=1 is reached.

Upvotes: 1

John
John

Reputation: 43249

Here are the data:

dat = structure(list(days = 1:35, percentage = c(0.187207822, 0.221165007, 
0.002773814, 0, 0, 0.159115287, 0, 0, 0, 0.080871794, 0.080299951, 
0.011975184, 0, 0.197308657, 0.272780872, 0.167274086, 0.142972819, 
0.07437692, 0, 0, 0, 0.079398371, 0.075284697, 0, 0, 0.06247718, 
0, 0, 0.167250066, 0.189849856, 0.048080715, 0, 0.024319067, 
0.031437684, 0.083643198)), .Names = c("days", "percentage"), class = "data.frame", row.names = c(NA, 
-35L))

Here is a function to work it out.

find_cumsum = function(df, day){for(i in nrow(df)){
    df = df[df$days >= day,]
    return(min(df$days[cumsum(df$percentage) >= 1]))
    }
}
find_cumsum(dat , 3)

This function takes the data.frame and the day you wish to start at. It returns the day at which you have achieved a cumulative sum of 1 or greater. For example if you start your run on day 3 (as shown) you will exceed cumulative percentage on day 17.

Upvotes: 0

Related Questions