+ Reply to Thread
Results 1 to 2 of 2

Translate formula into a sentence

Hybrid View

  1. #1
    Registered User
    Join Date
    09-15-2008
    Location
    Rochester
    Posts
    2

    Translate formula into a sentence

    Can anyone help me explain this formula in a sentance that a non-excel user

    =INDEX(OldExchange,SMALL(IF(COUNTIF(NewExchange,OldExchange)=0,ROW(OldExchange)-ROW(A$3)+1),ROWS($1:1)))

    OldExchange is a named range of A3:A305
    NewExchange is a named range of B3:B186

  2. #2
    Valued Forum Contributor
    Join Date
    08-26-2008
    Location
    singapore
    Posts
    626
    This formula is to return all the data that only in oldexchange.
    Index() function returns the ?th cell in the range selected, in this formula, oldexchange. ? Will change according to the value returned by small() function.
    small() function returns the ?th smallest row number. ? Will changed by the rows() function when you copy this formula down.
    if() function to return an array of row numbers of all the data only in old range.
    * row number is compare to cell A3, not A1

    *since this is an array formula, remember to press shift+ctrl+enter instead of enter after you modified it.

    Hope I can have made myself understandable
    I need your support to add reputations if my solution works.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula for bond price
    By Dracan in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-15-2014, 11:17 AM
  2. A formula template
    By ajaysehgal in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-07-2013, 06:12 AM
  3. Conditional formula question
    By odditie in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-27-2007, 09:47 PM
  4. How do I subsitute part of a formula linked to another workbook?
    By Scej12 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-03-2007, 10:10 AM
  5. use cell content as cell address in formula
    By nnsc in forum Excel General
    Replies: 4
    Last Post: 11-05-2006, 03:12 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1