Help on {=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))}

Dear All,
Plz help on this formula  
{=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))}, how this works as I m 
new excel user. what does is meant $A$1:$A$7=$A$10
0
10/16/2008 9:24:05 AM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
628 Views

Similar Articles

[PageSpeed] 46

Hi

As an array formula, it is testing each cell in the range A1 to A7 to see if 
it has the same value as that in cell A10
and returning what is the first row number that the value occurs in the 
range.

-- 
Regards
Roger Govier

"Sandeep Jangra" <SandeepJangra@discussions.microsoft.com> wrote in message 
news:9A133D2D-75A6-4A3B-93AB-6680D164C647@microsoft.com...
> Dear All,
> Plz help on this formula
> {=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))}, how this works as I 
> m
> new excel user. what does is meant $A$1:$A$7=$A$10 

0
Roger
10/16/2008 10:39:06 AM
Expanding a little on Roger's reply.

This part

IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)

as Roger said, checks each value in A1:A7 against A10, and it builds an 
array of values of that row number where it is the same else it returns 
FALSE. The end result is an array of row numbers that match or FALSE where 
they do not match.

The SMALL then grabs the lowest value from this array as the final result. 
Serendipitously, SMALL ignores the FALSE, it doesn't choke on it, so it 
ensures the first matching row number.

One problem is that if there is no match, it return #NUM. This can be 
catered for with

=IF(COUNTIF($A$1:$A$7,$A$10),SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),"no 
match")

-- 
__________________________________
HTH

Bob

"Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message 
news:umbBst3LJHA.728@TK2MSFTNGP03.phx.gbl...
> Hi
>
> As an array formula, it is testing each cell in the range A1 to A7 to see 
> if it has the same value as that in cell A10
> and returning what is the first row number that the value occurs in the 
> range.
>
> -- 
> Regards
> Roger Govier
>
> "Sandeep Jangra" <SandeepJangra@discussions.microsoft.com> wrote in 
> message news:9A133D2D-75A6-4A3B-93AB-6680D164C647@microsoft.com...
>> Dear All,
>> Plz help on this formula
>> {=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))}, how this works as 
>> I m
>> new excel user. what does is meant $A$1:$A$7=$A$10
> 


0
BobNGs (423)
10/17/2008 9:58:55 AM
thank you, Phillips... Thanks alot

"Bob Phillips" wrote:

> Expanding a little on Roger's reply.
> 
> This part
> 
> IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)
> 
> as Roger said, checks each value in A1:A7 against A10, and it builds an 
> array of values of that row number where it is the same else it returns 
> FALSE. The end result is an array of row numbers that match or FALSE where 
> they do not match.
> 
> The SMALL then grabs the lowest value from this array as the final result. 
> Serendipitously, SMALL ignores the FALSE, it doesn't choke on it, so it 
> ensures the first matching row number.
> 
> One problem is that if there is no match, it return #NUM. This can be 
> catered for with
> 
> =IF(COUNTIF($A$1:$A$7,$A$10),SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),"no 
> match")
> 
> -- 
> __________________________________
> HTH
> 
> Bob
> 
> "Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message 
> news:umbBst3LJHA.728@TK2MSFTNGP03.phx.gbl...
> > Hi
> >
> > As an array formula, it is testing each cell in the range A1 to A7 to see 
> > if it has the same value as that in cell A10
> > and returning what is the first row number that the value occurs in the 
> > range.
> >
> > -- 
> > Regards
> > Roger Govier
> >
> > "Sandeep Jangra" <SandeepJangra@discussions.microsoft.com> wrote in 
> > message news:9A133D2D-75A6-4A3B-93AB-6680D164C647@microsoft.com...
> >> Dear All,
> >> Plz help on this formula
> >> {=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))}, how this works as 
> >> I m
> >> new excel user. what does is meant $A$1:$A$7=$A$10
> > 
> 
> 
> 
0
10/17/2008 11:46:00 AM
Reply:

Similar Artilces:

