Having difficulty understanding SMALL function in formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steve - 04 Apr 2007 11:18 GMT

I have the following in a spreadsheet:

A    B    C    D    E    F    G    H

row 1    1.1    1    76    71    #N/A    71    5
row 2    1.2    1    124    117    #N/A    117    7
row 3    1.3    1    156    159    159    159    3
row 4    2.1    2    12    5    #N/A    5    7
row 5    2.2    2    54    59    59    59    5
row 6    3.1    3    41    45    45    45    4
row 7    3.2    3    252    310    310    310    5
row 8    3.3    3    305    438    310    310    5
row 9    3.4    3    422        438    438    3
row 10    3.5    3    441        #N/A    438    3
row 11    4.1    4    33    35    35    35    2
row 12    4.2    4    107    111    111    111    4
row 13    4.3    4    180    175    #N/A    175    5
row 14    5.1    5    227    230    230    230    3

My array formula in column H is as follows:

={SMALL(($C$1:$C$14=C1)*ABS(G1-($D$1:$D$14)),COUNTA($C$1:$C$14)-
COUNTIF($C$1:$C$14,C1)+1)}

I guess I'm not sure why the information in column H wouldn't come out
to be {5,41,83,0,0,0,0,0,0,0,0,0,0,0} when the first part of the array
is multiplied out... Wouldn't the array product for the last 11 rows
be zero since $C$1:$C$14=C1 is FALSE for these rows? I'm quite sure
I'm missing something here, but not sure as to exactly what it is.....

Please advise,

Steve

Reply to this Message

Steve - 04 Apr 2007 11:22 GMT

> I have the following in a spreadsheet:
>
[quoted text clipped - 29 lines]
>
> Steve

Sorry, my example was a bit obliterated. Cells E9 & E10 are
blank....Steve

Reply to this Message

Bob Phillips - 04 Apr 2007 12:52 GMT

Select H1:H14, and add this formula (as a block array formula) and array
commit it

=IF(ISERROR(NOT(SMALL(IF(($C$1:$C$14=$C1)*ABS($G1-($D$1:$D$14))<>0,ROW($A1:$A14),""),ROW($A1:$A14)))),"",
($C$1:$C$14=$C1)*ABS($G1-($D$1:$D$14)))

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I have the following in a spreadsheet:
>
[quoted text clipped - 29 lines]
>
> Steve

Reply to this Message

Steve - 04 Apr 2007 13:41 GMT

> Select H1:H14, and add this formula (as a block array formula) and array
> commit it
[quoted text clipped - 44 lines]
>
> - Show quoted text -

Bob,

Thanks for the formula, it does give me the results that I thought I
should have been getting with the formula that I had inherited with
the original spreadsheet. However, could you perhaps explain to me
why I was getting the results that I was originally with the original
formula, because that was bugging me why I wasn't seeing what I
thought I should have been seeing with that formula? I'm somewhat of
a novice with this stuff still, so I apologize if I sound like I'm out
in left field a bit.

Please advise,

Steve

Reply to this Message

Bob Phillips - 04 Apr 2007 13:52 GMT

The problem is that your results are shifting per row in this part of the
formula ($C$1:$C$14=C1)*ABS(G1-($D$1:$D$14))

Row1 gives {5;53;85;0;0;0;0;0;0;0;0;0;0;0}
Row 2 gives {41;7;39;0;0;0;0;0;0;0;0;0;0;0}
Row 3 gives {83;35;3;0;0;0;0;0;0;0;0;0;0;0}
etc.

Your formula gets closer if changed to

=SMALL(($C$1:$C$14=C1)*ABS(G1-($D$1:$D$14)),COUNTA($C$1:$C$14)-COUNTIF($C$1:$C$14,C1:C14)+1)

and block array entered, but it still needs work to remove the duplicates,
and zero.

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

On Apr 4, 7:52 am, "Bob Phillips" <bob....@somewhere.com> wrote:

> Select H1:H14, and add this formula (as a block array formula) and array
> commit it
[quoted text clipped - 49 lines]
>
> - Show quoted text -

Reply to this Message

Steve - 04 Apr 2007 14:06 GMT

> The problem is that your results are shifting per row in this part of the
> formula ($C$1:$C$14=C1)*ABS(G1-($D$1:$D$14))
[quoted text clipped - 90 lines]
>
> - Show quoted text -

got it...thanks so much, Steve

Reply to this Message

Steve - 04 Apr 2007 18:38 GMT

> The problem is that your results are shifting per row in this part of the
> formula ($C$1:$C$14=C1)*ABS(G1-($D$1:$D$14))
[quoted text clipped - 90 lines]
>
> - Show quoted text -

Bob,

One more quick question. Is there a way in Excel that I can
temporarily show the results of an array formula (or portion thereof)
calculated out, like what you had shown me in your response for those
3 rows?

Just curious,

Steve

Reply to this Message

Bob Phillips - 04 Apr 2007 21:30 GMT

Yes, Go to edit mode (f2), select a part of the formula (It must be a part
that completely resolves, so IF(A=B,1,2) is ok, IF(A=B,1,2 is not), and then
hit F9. When done, hit Ctrl-Z to restore the formula.

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

On Apr 4, 8:52 am, "Bob Phillips" <bob....@somewhere.com> wrote:

> The problem is that your results are shifting per row in this part of the
> formula ($C$1:$C$14=C1)*ABS(G1-($D$1:$D$14))
[quoted text clipped - 93 lines]
>
> - Show quoted text -

Reply to this Message

Steve - 05 Apr 2007 00:29 GMT

> Yes, Go to edit mode (f2), select a part of the formula (It must be a part
> that completely resolves, so IF(A=B,1,2) is ok, IF(A=B,1,2 is not), and then
[quoted text clipped - 119 lines]
>
> - Show quoted text -

cool, thanks again, Steve

Reply to this Message

Rate this thread: