Formatting cell in Numbers

Formulas in Numbers won't distinguish between the red and green figures. But if you enter the negative numbers as negative (not just red) then you can do something like this:

In B7:

=SUMIF(B,"<0",B)

In B8:

=SUMIF(B,">0",B)

The rows containing the formulas (here 7 and 8) are defined as Footer Rows so that the formulas can refer to the same column that contains them.

SG

Hi McKinney,

In Numbers:

A "Spreadsheet Document," often shortened to "Spreadsheet" or "Document" is the whole document—what gets saved as "My Document.numbers when you click Save, and name the file "My Document"

A Numbers Document is created from a Template; a file set to open an unnamed copy of its Document that is independent of the original.

A Document contains at least one Sheet (default name: Sheet 1).

The Sheet is a large 'canvas' on which may be placed one or more Tables, Charts, Images, Text boxes or other objects.

Each sheet is attached to a Tab, a small label, containing the name of the Sheet, in a bar across the top of the Document Window. Clicking on a tab in a multi Sheet Document will take you to the Sheet named in the Tab.*

Each Sheet, when created, contains one Table (Default name: Table 1)

You can add a Sheet (including a default Table named Table 1) by clicking the + sign at the left end of the tabs bar.

You can Duplicate a Sheet (including all its contents) by Clicking its Tab, then pressing command-D

A Table is a set of cells, arranged in a rectangular array of Columns and Rows.

New Tables can be added to a Sheet by several methods:

• • Click the Table button, then choose the style of table you want to add.
  • Select an existing table on the sheet and press command-D to Duplicate that table. Drag the new table to a convenient location on the Sheet.
  • With a Table selected, click on a Column reference tab to select that column. Click a second time, and hold until the column 'rises' slightly from the Sheet, then, with the mouse button still down, Drag the column out of and away from the Table until it separates from the Table and becomes a second Table, independent of the first.
  • Select an existing table on the same Sheet or another Sheet, then Copy. Select a new location on the same Sheet or on another Sheet (or on a Sheet in another Document), then Paste.

Cells are the rectangular spaces making up Tables.

Cells contain wither entered Data, or Formulas which construct/calculate Data from several sources (other cells, the system clock, etc.)

A cell may contain an entered value, or a formula. Entering a value in a cell containing a formula replaces the formula with the entered value. Entering a formula in a cell containing a fixed value replaces the fixed value with the formula, and displays the value resulting from calculations by the formula.

Formulas may contain references to cells. The reference must contain as much of the cell's full address as Numbers needs to identify the cell.

• • If the cell containing the formula is on the same Table as the referenced cell, Numbers needs only the column letter and row number of the referenced cell: B2
  • If the cell containing the formula is on a different Table than the referenced cell, but on the same Sheet, Numbers needs the column letter and row number of the referenced cell plus the tablename of the table containing the referenced cell: Table 1::B2
  • If the cell containing the formula is on a Table that is on a different Sheet from the one containing the referenced cell, Numbers may need the full cell address of the referenced cell.

Examples:

formula in Sheet 2::Table 1::K2, Referenced cell is in Sheet 1::Table 1::B2. Numbers needs full address:

Sheet 1::Table 1::B2

formula in Sheet 2::My table::K2, Referenced cell is in Sheet 1::Main::B2, and 'Main' is the only Table in the Document with that name. Numbers needs only the table name and cell coordinates:

Main::B2

Regards,

Barry

*more accurately, "Clicking on a Tab will bring the sheet named in the tab to the front of the Document Window."

Glad it worked for you. And thanks for the green tick!

When you have time you might explore the advantages of tables "Numbers style" with Footer Rows for formulas. That way you don't have to use the "Excel-like" ranges I2:I47, etc. You can just use I. That's an advantage of the Numbers table-centric design where, unlike in other spreadsheets, you can easily have multiple smaller tables on the same sheet rather than having to deal with one big grid of cells.

SG

Numbers terminology: "Documents" contain "sheets" which have a canvas on which can be placed tables, charts, shapes, and other objects. Only tables have cells in rows and columns. This is different from other spreadsheet apps like Excel, where sheets have a big grid of cells and tables are used, but are not central to the design as they are in Numbers.

SG

Sorry I wasn't clear.

You posted a screenshot above showing some values in cells. I'm assuming that was from Numbers, right?

If so, it was a screenshot of a table in Numbers.

In that table you can define the bottom rows (up to 5) as Footer Rows (choose Footer Rows in the Table menu then the number of rows). You can then put formulas in those rows can refer to the same column they are in. So a SUMIF at the bottom of column can be simply

=SUMIF(I,"<0",I)

rather than your

=SUMIF(I2:I47,"<0",I2:I47)

And the range will expand automatically if you add new rows.

Recommend having a look at the templates at File > New in your menu to get a sense for how things can be done "the Numbers way."

SG