How do I name a specific cell in Numbers 3.2.2?

Question

Level 1

8 points

How do I name a specific cell in Numbers 3.2.2?

I am working with a multi-table spreadsheet and I want to create a table of variables that are used in a specific formula throughout the spreadsheet. For example, here is the formula I'm working on:

=IF(C32≥321,"23",IF(C32≥281,"19",IF(C32≥241,"17",IF(C32≥201,"15",IF(C32≥161,"13" ,IF(C32≥121,"11",IF(C32≥80,"09","")))))))

Yes, all you database builders out there, it's a wacky way to spreadsheetize an IF, THEN, OR statement - fortunately, I'm only working with numbers. What I want to do is replace the cell values and the "then" result numbers with references from another table. I can't just link to the specific cell by clicking (resulting in something like "<table>::<cell>") because the formula builder spits it out claiming there are too many arguments. Here is an example of what I'm getting at if you replace the first number in each statement with a cell name like "R_1" and the calculated displayed value as "V_1":

=IF(C32≥R_1,"V_1",IF(C32≥R_2,"V_2",IF(C32≥R_3,"V_#",IF(C32≥R_4,"V_4",IF(C32≥R_5, "V_5",IF(C32≥R_6,"V_6",IF(C32≥R_7,"V_7","")))))))

Right now, I have to manually make changes to the formula in one cell then copy it across the other cells in the different tables. I'd much rather have the formula reference specific cells in a variable table so that I can update the values globally. In the "help" for Numbers, it states:

"If the reference is to a cell in another table, the reference must contain the name of the table (unless the cell name is unique within all tables). For example:

=Table 2::B2

Note that the table name and cell reference are separated by a double colon (::). The name of the table is automatically included when you select a cell in another table while building a formula.

If the reference is to a cell in a table in another sheet, the sheet name must also be included (unless the cell name is unique within all the sheets)." (emphasis mine)

However, I can't find any documentation to instruct me on how to create a unique cell name. Any advice?

Numbers-OTHER, OS X Mavericks (10.9.5)

Posted on Oct 1, 2014 9:06 AM

Reply

Answer 1

coop1108 Author

Level 1

8 points

Oct 2, 2014 5:49 AM in response to t quinn

t quinn, you are right, the "named cell" issue is not the source of my problems and your lookup table suggestion worked great.

However, once more, I'm looking to find how you name a cell, if possible. Not for the problem I stated earlier, but in general. The documentation mentions it, but there doesn't seem to be a way to do it. In Excel, each cell can have a specific name and that name can be used throughout the spreadsheet as an absolute reference in formulas. It's as easy as clicking on the cell and, in the display of the cell name, you just highlight the name and change it. The documentation leads one to believe there is capability to do this in Numbers, but I don't seem to be able to find it in the app. Perhaps it was from an earlier version or it was a feature that got left out but documentation had already been created. I'm just curious at this point, since using a lookup table has solved my immediate problem.

Reply

Answer 2

t quinn

Level 5

7,063 points

Oct 1, 2014 2:41 PM in response to coop1108

Hi coop,

Here is a way to approch your problem with LOOKUP()

The lookup table is all entered data.

I copied the formula in B1 down to B4 so you could see more results. The formula in B4 =LOOKUP(A4,lookup::A,lookup::B)

I created this formula by clicking on the cell or column I needed. Much less chance of typos.

quinn

Reply

Answer 3

coop1108 Author

Level 1

8 points

Oct 1, 2014 2:51 PM in response to t quinn

t quinn, thanks! That certainly does clean up the mess of nested IF statements. I'll try it in the actual formula tomorrow. That cleans up my immediate problem.

However, I still have no answer for how to name a specific cell for use across multiple sheets. It's a feature I used extensively in Excel spreadsheets and, while the documentation points to it existing, there doesn't seem to be any specific documentation available.

Reply

Answer 4

t quinn

Level 5

7,063 points

Oct 2, 2014 11:46 AM in response to coop1108

Hi coop,

To the best of my knowledge the name of a cell in Numbers is its address. In your example in your initial question:

=Table 2::B2

It is considered a bad practice to use the following option because it can lead to unnecessary problems but in the interest of making a joke and acknowledging your commitment to Knowledge here you are.

There is a preference to use header names as labels that can give you this:

And yes, I can reverence this cell from another sheet by typing "=very bad". It is still just an address.

quinn

Reply

Answer 5

