Formula to report most commonly entered location query

Copper Contributor

Hi, I am relatively new to larger formulas in excel and I would greatly appreciate any advice in resolving a formula issue I have.  I have a spreadsheet spanning line 11 to line 400, which will reflect the entries for this year potentially. The column 'AK' records places meetings happen, ie 'Margate', 'Ramsgate', 'On-Phone', etc. To report the most common meeting location I have used the formula '=@INDEX(AK11:AK53,MODE(MATCH(AK11:AK53,AK11:AK53,0)))', which reports in the selected reporting cell the most common entry from the lookup array. You will note 'AK11:AK53' as the lookup value and lookup array , because I I enter the full array 'AK11:AK400', where there are not yet any entries from AK54 to AK400, the reporting cell shows a '#N/A' error.  Is there anyway I can run the lookup value:lookup array with the full value (AK11:AK400) without having to update it manually every time I enter a value in the AK column? Thank you.

3 Replies

@LWE_SE 

That's like

=INDEX(AK11:AK530,MODE(IF(AK11:AK530<>"",MATCH(AK11:AK530,AK11:AK530,0))))

(array formula). Please check here Most frequently occurring text 

Thank you Sergei. I have entered the formula with the if statement exactly as dispalyed. There is a #NAME? Error with the open parenthesis before the if and its closing parenthesis highlighted in red. Any idea? I appreciate your support.

@Sergei Baklan  Success - Thank you.