Irritating Array Formula

[Pete McCosh]

All,

I would appreciate any light you can shed on the failure
of this formula to work the way I want it to:

{=INDEX($I$2:$BM$2,1,SMALL(IF($I3:$BM3=$H3,COLUMN
($I3:$BM3),100),COUNTIF($I3:$BM3,$H3)))}

The INDEX and COUNTIF parts work fine, but there seems to
be something amiss in the IF part.

H3 contains a shift code letter and I3:BM3 contain half
hourly details which can consist of the same shift code or
a range of different values. The IF statement is supposed
to create an array of values by looking at each cell in
the range and, if it equals the shift code, adding the
column index and, if not, adding 100 which is higher than
any possible positive value (column BM = 65.)
The SMALL function should then pick out the Nth
smallest value, where N is the number of occurences of the
shift code in the range...

What actually happens is it returns the column index
irrespective of the value. In fact, I've just looked into
it a bit more and it is actually evaluating all the cells
as true, even though some of them are demonstrably not
equal...

Any help appreciated.

Pete

 

[hgrove]

Pete McCosh wrote...

I would appreciate any light you can shed on the failure of this
formula to work the way I want it to:

{=INDEX($I$2:$BM$2,1,SMALL(IF($I3:$BM3=$H3,
COLUMN($I3:$BM3),100),COUNTIF($I3:$BM3,$H3)))}

...

This formula could return an error if the only match for H3 in I3:BM
were in BF3:BM3, in which case the SMALL call would return a numbe
between 58 and 65, but there are only 57 columns in I2:BM2. Even if th
rightmost match were between columns I and BE, the SMALL call woul
return the column number in which the match occurred, which is always
greater than the corresponding column index into I2:BM2. Is thi
intentional?

What actually happens is it returns the column index
irrespective of the value. In fact, I've just looked into it a bit
more and it is actually evaluating all the cells as true, even
though some of them are demonstrably not equal...

What's your H3 value? What are some I3:BM3 values that ar
'demonstrably not equal'

 

[Pete McCosh]

H(arlan?),
thanks for the response. To clarify your points,

-----Original Message-----
Pete McCosh wrote...
...

This formula could return an error if the only match for H3 in I3:BM3
were in BF3:BM3, in which case the SMALL call would return a number
between 58 and 65, but there are only 57 columns in I2:BM2. Even if the
rightmost match were between columns I and BE, the SMALL call would
return the column number in which the match occurred, which is always 8
greater than the corresponding column index into I2:BM2. Is this
intentional?

This isn't intentional, but the formula did originally
have "-8" after the column index. I was messing around
trying to see where the problem was and the version I've
posted is still missing that. Although you also made me
realise that my use of 100 for no match will cause the
same error.

What's your H3 value? What are some I3:BM3 values that are
'demonstrably not equal'?

The H3 value is a single letter code: A, P, M or T. The
I3:BM3 values can be either the same letter or zero, which
are returned from a lookup to another file. After my
original post I tried to look a bit further into the
problem and there seems to be something wrong with these
values:

H6 = S, I6:BM6 = 0,0,0,0,0,0,0,0,0,S,S,S,S,S,0,0,0,0,S, etc

=$H6=I6 returns TRUE! As does the same test for every cell
in the range! I had a look at some posts on non-printing
characters getting into cells and this didn't lead
anywhere. However, when I tried to get =LEN(I6), it
returned a #VALUE! error and I haven't been able to find
any help on this at all.

Any ideas?

Pete

 

[hgrove]

Pete McCosh wrote...
...

The H3 value is a single letter code: A, P, M or T. The I3:BM3 value can be either
the same letter or zero, which are returned from a lookup to anothe file. After
my original post I tried to look a bit further into the problem an there seems to
be something wrong with these values:

H6 = S, I6:BM6 = 0,0,0,0,0,0,0,0,0,S,S,S,S,S,0,0,0,0,S, etc

=$H6=I6 returns TRUE! As does the same test for every cell in th range! I had
a look at some posts on non-printing characters getting into cells an this didn't
lead anywhere. However, when I tried to get =LEN(I6), it returned #VALUE!
error and I haven't been able to find any help on this at all.

...

The #VALUE! error returned by LEN is a dead give-away that you hav
Transition Formula Evaluation enabled. Run the menu command Tools
Options to display the options menu, then select the Transition tab an
uncheck Transition Formula Evaluation. If you need this setting enabled
then you'll have to use a test like

$H6=TEXT(I6:BM6,"General"

 

[Pete McCosh]

Many thanks. I've got that sorted out now: the only errors
left are my own!

This isn't something I've come across before, but looking
at some other posts on this topic and asking a bit more
about the origin of the files in question, it seems that
they have been on the go, in some form, for quite some
time...