New Posts For Thread: Excel help pleasehttps://community.betfair.com/general_betting/go/thread/view/94082/26569137/excel-help-pleaseHow do I get the function MODE() to work in a filtered list to only show a result of the filter, not all the cells inside the bracket?If I use =MODE(C6:C500) in an unfiltered list I get the right answer, if I filter the data it returns the same resulThanks for the reply bushy The array formula works fine as postedI tried all sorts of things with SUBTOTAL and was getting frustrated that I could not solve it myself, so resorted to posting and googlingCheershttps://community.betfair.com/general_betting/go/thread/view/94082/26569137/excel-help-please?post_id=480358977#480358977Thanks for the reply bushy The array formula works fine as postedI tried all sorts of things with SUBTOTAL and was getting frustrated that I could not solve it myself, so resorted to posting and googlingCheersTue, 04 Jan 2011 12:15:55 -0600You can't with SUBTOTAL. SUBTOTAL(x,A1:A100) applies one of the following functions to the filtered range dependant on the value of x as per below. An array formula as per what you have done is the best way, I think. 1 AVERAGE 2 COUNThttps://community.betfair.com/general_betting/go/thread/view/94082/26569137/excel-help-please?post_id=480358261#480358261You can't with SUBTOTAL. SUBTOTAL(x,A1:A100) applies one of the following functions to the filtered range dependant on the value of x as per below. An array formula as per what you have done is the best way, I think. 1 AVERAGE 2 COUNTTue, 04 Jan 2011 12:07:50 -0600Solved the problem by doing a googleFormula answer was an array ~ {=MODE(IF(SUBTOTAL(3,OFFSET(Stake,ROW(Stake)-MIN(ROW(Stake)),0,1)),Stake*{1,1}))}Stake being the named range for C6:C500https://community.betfair.com/general_betting/go/thread/view/94082/26569137/excel-help-please?post_id=480311269#480311269Solved the problem by doing a googleFormula answer was an array ~ {=MODE(IF(SUBTOTAL(3,OFFSET(Stake,ROW(Stake)-MIN(ROW(Stake)),0,1)),Stake*{1,1}))}Stake being the named range for C6:C500Mon, 03 Jan 2011 22:19:32 -0600How do I get the function MODE() to work in a filtered list to only show a result of the filter, not all the cells inside the bracket?If I use =MODE(C6:C500) in an unfiltered list I get the right answer, if I filter the data it returns the same resulhttps://community.betfair.com/general_betting/go/thread/view/94082/26569137/excel-help-please?post_id=480305457#480305457How do I get the function MODE() to work in a filtered list to only show a result of the filter, not all the cells inside the bracket?If I use =MODE(C6:C500) in an unfiltered list I get the right answer, if I filter the data it returns the same resulMon, 03 Jan 2011 20:38:47 -0600