Feb 14 2020 07:30 AM
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.
Feb 14 2020 01:19 PM
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
Feb 15 2020 02:48 AM
Feb 17 2020 03:27 AM
@Sergei Baklan Success - Thank you.