Having difficulty understanding SMALL function in formula

[Steve]

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

 

[Steve]

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

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

 

[Bob Phillips]

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)))

--
HTH

Bob

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

 

[Steve]

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)))

--
HTH

Bob

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











- 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

 

[Bob Phillips]

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.

--
HTH

Bob

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

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)))

--
HTH

Bob

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











- 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

 

[Steve]

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.

--
HTH

Bob

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





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- Hide quoted text -

- Show quoted text -

got it...thanks so much, Steve

 

[Steve]

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.

--
HTH

Bob

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





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- Hide quoted text -

- 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

 

[Bob Phillips]

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.

--
HTH

Bob

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

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.

--
HTH

Bob

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





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- Hide quoted text -

- 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

 

[Steve]

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.

--
HTH

Bob

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











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- Hide quoted text -

- Show quoted text -

cool, thanks again, Steve