Day when 50% of total was reached

West Man

Well-known Member
Joined
Mar 27, 2006
Messages
1,175
In A6 through A87 I have days consecutively numbered 1 throufh 82. In B6:B87 I have a count of the responses for each day. I need a formula to give me the day number on which 50% of the total response (sum of daily counts) occurred. It seems like a lookup for = or > half the total response in an array of the "running total" by day, but I just can't get a grasp on it.

Any help will be appreciated.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
You can add a helper column with formula =SUM($B$6:B6) copied down. This shows your cumulative total.

Then to get day number use: =INDEX(A6:A87,MATCH(SUM(B6:B87)/2,C6:C87)+1) where C6:C87 is the helper column range just added.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
=INDEX(A6:A87,MATCH(TRUE,SUBTOTAL(9,OFFSET(B6,0,0,ROW(B6:B87)-ROW(B6)+1))/SUM(B6:B87)>=0.5,0))

which you need to confirm with control+shift+enter, not just with enter.
 

West Man

Well-known Member
Joined
Mar 27, 2006
Messages
1,175
Thank you both. I will go with Aladin's solution as a helper column is not desired.

Some "formula auditing | evaluate formula" will help me understand the formula.

Again thanks to each of you.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,042
Messages
5,545,688
Members
410,698
Latest member
Wloven
Top