Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding center of a distribution
Trying to use the correct function(s) to find the center of a distribution
of numbers such that the sum of the numbers to the left of "center" equals the sum of the numbers to the right. i.e. if the data points are, say 8 am, 9 am, 10 am.... thru 5 pm, and there are events thru out the day, I am looking at what time has 50% of the events happen. Expected results example= if the "center" happens at 3pm, means that 42 events happend before 3 pm and 42 events happened after 3 pm. TIA |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding center of a distribution
Say we have values in column A like:
28 36 7 32 2 16 21 4 46 38 47 38 11 28 18 44 49 14 21 31 5 27 44 47 48 35 8 4 16 25 In B2 enter: =ABS(SUM($A$1:A2)-SUM(A2:A$30)) and copy down column B compares the sum above to the sum below. We now see: 28 36 698 7 655 32 616 2 582 16 564 21 527 4 502 46 452 38 368 47 283 38 198 11 149 28 110 18 64 44 2 49 91 14 154 21 189 31 241 5 277 27 309 44 380 47 471 48 566 35 649 8 692 4 704 16 724 25 765 Clearly column B has a minimum where the sum above and sum below nearly balance. So =MATCH(MIN(B:B),B:B,FALSE) returns a 16.....the "middle" row -- Gary''s Student - gsnu2007j "Meebers" wrote: Trying to use the correct function(s) to find the center of a distribution of numbers such that the sum of the numbers to the left of "center" equals the sum of the numbers to the right. i.e. if the data points are, say 8 am, 9 am, 10 am.... thru 5 pm, and there are events thru out the day, I am looking at what time has 50% of the events happen. Expected results example= if the "center" happens at 3pm, means that 42 events happend before 3 pm and 42 events happened after 3 pm. TIA |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding center of a distribution
Thank you GS...will have to work with this a couple of days and see what
happens. I have decided on using 37 data points, 8-5, 15 minute increments and did some conditional formating (lowest 3)so that the "center" backfills with green. This will constantly change during the day as the data is input. "Gary''s Student" wrote in message ... Say we have values in column A like: 28 36 7 32 2 16 21 4 46 38 47 38 11 28 18 44 49 14 21 31 5 27 44 47 48 35 8 4 16 25 In B2 enter: =ABS(SUM($A$1:A2)-SUM(A2:A$30)) and copy down column B compares the sum above to the sum below. We now see: 28 36 698 7 655 32 616 2 582 16 564 21 527 4 502 46 452 38 368 47 283 38 198 11 149 28 110 18 64 44 2 49 91 14 154 21 189 31 241 5 277 27 309 44 380 47 471 48 566 35 649 8 692 4 704 16 724 25 765 Clearly column B has a minimum where the sum above and sum below nearly balance. So =MATCH(MIN(B:B),B:B,FALSE) returns a 16.....the "middle" row -- Gary''s Student - gsnu2007j "Meebers" wrote: Trying to use the correct function(s) to find the center of a distribution of numbers such that the sum of the numbers to the left of "center" equals the sum of the numbers to the right. i.e. if the data points are, say 8 am, 9 am, 10 am.... thru 5 pm, and there are events thru out the day, I am looking at what time has 50% of the events happen. Expected results example= if the "center" happens at 3pm, means that 42 events happend before 3 pm and 42 events happened after 3 pm. TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trust center log | Setting up and Configuration of Excel | |||
Center on colon | Excel Discussion (Misc queries) | |||
center across the selection | New Users to Excel | |||
Center Across Selection Vertically Help. I am trying to center te. | Excel Discussion (Misc queries) | |||
How do I UN Center a Selection | Excel Discussion (Misc queries) |