I shouldn't be doing this manually, right? Making a cumulative bonus calculator with variable tiers.

Hi Patrick,

"Is there a way/function to select the Tier (C14:F14) and have the bonus paid amount automatically pulled from the table? Then I can work on making a design that is clear, lock the cells and share this with others. "

Are the numbers in row 14 entered manually? Are they the result of a calculation?

Given the values in rows 3 to 14 and the 'tier' value in for 14, extracting the Bonus Paid amount is a straightforward lookup process.

Using VLOOKUP:

The formula shown below the table is entered in C15, then filled right to F15

VLOOKUP(C14,$A:C,COLUMN(),FALSE)

As it is filled right, the $A reverence to the first column of the lookup table will remain constant, and the references to C14 and C will change to match the column in which that copy of the formula is inserted.

Empty cells in the example are cells containing information not required by this formula.

Regards,

Barry

Hi Patrick,

"I changed your formula

VLOOKUP(C14,$A:C,COLUMN(),FALSE)

to

VLOOKUP(C14,$A:C,3,FALSE)

and when I right fill this it still references column 3. Is there (a way to make this work relatively?

Yes. Replace 3 with COLUMN().

"3"               tells the formula to return the amount from the third column of the lookup table (columns A to C of 'this table'.

COLUMN() tells the formula to return the amount from 'this column' of the lookup table, by returning the number (3) of the column containing the formula (C).

The range $A:C expands rightward as the formula is filled right. The search column (A) is fixed (or 'preserved' to use the term in the Formula Editor, by the $ operator, but the C increments by one column as the formula is filled right, to always match the column that the formula is in, redefining the lookup table to include the column from which the result is to be returned.

"Can I make the column reference relative so that B14:F15 could move elsewhere on the sheet?"

"Sheet" in Numbers refers to a large "canvas" on which one or more objects can be placed.

When created, a Sheet usually contains a single Table, and that table has the default name "Table 1".

A Table is a collection of cells arranged in rows and columns. these cells can contain entered data, or they can contain formulas.

In Numbers, Formulas can collect information fro other cells on the same Table or from cells on other Tables. these Other tables ma\may be on the same Sheet as the table containing the formula, or on other Sheets, but must be in the Dame Document.

So, rephrasing your question:

Can I move the cells B14-F14 to a different set of cells on the same table or on a different table?

Yes, provided that the different 'set of cells' is in the same Document.

Here's an example, made by separating the bottom rows from the original table to form a separate table, then inserting two columns between the original A and B (on Table 2 only) to place the results formulas in columns different from the columns they occupied in Table 1.

Inserting those columns created a reference error and an error triangle in each of the four cells containing the original formula. This was easily corrected by adding an 'offset' value (-2) to the COLUMN() reference, then filling the edited formula right to the remaining three cells. The revise formula is shown below Table 2. (Changes other than the -2 were made automatically by Numbers when Table 1 was split into Table 1 and Table 2.

Original formula in Table 1::C2:   VLOOKUP(C14,$A:C,COLUMN(),FALSE)

Revised formula in Table 2::E2:   VLOOKUP(E2,Table 1::$A:C,COLUMN(cell)−2,FALSE)

italic: Change made by Numbers

bold: Change made by user.

Regards,

Barry