Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: What's this excel formula do?

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can you tell me what this formual does? Need to know why it's pulling the result it is. (Previous user's spreadsheet.)

    =IF(ISERROR(F2),0,LOOKUP(C2,INDIRECT(ADDRESS(F2,5,,,F$1)):INDIRECT(ADDRESS(F2,15,,,F$1)),INDIRECT(ADDRESS(F2+1,5,,,F$1)) :INDIRECT(ADDRESS(F2+1,15,,,F$1))))

    Thank you.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,635
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-02 09:55, mcphee wrote:
    Can you tell me what this formual does? Need to know why it's pulling the result it is. (Previous user's spreadsheet.)

    =IF(ISERROR(F2),0,LOOKUP(C2,INDIRECT(ADDRESS(F2,5,,,F$1)):INDIRECT(ADDRESS(F2,15,,,F$1)),INDIRECT(ADDRESS(F2+1,5,,,F$1)) :INDIRECT(ADDRESS(F2+1,15,,,F$1))))

    Thank you.
    It's doing a lookup on a computed range including the file+sheet where the range should be.

    ADDRESS(F2,5,,,F$1)

    is the essential bit, where

    F2 houses a value that is treated as a row number, 5 is the column number, and F$1 contains a (file plus) sheet name.

    The Help file of Excel has the following info on ADDRESS:



    ADDRESS

    Creates a cell address as text, given specified row and column numbers.

    Syntax

    ADDRESS(row_num,column_num,abs_num,a1,sheet_text)

    Row_num is the row number to use in the cell reference.

    Column_num is the column number to use in the cell reference.

    Abs_num specifies the type of reference to return.

    Abs_num Returns this type of reference
    1 or omitted Absolute
    2 Absolute row; relative column
    3 Relative row; absolute column
    4 Relative


    A1 is a logical value that specifies the A1 or R1C1 reference style. If a1 is TRUE or omitted, ADDRESS returns an A1-style reference; if FALSE, ADDRESS returns an R1C1-style reference.

    Sheet_text is text specifying the name of the worksheet to be used as the external reference. If sheet_text is omitted, no sheet name is used.





  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin,

    Thank you very much for your time and kind reply.

    I previously read the Help File info concerning ADDRESS, but wasn't clear on what was going on w/this formula. I am still unclear on how this formula is working, as it presents a different result based on different numbers (quantities) entered into cell C2. I don't see what is telling the formula cell to choose which results. (IE: a low quantity entered produces a higher result/price, a high quantity entered produces a lower result/price.) If I understand what is happening, I am looking for where the quantity breakdowns are telling the formula which prices to produce as the result and where those prices are. I don't see those prices in an associated file anywhere.

    Appreciate your help and time,
    mcphee

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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