Mode Function with Criteria
Greetings, I am in need of a particular formula, however, I cannot seem to get my head around it. I need to calculate the mode of data for each person in a list. Specifically, I need to calculate the mode for all of Jeff's records (8) and calculate the mode for all of Bill's records (4) and due so for the entire spreadsheet (~10,000 rows/800 names). I want to do something like this: =if(A2=A2:A10,mode(B2:B10),"") However, I cannot find a way to specifiy for the range of the mode to only equal cells within Col B in rows where cells in Col A equal A2. Col A Col B Name Data Jeff 8 Jeff 7 Jeff 8 Jeff 8 Jeff 6 Bill 4 Bill 4 Bill 5 Thanks For Any Help, Dave -- DaveShoe ------------------------------------------------------------------------ DaveShoe's Profile: http://www.msusenet.com/member.php?userid=315 View this thread: http://www.msusenet.com/t-1869398565 |
Something on this lines
=MODE(IF(A1:A8="Jeff",B1:B8)) array entered (ctrl+shift+enter) "DaveShoe" wrote in message ... Greetings, I am in need of a particular formula, however, I cannot seem to get my head around it. I need to calculate the mode of data for each person in a list. Specifically, I need to calculate the mode for all of Jeff's records (8) and calculate the mode for all of Bill's records (4) and due so for the entire spreadsheet (~10,000 rows/800 names). I want to do something like this: =if(A2=A2:A10,mode(B2:B10),"") However, I cannot find a way to specifiy for the range of the mode to only equal cells within Col B in rows where cells in Col A equal A2. Col A Col B Name Data Jeff 8 Jeff 7 Jeff 8 Jeff 8 Jeff 6 Bill 4 Bill 4 Bill 5 Thanks For Any Help, Dave -- DaveShoe ------------------------------------------------------------------------ DaveShoe's Profile: http://www.msusenet.com/member.php?userid=315 View this thread: http://www.msusenet.com/t-1869398565 |
All times are GMT +1. The time now is 11:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com