t quinn

Level 5

7,063 points

Oct 1, 2014 9:49 AM in response to coop1108

Hi coop,

LOOKUP() was made for your situation. It will be a simple formula when you are done. You will need to create a lookup table just as you have planned.

Check out this thread for an example:

IF formula advice please

quinn

Reply

Answer 6

Yellowbox

Level 7

21,945 points

Oct 1, 2014 9:32 AM in response to coop1108

Hi coop,

coop1108 wrote:

IF, THEN, OR

I think of the IF function as IF(this is true, THEN do this, ELSE do that)

From the Function Browser: type = in any cell, then type 'if' (no quotes) in the search box.

The IF function returns one of two values, depending on whether a specified expression evaluates to a boolean value of TRUE or FALSE.

IF(if-expression, if-true, if-false)

if-expression: A logical expression. if-expression can contain anything as long as the expression can be evaluated as a boolean value. If the expression evaluates to a number, 0 is considered to be FALSE, and any other number is considered to be TRUE.
if-true: The value returned if if-expression is TRUE. if-true can containany value. If if-true is omitted (there’s a comma, but no value) and if-expressionevaluates to TRUE, IF will return 0.
if-false: An optional argument specifying the value returned if if-expression is FALSE. if-false can contain any value. If if-false is omitted (there’s a comma, but no value) and if-expressionevaluates to FALSE, IF will return 0. If if-false is entirely omitted (there’s no comma after if-true) and if-expressionevaluates to FALSE, IF will return FALSE.

Or more to the point,

IF it is sunny, THEN have a picnic, ELSE stay in bed.

Call back with further questions. Maybe a LOOKUP function will work for you.

Regards,

Ian.

Reply

Answer 7

coop1108 Author

Level 1

8 points

Oct 1, 2014 10:05 AM in response to Yellowbox

No, IF/THEN/ELSE arguments won't work (yeah, brainfart typo) in Numbers formulas, at least not ones that have seven steps to them. What I am doing is taking a reading of a specific amount of a chemical in a test and then adding an amount of a second chemical based on the amount of the first (expressed in a range). I would take the IF/THEN/ELSE approach when working with a database, but Numbers is not that sophisticated.

So, to put my argument in an English-ey way,

If the test shows the amount of chemical x in the test is equal to or greater than 321,

then add 25ml of chemical y

Else,

If the test shows the amount of chemical x in the test is equal to or less than 320 but equal to or greater than 281,

then add 21ml of chemical y

Else,

If the test shows the amount of chemical x in the test is equal to or less than 280 but equal to or greater than 241,

then add 19ml of chemical y

And so forth.

The formula I have works just fine, it's just when I have to change either the ranges or the amounts, I have to make the change to the formula in one cell, then copy that cell to all other cells that use the formula for calculation. So, what I'm trying to do is create a formula that uses specific cell names as variables instead of fixed values in the formula. The "help" for Numbers specifically references the ability to give cells a specific, unique, global name and to use those names in formulas instead of the <table>::<cell> type of reference, and I'm trying to find out how to do it.

Reply

Answer 8

coop1108 Author

Level 1

8 points

Oct 1, 2014 10:10 AM in response to t quinn

Thanks for the point to Lookup tables. I tried earlier, but little luck. I'll try again and see if that can solve the problem in a more streamlined way. Of course, this still doesn't answer the original question of naming specific cells...

Reply

Answer 9

t quinn

Level 5

7,063 points

Oct 1, 2014 3:17 PM in response to coop1108

Hi coop,

I don't think having a unique cell name is the center of your troubles. When you are clicking in the cell you want to add to the formula in your original question you are perhaps not getting it where in the formula you think you are. If Numbers is telling you there are too many arguments you need to check out how many commas have ended up in your formula. A screenshot would be necessary to tease out what is going wrong. With 7 nested IFs it is so easy to misplace something. By expanding your lookup table you could expand your range of responses without modifying the lookup formula at all.

A unique cell name in my example would be lookup::A2. There are no other tables named "lookup". The name of the cell includes the table name.

Numbers is really friendly if you like to point and click and I want to encourage you to try this. If my lookup table is on sheet 2, I would navigate to that sheet as I am building the formula and click on the cell or column i need. If I somehow have another table "lookup" on, say, sheet 3 the formula will read "sheet 2::lookup::A2". If it is indeed unique, Numbers will create the formula to read "lookup::A2".

quinn

Reply