Mode #N/A Error

Question

I am trying to take the mode of a set of string values by using the following formula:

{=INDEX(C11:C999,MODE(MATCH(C:C,C:C,0)))}

I keep getting an #N/A error because column C has some blank values. The problem is that I have to use an absolute cell reference instead of a fixed one because never know how long the column is actually going to be. I am doing this for a lot of spreadsheets and a lot of data and I don't want to have to manually reference the cell as new data is imported. 

Any help would be much appreciated.

Answer 1

Hi Julian,

Thanks for using Microsoft products.

According to your description, we would like to suggest you refer to How to correct a #N/A error in INDEX/MATCH functions

If the issue still persists, would you mind providing the following information for our troubleshooting?

1.Could you please share your workbook without privacy with us? In this way, we could help you more accordingly. You could upload the sample without privacy to OneDrive or Google Drive and share the link in public. 

Reference: 

Upload and save files and folders to OneDrive 

Share OneDrive files and folders

2.Your subscription screenshot via Open Excel client>File>Account>Product Information as below. In order to protect your privacy, please help us mask your PII, such as email address. You could click this icon to upload image.

Appreciate your understanding and stay safe!

Best Regards,

Sandro

Answer 2

Hi Julian,

Haven’t heard from you for a while.

According to my last reply, is there anything unclear with #N/A Error?

If you need further assistance, you could contact us in forum at your convenience.

Best Regards,

Sandro