Can any one help me for synchroniing outlook and squirrel mail
Hello , I have a problem with my outlook. The mails sent from squirrel mail are not being appeared(In sent items folder) in Outlook and even the vice-versa. Can any one help me out. Our Boss is going nuts on me for this small reason. Thank you for any kind of help. What is Squirrel Mail and how does it relate/connect to Outlook? "My Discussion" <phanikumar15@gmail.com> wrote in message news:1141286432.289395.245010@v46g2000cwv.googlegroups.com... > Hello , > > I have a problem with my outlook. > > The mails sent from squirrel mail are not being appeared(...

HELP.
i need a vba script that will select any cell interiorcolor = 36 and then copy special paste value? Help is a lousy subject line and your explanation leaves a lot to mind read over. Could you try again. -- Don Guillett SalesAid Software donaldb@281.com "uobt" <uobt@aol.com> wrote in message news:1103129207.620790.146460@z14g2000cwz.googlegroups.com... > i need a vba script that will select any cell interiorcolor = 36 > and then copy special paste value? > UOBT, Try something like this . . . Sub CopyData() Dim Rng As Range For Each Rng In Range("A1...

Russ, can ya help?
I have a rather large (36mb) .pst file from Outlook 2000 I am trying to import into OL 2003. It only pulls in a portion - I am losing approx 2/3 of what I should have. I tried the inbox repair tool, but to no avail. I saved this to a CDRW, and when I try and copy the entire file to another location, I get a "cyclic redundancy check" error messaage. Any ideas? What did the Inbox Repair Tool report? Are you unable even to move the file off the CD and on to the hard drive? Do you still have access to the original PST file? If so, do not export it. Just copy it. -- Russ Vale...

Help Need for Sales Comparison Report
I have a table of daily sales figures. Fields are: WE (Numberic for Week Number) Date (Date) CashSales (Currency) ChargeSales (Currency) Credits (Currency) What I need to be able to do is produce a report that shows a comparison of all sales by Week Number from 2007 vs current 2008 year, grouped by Week Numbers. Example: W/E 1 Date Cash Sales Charge Sales Credits Date Cash Sales Charge Sales Credit 1-1-07 $100.00 $50.00 $0 1-2-08 $110.00 $65.00 $0 1-2-07 $200.0...

Windows 7 home and Outlook 2007
Is Windows 7 home compatible with outlook 2007? Thanks. I think you mean is Outlook 2007 compatible with Win7, and yes it is "Sydney" <Sydney@discussions.microsoft.com> wrote in message news:B6C37FDE-7116-4DB7-8B64-42348FE7E3DB@microsoft.com... > Is Windows 7 home compatible with outlook 2007? > > Thanks. Yes, make sure you install Service Pack 2 and additional updates for Office 2007 after installing though. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downl...

Not sending mail thru Dfault account . Help pls.!!!
Hi all, I have Exchange server installed on SBS 2000 server. My users use outlook 2002 as there e-mail client. I have configured exchange server but this server is only for internal use (it can't send or recive any external mail). I have set it up cos I wanted to make use of "Public Folders" functionality. This alowes you to share Calender, Task, Contacts.... with other users. The real E-mail account (the one that can send recive external mail) is account provided by ouwer internet provider. So I am useing SMTP account as a default account in outlook and I have configured ou...

How do I link columns so data flows from 1 column to another like.
Is there a way to link columns in Excel, so that data that's entered flows from one column to the next? There's a function similar to this in Quark. Anybody know anything about this? Regards, If I understand the question correctly, and the information is being typed into the cells, you could try this: Select a block of cells, say A2:C10 Then, type a value. Press enter. If yuo press enter after each value entered, when you enter a value in A:10 and press enter, you're active cell will be B2. tj "M. Frazel" wrote: > Is there a way to link columns in Excel, so t...

Help with ldifde or adfind AD<>Exchange
* Also posted in the AD forum Hi, I have a request to separate mail-enabled contacts and legitimate exchange mail boxes which are combined in our exchange organization. I'm trying to get this data from the following attribute: msExchHomeServerName msExchHomeServerName: /o=OrganizationName/ou=Mail-01/cn=Configuration/cn=Servers <--- this is where the contacts & mailboxes are combined Here's a sample ldifde command: ldifde -f output.ldf -s dc12003 -d "dc=snetworks,dc=com" -p subtree -r "(&(objectCategory=person)(objectClass=User)(msExchHomeServerName=...

windows media player #11 08-10-10
Hello to all, I need some help from some of you. Friends send me e-mails sometimes that has short movies in them. The media player plays most of them. The ones that wont play has a pop up that says, This file does not have a program associated with it for performing this action. Create an association in the folder option control panel. I guess I am not smart enough to do this myself. I would appreciate any help from you people that are more savy than me. Thanks a lot Thomas Stevens "TJ STEVENS" <tsteve...

Windows 7 Live Mail
How can I automatically block an e-mail address. I have asked the person to stop sending me mail to no avail. Thanks. Bert Right-click on the sender in a message from them, select "Junk e-mail" - "Add to blocked senders list" -- Dave N. MS-MVP (Mail) Windows 7 Ultimate http://download.live.com/wlmail "bert" <bertpickover@optonline.net> wrote in message news:8B8F6478-5625-4090-8A33-697D248E9832@microsoft.com... > How can I automatically block an e-mail address. I have asked the person > to stop sending me mail to no...

Desperate for help on assending/decending macros
I have built a worksheet that is full of data I need to review it b specific year as well as assending and decending order. This workshee also needs to be protected. I built three buttons with macros. One for return to main menu. and two more to have the user view the data in assending or decendin order. I also have a auto filter for specific dates. Without protecting the worksheet, two of the macros work fine. Th assending macro brings 0 values to the top and I need to have a valu greater that 0 in this macro. I don't know how to do that. When I protect the worksheet the return macro and...

Opening links from Outlook in Mozilla Firefox
I have a problem and would appreciate some help. When I open a mail in Outlook and it contains a link to a web page and I wish to follow it, it doesn't do it for me. Instead it opens an error window with "Microsoft Outlook" in the window title, saying "This operation has been cancelled due to restrictions in effect on this computer. Please contact your system administrator." I've tried with different settings of Firefow and Outlook, I've tried Help files and internet tutorials, but I can't manage to fix this. The machine in question is a home ...

duplicate entries in rows
I have a list of names from a few different sources, I need to compile them into one list, and would like to eliminate any multiple entries by using, I'm assuming, a filter. To clarify what I need, here is an example: here are the rows: mitch gary clark mitch I'd like to use a filter where I could select all of the names, and eliminate the duplicate entry of the name "mitch". Is there any easy way to do this? Thank you. Hi use 'Data - Filter - Advanced Filer' and check 'Unique entries' -- Regards Frank Kabel Frankfurt, Germany Jim wrote: > I hav...

XP to Windows 7 Outlook 2003 to 2007 contacts no longer address b.
Used Belkin Easy Transfer/Windows Easy Transfer process to copy files from Windows XP (2 years old) computer to new Windows 7 computer. Went from Office Pro 2003 to Office Basic 2007. Everything seems ok -- Outlook has all of my old emails, my sent emails, etc. Contact list looks good. Calendar is fine. But when I try to send an email, there's no address book and I can't seem to find the proper place to reconnect the contact list as the address book. All of the previous questions like this are from 2006-2008, going from XP to Vista and the Microsoft files I've...

Function that helps me find the middle value
Hi, I need a function that helps find the middle value. For example, if I have 12, 23 and 100, it must show 23. Or if I have 1,2,3 must show 2. Thanks Here's a generalised solution to the problem: =3DLARGE(A1:A10,ROUNDUP(COUNT(A1:A10)/2,0)) Your numbers can be anywhere in the range A1:A10 (as an example), and works best if you have an odd number of numbers. Hope this helps. Pete On Dec 18, 1:31=A0pm, Diana <dianamrco...@gmail.com> wrote: > Hi, > > I need a function that helps find the middle value. For example, if I > have 12, 23 and 100, it m...

If Then Help
Hi, how would I say: if cell e1 is a positive number, then return "win". If cell e1 is a negative number then return "looser". Thank you On Fri, 8 Jan 2010 12:48:01 -0800, nabanco <nabanco@discussions.microsoft.com> wrote: >Hi, how would I say: > >if cell e1 is a positive number, then return "win". If cell e1 is a >negative number then return "looser". > >Thank you Try this formula: =CHOOSE(SIGN(E1)+2,"looser","E1 is zero","win") Hope this helps / Lars-�ke There are ...

Who could help me about interface?
Hello all: I write some COM,and I find some interfaces are registered ,some ones are not registered under HKEY_CLASSES_ROOT\Interface,why? Its upto the server to register interfaces. Not all interfaces need to be registered. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Lee Tow" <fbjlt@pub3.fz.fj.cn> wrote in message news:uI$6sKkiHHA.3512@TK2MSFTNGP06.phx.gbl... > Hello all: > I write some COM,and I find some interfaces are registered ,some ones > are > not registered under HKEY_CLASSES_ROOT\Interface,why? > > Could you tell me in detail...

format new rows
My table looks like this: A B C D E 1 Organisation Adress Contact Outcome 2 Employer 3 Industry 4 Schools 5 Referral 6 Employment 7 Groups 8 Training 9 Community 10 Media 11 Other Jacob Skaria was able to help me generate a new row when data is entered into each category [(eg when new data is added to B2 a new line will automatically appear at 3 to allow room for new data for the employer category). A new line would insert for each of the column A categories in the same ...

Help!! urgent
I was using Excel 2007 quite happily, and must have hit something on the keyboard, because now every time I touch keys windows spring open and I am powerless to do anything on my spreadsheet. I have tried restarting the computer. How do i turn off this feature? On Mar 9, 3:21=A0am, Anthony B <Anthony B...@discussions.microsoft.com> wrote: > I was using Excel 2007 quite happily, and must have hit something on the > keyboard, because now every time I touch keys windows spring open and I a= m > powerless to do anything on my spreadsheet. I have tried restarting the &g...

Need help with calendar sharing
Our secretary uses outlook 2003 to make appointments for executive. The executive shares her outlook 2003 calendar with the secretary. We've encountered a problem where the secetary will make a 10 am appointment on executives calendar, but it shows up as 11 am on outlook at executives pc. This happens for any time period. Always 1 hour difference. This just started happening and just on this secretary/executive combo. Nothing was added or modified on either pc. workstations = winxp and outlook 2003 exchange 5.5 Has anyone run into this problem? Thanks, Emin Parsu HJACenter ...

help with pivot tables
I have created a spreadsheet which has multiple pivot tables the only problem is that my source data can contain anything from one item to fifty items therefore the pivot tables can overlap if two adjoining pivot table both contain alot of information, how do I solve this problem I want the pivot table when refreshed to insert and move everthing else either down or across plese help ...

Can't See Rows (not hidden)
I have been sent a spreadsheet where many of the rows are "hidden" and I need to see them. They aren't hidden in the typical hide/unhide way and I can't figure out how to open them up. Can anyone suggest what I can do to "unhide" the rows? Hi Probably the row height is set to 0. Select the some range of rows with missing ones included, and set new row height (p.e. 12.75) -- When sending mail, use address arvil<at>tarkon.ee Arvi Laanemets "ChrisW" <ChrisW@discussions.microsoft.com> wrote in message news:B2487060-69DA-466A-ACC7-1B5CC5F0...

-Help
I've created a quartely report, that only shows the relevant quarters. Meaning, if I run for Jun08, It will show Jun08, Mar08, Dec07. If I prompt the same report for Sept, It will give me Sept08, Jun08, Mar08, Dec07. So the most it can be is 5 columns. (Dec08, Sep08, Jun08, Mar08, Dec07), and the least it would probably be is 2 columns (Mar08, Dec07). The problem I'm having is the margins and pagebreaks were set up, when the report was generating 4 columns, and when I select a different month, that prints one or two less columns, the font size seems to expand and messes up the ...

Custom workflow
I'm REALLY struggling with creating a custom workflow. If I describe the scenario, maybe some kind person might point the right direction.... Our CRM uses a Service Activity to schedule training for Contacts. We open a Case, attach a new Service Activity, and on the Service Activity form there is a "Customers" field which allows you to attach multiple Contacts. We've added a custom picklist called Service Status to the form. When the Service Activity is completed (by changing the Status to "Complete"), we need to automatically updated all of the associated Cont...

Go to Page 1
Hi! I have a main form with TabCtl with 10 pages. In this main form I have one combo to choose record. I woul like when click in combo oblige go to Page 1 of this record. Is it possible, please? Thanks in advance. an Hopefully this will help you. http://www.thescripts.com/forum/thread615446.html -- Hope this helps, Daniel P "an" wrote: > Hi! > > I have a main form with TabCtl with 10 pages. > In this main form I have one combo to choose record. > I woul like when click in combo oblige go to Page 1 of this record. > Is it possible, please? > Thanks ...