MODE Array with Criteria - #N/A Error

RJB85

New Member
Joined
Nov 27, 2018
Messages
9
Hi,

I am getting a #N/A error with the following formula:
{=INDEX(INDIRECT($B$25),MODE(IF(INDIRECT($B$7)=$B$2,IF((--ISTEXT(INDIRECT($B$25)))>0,MATCH(INDIRECT($B$25),INDIRECT($B$25),0)))))}

$B$25 = 'Sheet1'!$K$2:$K$1137 Text categories with three different options (R, S and K)$B$7 = 'Sheet1'!$C$2:$C$1137 Text Categories with numerous inputs (Lith1, Lith2, RM1 etc)
$B$2 = Category I want to sort by i.e. Lith1

I've used this formula on a smaller test scale and it works so I'm not sure why it doesnt work on my larger database.

The only time I get a #N/A error on the small test is when I reduce all options (R, S and K) to have a maximum of one occurrence. Not sure if this will help identify the problem!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
If B25 = Sheet1!K2:K1137 then you're putting that RANGE in a MATCH statement. Surely you cant MATCH an entire range?
 
Last edited:
Upvote 0
It matches the text range against itself and then produces an array of the positions of where each category first appears.

Example:
R
R
S
S
K

Would give an array {1,1,3,3,5}
 
Upvote 0
Could you provide a small data sample (~10 rows) along with expected results?

M.
 
Upvote 0
Identified the error. It wasn't ignoring cells which had errors in the 'Sheet1'!$C$2:$C$1137. Just working on a way around that.

Not sure how to share an example as I can't upload a file and table seems pointless if you cant review the formulas.
 
Upvote 0
Thanks Marcelo, noted for next time.

I have adjusted my formula so it now works - Calculates the MODE from a list of text category inputs, based on a criteria in an adjacent cell to the list, ensures values are text and ignores errors.
{=INDEX(INDIRECT($B$25),MODE(IF((--NOT(ISERROR(INDIRECT($B$7)))),IF(INDIRECT($B$7)=$B$2,IF((--ISTEXT(INDIRECT($B$25)))>0,MATCH(INDIRECT($B$25),INDIRECT($B$25),0))))))}
 
Upvote 0
You're welcome.

I'm glad you have found a solution to the problem, but looking at the above formula it seems to me that something simpler would be possible. I do not understand why you used INDIRECT - a volatile function that can degrade performance.

Anyway if the formula works, nothing more to say!

M.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top