 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / February 2008Excel Broken?
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 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 > My formulas: > in cell B1 = SMALL($A$1:$A:$10,1) [quoted text clipped - 28 lines] > Thanks, > Daniel 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  SignatureBernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email > My formulas: > in cell B1 = SMALL($A$1:$A:$10,1) [quoted text clipped - 28 lines] > Thanks, > Daniel 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 . > No, Excel is not broken > You need to commit the B2 and later formulas with CTRL+SHIFT+ENTER since [quoted text clipped - 34 lines] >> Thanks, >> 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 > My formulas: > in cell B1 = SMALL($A$1:$A:$10,1) [quoted text clipped - 28 lines] > Thanks, > Daniel On Feb 7, 1:30 pm, Teethless mama <Teethlessm...@discussions.microsoft.com> wrote: > Try this: > [quoted text clipped - 22 lines] > > 9) 60 60 > > 10) 100 100 The ctrl+shift+enter worked! I never knew that. Thanks! Now to clean it up I'll just have to remove the #num error... > > 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 [quoted text clipped - 8 lines] > > Thanks, > > Daniel 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 > My formulas: > in cell B1 = SMALL($A$1:$A:$10,1) [quoted text clipped - 28 lines] > Thanks, > Daniel 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? > My formulas: > in cell B1 = SMALL($A$1:$A:$10,1) [quoted text clipped - 28 lines] > Thanks, > Daniel Thank you Tyro. Well you could use the filter command, but the point is to automate the whole procedure. On Feb 7, 4:02 pm, MrAcquire <MrAcqu...@discussions.microsoft.com> wrote: > 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 [quoted text clipped - 32 lines] > > Thanks, > > Daniel > .. 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. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- 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... > > .. what i'm trying to do .. is extract unique numbers > > (non-repeating) from a large data set. [quoted text clipped - 14 lines] > xdemechanik > --- Sorry, there was an error earlier in this line >> In C1: >> =IF(ROW()>COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROW()))) 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 SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > That is beautifully simple code. The adjustment I made is to extract > the actual numbers and not rows, ie: [quoted text clipped - 5 lines] > > The issue come up when you have data starting on Row X other than 1... |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.