What's this excel formula do?

[mcphee]

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.

 

[Aladin Akyurek]

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.

 

[mcphee]

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