I have the following set of data:

Time   Counts   Total Counts   % Complete
1      2              2           2/10
2      0              2            2/10
3      4              6            6/10
4      3             9            9/10
5      1             10          10/10

In other words, after 1 minute went by, there were 2 counts; after 2 minutes, there were 0 counts, etc.

I can easily bar graph this in Excel or R. But I'm having trouble figuring out how to easily find the point at which the "Counts" are X% complete without summing up the total counts after each additional row and dividing by the total count in the fourth column.

Is there a way to get quickly get the row in which the data is X% complete in Excel or R?


Any solution will do in principle what you are doing, though with various optimization. If you are going to be looking for many different cutoffs, it may be worth computing all the percent completes and then testing for many thresholds. If you are doing it only once, it may be worth just doing the computation on the fly.

Make your sample data readable

dat <- read.table(textConnection(
"Time Counts
1 2
2 0
3 4
4 3
5 1"), header=TRUE)

You can easily compute the other two columns:

dat$Total.counts <- cumsum(dat$Counts)
dat$Percent.complete <- dat$Total.counts / dat$Total.counts[length(dat$Total.counts)]

Then the row that first meets or exceeds a threshold X can be gotten, either by row index or the row itself. This example uses a threshold of 34%

x <- 0.34
# row number
min(which(dat$Percent.complete >= x))
# row itself
dat[min(which(dat$Percent.complete >= x)),]

If you are doing it as a one-off and don't want to precompute the third and fourth columns, you can do it all at once:

dat[min(which((cumsum(dat$Counts)/sum(dat$Counts)) >= x)),]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.