Excel Broken?

[Daniel]

My formulas:
in cell B1 = SMALL($A$1:$A:$10,1)
in cell B2 and down =TRUNC(SMALL(IF($A$1:$A$10>B1,$A$1:$A
$10+0.001*ROW($A$1:$A$10)),1))

This is what i'm getting...

Data set:
A B
1) 10 10
2) 20 20
3) 30 30
4) 40 40
5) 50 50
6) 30 0
7) 20 20
8) 50 50
9) 60 60
10) 100 100

Looking at my formula you'll see that the results of B6 make no
sense. Can anyone explain this or help me fix this? I should be
getting: 10, 20, 30, 40, 50, 60, and finally 100 with 0's in the last
3 cells.

Other info, what i'm trying to do with this formula is extract unique
numbers (non-repeating) from a large data set. Some of the data in
column A will be repeating, but I only am interested in a condensed
list.

Thanks,
Daniel

 

[Tyro]

Excel is not broken. It is giving you the correct answers based on your
formulas. I don't think your formulas are doing what you think they're
doing.
For example, ROW($A$1:$A$10) results in multiplication by 1 in every
instance of your formula. I suggest you look at your formulas using the
formula evaluator to see exactly what is happening.

Tyro

 

[Bernard Liengme]

No, Excel is not broken
You need to commit the B2 and later formulas with CTRL+SHIFT+ENTER since
they are array formulas (in the Formula Bar you will then see {formula} --
Excel adds the braces)
The last three give a #NUM! error
best wishes

 

[Tyro]

When I enter the formula in B2 as an array formula in B2 through B10, I get
the answer 20, which is the first smallest value in the array, in B2 through
B10

Tyro
..

 

[Teethless mama]

Try this:

=IF(ISERR(SMALL(IF(MATCH($A$1:$A$10,$A$1:$A$10,0)=ROW($A$1:$A$10),MATCH($A$1:$A$10,$A$1:$A$10,0)),ROWS($1:1))),0,INDEX($A$1:$A$10,SMALL(IF(MATCH($A$1:$A$10,$A$1:$A$10,0)=ROW($A$1:$A$10),MATCH($A$1:$A$10,$A$1:$A$10,0)),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down as far as needed

 

[Daniel]

Try this:

=IF(ISERR(SMALL(IF(MATCH($A$1:$A$10,$A$1:$A$10,0)=ROW($A$1:$A$10),MATCH($A$1:$A$10,$A$1:$A$10,0)),ROWS($1:1))),0,INDEX($A$1:$A$10,SMALL(IF(MATCH($A$1:$A$10,$A$1:$A$10,0)=ROW($A$1:$A$10),MATCH($A$1:$A$10,$A$1:$A$10,0)),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down as far as needed

The ctrl+shift+enter worked! I never knew that. Thanks!

Now to clean it up I'll just have to remove the #num error...

 

[Tyro]

Your formula in B2 could be entered as an array formula and then copied
down:

In versions of Excel prior to 2007:

=IF(ISERROR(SMALL(IF($A$1:$A$10>B1,$A$1:$A$10),1)),"",SMALL(IF($A$1:$A$10>B1,$A$1:$A$10),1))

In Excel 2007:

=IFERROR(SMALL(IF($A$1:$A$10>B1,$A$1:$A$10),1),"")

Tyro

 

[MrAcquire]

Wouldn't it be easier to just use Data - Filter - Advanced Filter and extract
all of the unique records that meet a certain criteria (if any) if you want a
subset of unique numbers?

 

[Daniel]

Thank you Tyro.

Well you could use the filter command, but the point is to automate
the whole procedure.

 

[Max]

.. what i'm trying to do .. is extract unique numbers

(non-repeating) from a large data set.

If that data set is running in A1 down,

In B1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",ROW()))

In C1:
=IF(ROW()>COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROW())))
Select B1:C1, copy down to cover the max expected extent of data in col A.
Minimize/hide col B. Col C will auto-return the required list of uniques
from col A, all neatly bunched at the top.

 

[Daniel]

That is beautifully simple code. The adjustment I made is to extract
the actual numbers and not rows, ie:
In B1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",A1))

In C1:
=IF(ROW()>COUNT($B$1:$B$29),"",SMALL($B$1:$B$29,ROW()))

The issue come up when you have data starting on Row X other than 1...

 

[Max]

Sorry, there was an error earlier in this line
In C1 should be:
=IF(ROW()>COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROW())))

You're right that some adjustments are needed should the source
data/extractions start in other than row1 down