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

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

— 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 ? ?”

2 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
2 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

2 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
2 years ago

You say just what I can read in the « error window ».
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

2 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
2 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
2 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

2 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.