Formula not showing result but shows blanks instead

[Patcheen]

Im using this formula to eliminate any blanks and return the most item value instead...

{=INDEX(B2:B21,MODE(IF(B2:B21<>"",MATCH(B2:B21,B2:B21,0),0)))} (Array Formula)

but it still returns blanks

example in b2:b21 is - oranges x8 - apples x2 - pears x4 and blanks (no value) x6 (it should show "apples"

the X represents the amount of times the word appears in the row from b2:b21 etc etc


thank you all in advance for your input and help

 

[Patcheen]

i think i know what it is -

how can i change the Iferror to include this instead IF(B2:B21<>"" as there is aload more blanks in sheet 1 column m where the data is

=IFERROR(INDEX('sheet 1'!$M$4:$M$383,MATCH(B$1&$A18,'sheet 1'!$F$4:$F$383&'sheet 1'!$I$4:$I$383,0)),"")

 

[FormR]

how can i change the Iferror to include this instead IF(B2:B21<>"" as there is aload more blanks in sheet 1 column m where the data is

Sorry, I don't really know what you mean.

what does this return =LEN(B2) when pointed at one of the blank cells?

Did you try this? What was the result?

 

[Patcheen]

dont know as i dont know where to implement it

 

[FormR]

It is to help diagnose.

Put the formula into a spare cell, change B2 to a cell that is apparently blank in the range B2:B21 and report back what the formula returns.

 

[Patcheen]

It returns the number 1

 

[FormR]

It returns the number 1

We are getting somewhere then - that means they are not blank, so are not being excluded by this:

{=INDEX(B2:B21,MODE(IF(B2:B21<>"",MATCH(B2:B21,B2:B21,0),0)))}

Let's try to find out what the character is, in a spare cell put =CODE(B2) changing B2 to one of the apparently blank cell references. Report back what it returns.

 

[Patcheen]

=Code(b8) returns the number 48

 

[FormR]

=Code(b8) returns the number 48

That is a zero; do you have some special formatting in place to supress zeros from being displayed?

To start with try the red addition to the formula below:

=IFERROR(""&INDEX('sheet 1'!$M$4:$M$383,MATCH(B$1&$A18,'sheet 1'!$F$4:$F$383&'sheet 1'!$I$4:$I$383,0)),"")

I assume that you know that this formula is also an array formula and needs committing with CSE?

 

[Patcheen]

yes i prefer to display blank cell rather thank 0

no still the same displaying blank

i now have the zero displaying to sort any confusion

 

[FormR]

yes i prefer to display blank cell rather thank 0

Maybe so - but using formatting to hide them does not change the fact they are there!

Are you saying that the amended formula is still returning zeros?