MacMost Q&A Forum • View All Forum QuestionsAsk a Question

How and Where To Use, With Examples, the « ADDRESS » Function In Numbers ? ?

The only information about « ADDRESS » function is this :
— Constructs a cell address string from separate row, column, and table identifiers —

Someone told me that I had to use this function to do what I want to do.
I know how to « write » the function in a cell but I don’t know how to use it in relation with others cells. I want some examples where to use it.
—–
Zattza

Comments: 8 Responses to “How and Where To Use, With Examples, the « ADDRESS » Function In Numbers ? ?”

    4 years ago

    Impossible to give you a good example without knowing what you are trying to do. ADDRESS is a rarely-used function.

    If you put ADDRESS(2,2) you get the result $B$2. That typically wouldn't be the result you are looking for. You would usually put that into another function to use in some way.

    So what is it that you want to do?

    Zattza
    4 years ago

    You say « You would usually put that into another function to use in some way ».
    Its just want to know how to do that.
    I want some examples where you put the result of that function into another function ?
    Like this, if I put that function « =ADRESSE(12;2;1;1) » into the cell B2 that references to cell B12 like this « $B$12 ». After I put the number 23 into the cell B12 and into the cell C4 I put 17.
    Why, I get an error, when I put (B2 + C4) and not 40 ?

    Zattza

    4 years ago

    Zattza: You get an error because C4 contains the number 17 and cell B2 contains the text string "$B$12". This is just 5 text characters: "$B$12" and not a number. You can't add 17 and the characters "$B$12" as they need to both be numbers.
    If you put just $B$12 into B2 then it will calculate the result of that formula, which is just a single reference to another cell. The result will be shown in B2 as 23 since that is what you have in B12. Then B2+C4 is 17 + 23 which is 40.
    So in this example, then you don't want the ADDRESS function at all. You just want the formula $B$12 in cell B2.

    Zattza
    4 years ago

    You say just what I can read in the « error window ».
    So, you don't help me about the « ADDRESS » function.
    That's why I want someone to give me a simple example in which that function is used.
    If not, can you tell me what is the utility of that function ?

    Zattza

    4 years ago

    Zattza: I can't think of any example offhand. I've never had to use that function. Perhaps it is only there for compatibility with very old spreadsheets. But it doesn't matter. What matters is what you are trying to do and what formulas will help you. So explain what you are trying to do.

    Zattza
    4 years ago

    1 —
    I want this:
    ROW 1, from COLUMN 3 to COLUMN 14 = (amounts that will never change)
    ROW 2, from COLUMN 3 to COLUMN 14 = (ROW 3 absolute)-(ROW 1)

    From ROW 3
    COLUMN A = (date)
    COLUMN B = (number changing every day)
    COLUMN C to COLUMN L = (COLUMN B)*(constant value)

    Every day, I add a row above ROW 3 placing the date and value for COLUMN B.
    ROW 3 are then placed in ROW 4.
    The columns of ROW 2 then become (ROW 4 absolute)-(ROW 1) which is no longer (ROW 3 absolute)-(ROW 1).
    — See next comment —

    Zattza
    4 years ago

    2 —
    I want the columns in ROW 2 to keep the reference with ROW 3. I do not want the reference to follow the number of the ROW where it ends up after the creation of the new row.

    This is where the "ADDRESS" function should come into play using its result which would be the reference to ROW 3 and the reference of ROW 4.

    So, how can I put the result of the "ADDRESS" function in the cells of ROW 2 in order to always get (ROW 3 absolute) in the formula (ROW 3 absolute) - (ROW 1) ?

    Zattza

    4 years ago

    You wouldn't use the ADDRESS function for that. You would use INDIRECT.
    So the formula in C2 is ABS(C3)-C1, is that correct?
    Then when you insert a row above row 3, it changes to ABS(C4)-C1 which is not what you want. You want it to stay ABS(C3)-C1.
    So instead use ABS(INDIRECT("C3"))-C1
    Then the "C3" inside the indirect doesn't change when you insert the new row.
    It will mean you need to set the other columns D-L to have the correct cell in the quotes: "D3" and "E3" and so on.
    You could also use the INDEX function for this.
    So ABS( INDEX(3:3,0,COLUMN()) ) - C1
    What that does is take row 3 (3:3, you get that by clicking on the row number on the left) and looks for row 0 and column COLUMN() (the same number as the current column).
    The advantage to that is you can copy cell C3 and paste it into D3-L3 without changing anything.

Comments Closed.