How do I apply a calculation to row of cells

You can't do that directly because a formula can't apply to its own cell.
You may duplicate your table then in cells of the replicate insert formula like this one designed for the cell B2.
=1+originalTable :: B2

When it's done for every cells :
select all
copy
paste value.

For those using Numbers '09, an alternate solution would be to use this simple script :

--

--[SCRIPT add-oneto_selectedcells]
(*
Enregistrer le script en tant que Script : add-oneto_selectedcells.scpt
déplacer le fichier ainsi créé dans le dossier
<VolumeDeDémarrage>:Users:<votreCompte>:Library:Scripts:Applications:Numbers:
Il vous faudra peut-être créer le dossier Numbers et peut-être même le dossier Applications.
Sélectionner un groupe de cellules
aller au menu Scripts , choisir Numbers puis choisir add-oneto_selectedcells
Le script ajoute 1 à toutes les cellules occupées par des nombres.
ATTENTION : comme il est écrit pour un calendrier, le script considère qu'une cellule dont la valeur extraite est 0,0 est une cellule vide.
--=====
L'aide du Finder explique:
L'Utilitaire AppleScript permet d'activer le Menu des scripts :
Ouvrez l'Utilitaire AppleScript situé dans le dossier Applications/AppleScript.
Cochez la case "Afficher le menu des scripts dans la barre de menus".
--=====
Save the script as a Script: add-oneto_selectedcells.scpt
Move the newly created file into the folder:
<startup Volume>:Users:<yourAccount>:Library:Scripts:Applications:Numbers:
Maybe you would have to create the folder Numbers and even the folder Applications by yourself.
Select a gropup of cells.
go to the Scripts Menu, choose Numbers, then choose "add-oneto_selectedcells"
The script add one to every cell filled by a number.
CAUTION : As it was written for a calendar, the script treat cells whose extracted valu is 0.0 as blank ones.
--=====
The Finder's Help explains:
To make the Script menu appear:
Open the AppleScript utility located in Applications/AppleScript.
Select the "Show Script Menu in menu bar" checkbox.
--=====
Yvan KOENIG (VALLAURIS, France)
2010/06/08
*)
--=====
on run
 set {dName, sName, tName, rname, rowNum1, colNum1, rowNum2, colNum2} to my getSelParams()
 tell application "Numbers" to tell document dName to tell sheet sName to tell table tName
 repeat with r from rowNum1 to rowNum2
 tell row r
 repeat with c from colNum1 to colNum2
 try
 set aVal to value of cell c
 set aVal to aVal + 1
 if aVal is not 1.0 then set value of cell c to 1 + (value of cell c)
 end try
 end repeat
 end tell
 end repeat
 end tell
end run
--=====
(*
set {rowNum1, colNum1, rowNum2, colNum2} to my getCellsAddresses(dname,s_name,t_name,arange)
*)
on getCellsAddresses(d_Name, s_Name, t_Name, r_Name)
 tell application "Numbers"
 set d_Name to name of document d_Name (* useful if we passed a number *)
 tell document d_Name
 set s_Name to name of sheet s_Name (* useful if we passed a number *)
 tell sheet s_Name
 set t_Name to name of table t_Name (* useful if we passed a number *)
 end tell -- sheet
 end tell -- document
 end tell -- Numbers
 
 set two_Names to my decoupe(r_Name, ":")
 set {row_Num1, col_Num1} to my decipher(d_Name, s_Name, t_Name, item 1 of two_Names)
 if item 2 of two_Names = item 1 of two_Names then
 set {row_Num2, col_Num2} to {row_Num1, col_Num1}
 else
 set {row_Num2, col_Num2} to my decipher(d_Name, s_Name, t_Name, item 2 of two_Names)
 end if
 return {row_Num1, col_Num1, row_Num2, col_Num2}
end getCellsAddresses
--=====
(*
set { dName, sName, tName, rname, rowNum1, colNum1, rowNum2, colNum2} to my getSelParams()
*)
on getSelParams()
 local r_Name, t_Name, s_Name, d_Name
 set {d_Name, s_Name, t_Name, r_Name} to my getSelection()
 
 if r_Name is missing value then
 if my parleAnglais() then
 error "No selected cells"
 else
 error "Il n'y a pas de cellule sélectionnée !"
 end if
 end if
 
 return {d_Name, s_Name, t_Name, r_Name} & my getCellsAddresses(d_Name, s_Name, t_Name, r_Name)
end getSelParams
--=====
(*
set {rowNumber, columnNumber} to my decipher(docName,sheetName,tableName,cellRef)
apply to named row or named column !
*)
on decipher(d, s, t, n)
 tell application "Numbers" to tell document d to tell sheet s to tell table t to return {address of row of cell n, address of column of cell n}
end decipher
--=====
(*
set { d_Name, s_Name, t_Name, r_Name} to my getSelection()
*)
on getSelection()
 local _, theRange, theTable, theSheet, theDoc, errMsg, errNum
 
 tell application "Numbers" to tell document 1
 repeat with i from 1 to the count of sheets
 tell sheet i
 set x to the count of tables
 if x > 0 then
 repeat with y from 1 to x
 try
 (selection range of table y) as text
 on error errMsg number errNum
 set {_, theRange, _, theTable, _, theSheet, _, theDoc} to my decoupe(errMsg, quote)
 return {theDoc, theSheet, theTable, theRange}
 end try
 end repeat -- y
 end if -- x>0
 end tell -- sheet
 end repeat -- i
 end tell -- document
 return {missing value, missing value, missing value, missing value}
end getSelection
--=====
on parle_anglais()
 return (do shell script "defaults read 'Apple Global Domain' AppleLocale") does not start with "fr_"
end parle_anglais
--=====
on parleAnglais()
 local z
 try
 tell application "Numbers" to set z to localized string "Cancel"
 on error
 set z to "Cancel"
 end try
 return (z is not "Annuler")
end parleAnglais
--=====
on decoupe(t, d)
 local oTIDs, l
 set oTIDs to AppleScript's text item delimiters
 set AppleScript's text item delimiters to d
 set l to text items of t
 set AppleScript's text item delimiters to oTIDs
 return l
end decoupe
--=====
--[/SCRIPT]
--

Yvan KOENIG (VALLAURIS, France) mardi 8 juin 2010 21:55:15

wonderlips wrote:
I'm using iwork '08 and can't find a way to add a value to row of cells, as per the Calculate menu in Appleworks. I'm trying to update a calendar from the previous year by adding a value of 1 or 2 to the numbers in all the cells so that last year's June 24 becomes this year's June 25. Does Numbers have a way of doing this?

Yes, but it's the same way as AppleWorks does it. The Calculate Now menu item in AppleWorks only lets you delay calculations until you choose the menu item. Numbers does not have such a menu item as all calculations are applied whenever a value is changed in any cell on a table—as they are when you have Auto Calculate checked in AppleWorks.

Better than a calendar in which you have to instruct it to change the placing of the numbers is on where all you do is enter the starting date, and the calendar does the rest automatically.

Many years ago, I wrote one of these for the spreadsheet in AppleWorks 5. It ported without problems to AppleWorks 6, and that version also ported quite nicely to Numbers '09.

Numbers '08 does not support the MONTHNAME() function, so the formula showing "October" in the illustration had to be revised to a LOOKUP formula to convert the month number to the month name.

The file creates a 12 month calendar on a single table (AppleWorks heredity) starting on the first of whichever month you choose. Each month occupies five rows on the calendar. The illustration is of October, 2010, the first future month to include dates in six separate weeks, and chosen to show how the calendar handles that. Column A is used to enter the starting date, and would normally be hidden.



If you'd like a copy, email a request with "Numbers '08 calendar" or "Numbers '09 Calendar" in the subject line. My address is available in my profile. Click my name to the left of this message.

Regards,
Barry

wonderlips wrote:
Thanks for your post, Barry
Apparently, I lied. My "calendar" spreadsheet is actually a practice time form for my music students, which has additional cells for filling in times and assignments and starts at different parts of the month, depending on the quarter or semester. I didn't use Calculate Now after all, but Fill Special to change the dates. Would your '08 Calendar work in this context?

Possibly, but it's written to start at the beginning of a month—the complicated formulas are in the first and last rows of each month. Your form sounds like a much simpler problem. Given a better idea of the requirements (Dates in a Row? Column? Every day? Every other day? Skip days on a regular pattern?) It shouldn't be difficult to derive a formula that, given a starting date, would fill in the rest of the dates.

I don't suppose iWork has a Fill Special replacement.

If you mean a 'pattern fill', that's built in. Chapter 4 of the Numbers '09 User Guide, Working with Table Cells, describes how to "add values to cells based on value patterns", starting on page 76.

And can you give me a clue why some of my AW6 documents (like this one) have begun printing out much lighter gray than before, which is why I've started this whole Numbers odyssey? Thanks!

No. I think I have experienced something similar with AppleWorks in the past, but I don't recall what the cure was.

I'd suggest using step 1 in AppleWorks trouble shooting—clearing the preferences files. Click the link for instructions.

Regards,
Barry

wonderlips wrote:
I figured out how to assign a formula to each cell (ie. =A1+1 etc.) which adds a higher number to each succeeding cell. At the ends of the months my formula has to reflect a reset back to 1 from 30 or 31, and each week has to have an adjustment since I have some text cells between weeks.

You could use actual Date and Time entries, the same =A1+1 formula, and use the Cell Format Inspector to format the cells to show a short date format. (The one shown in column A, rows 2 and below, is the shortest available). If you want only the Day number, use =DAY(A) in column B to extract this information from the dates in column A. Doing this, the number will automatically reset to 1 on the first date in a new month.


Regards,
Barry

PS: I used a Date format that included the name of the day in A1 to check that I was starting on a Monday.

wonderlips wrote:
Hi Barry,
Wow! Your responses to my problem makes a lot more sense when I can see the graphics you sent with them. Not sure what I was looking at, but it didn't contain the screenshots. Below is a representation of what I'm trying to accomplish. (Sorry, but I don't know how to provide a screenshot of the actual doc in this post.)

Screenshots are taken using shift-command-4, then dragging the crosshair cursor to enclose the part of the screen you want to copy. The file is copied to your desktop as a PNG (portable network graphics) file named Picture 1 (with numbers ranging from 1 to 10), which you can then go to the finder and rename.

Upload the file to a picture storage site (there are many free ones on the 'net) and store it in an album there. For most sites, clicking on your image (or just moving the pointer over it) will give a list of codes to paste into a message to call that image.

Use the one labeled HMTL. Copy the code, then paste it into your message.

You can see an example of the code used by clicking Reply on any message here showing an image, then clicking the quote button ( ” ) to see the text and HTML codes in the message itself. (Click the discard and Cancel button below the message space if you don't actually want to reply to that message.)

Hope the formatting doesn't completely go away when you get this.

It does, pretty much. The forum software parses out multiple spaces. Still readable if the reader has an idea what it should look like, and more readable using the technique described above for seeing HTML codes in a message.

Anyway I can't get the DATE or DAY functions to track properly between cells, because the DAY function converts text into a number,

DAY() does absolutely nothing with text, except return an error message:
"DAY requires a date but found a string instead."

A cell containing a Date and Time value may be formatted to show only the date portion in any of the following forms:



No matter which of those is visible in the cell, the cell does NOT contain a text string and does NOT contain a number. The cell contains a Date and Time value in which the Date portion is the value for the ninth day of February in the 2006th year of the common era.

and the DATE function converts a date into a number, and the the two functions don't play nice with each other.

Actually DATE works in the opposite direction. It constructs a date from three separate numbers.

The syntax is DATE(year,month,day)

DATE(2006,2.9) will construct the Date and Time value for February 9, 2006 and set the time value to 0:00:00 (midnight). Depending on the format set for the cell containing the function, the date will display in one of the forms shown in the list above.

Once the DAY function converts a date into a plain number, it won't reset after 30 and 31. In fact it seems to treat any reference to that cell as a one.

DAY does return a (plain) number in the range 1-31. If you add 1 to that +number+, you'll get a number in the range 2-32. If you add 1 to the +date+ that DAY extracted that number from, you'll get a number in the range 2-31, or if the new date is in the next month, you'll get the number 1.

Now that I've seen the graphics that were supposed to come with your posts, it looks like the answer may be a set of reference cells in another table that I can hide from view in the printed form of my chart.

There's no need for a second table just to get the day numbers in your example. You may need a LOOKUP table to convert the 1s to the shortened name of the current month, though, and that LOOKUP table can be placed on a second table.

Here's my take on the problem as currently described. Description below.



Column A contains the starting date in Cell A1. The format is set to display this date including the name of the weekday to ensure the calendar starts on a Monday. (Necessary because the weekday labels in row 2 are entered text, not constructed from the date information being placed on the calendar.)

A5, and every 4th row after contains a formula that adds 7 to the date in the cell four rows above.
=$A1+7

B1 contains a formula that extracts the DAY (of the month) from the date in A1, plus a calculation that allows it to increment the date when filled into the columns to the right.
=DAY($A1+COLUMN()-2)

The desire to replace "1" in C1 with "Jun" (the shortened name of the new month) makes the formula a bit more complicated:

=IF(condition,TRUE: do this,FALSE:do this)

=IF(DAY($A1+COLUMN()-2)=1,LOOKUP(MONTH($A1+COLUMN()-2),Month Lookup :: $A$1:$A$12,Month Lookup :: $B$1:$B$12),DAY($A1+COLUMN()-2))

condition: DAY($A1+COLUMN()-2)=1

as above, the formula extracts the (adjusted) day number from the date in A1, then compares it with the number 1.

If they're equal the comparison evaluates as TRUE, and the formula does this part:

LOOKUP(MONTH($A1+COLUMN()-2),Month Lookup :: $A$1:$A$12,Month Lookup :: $B$1:$B$12)

MONTH() returns the number of the month of the adjusted date in A1, then looks up that value (6) in column A of the table Month Lookup, and returns the corresponding text string ("Jun") from column B of that table.

If the day number is not equal to 1, the comparison evaluates as FALSE, and the fomula does the last part:

DAY($A1+COLUMN()-2)

and returns the day number of the (adjusted) date.


Constructing the tables:

Month Lookup table

Enter 1 in A1 and 2 in A2.
Select both cells and drag the coltrol down to fill to row 12.
Enter Jan in B1
Select B1 and drag the control down to fill ro row 12 (Dec)

Calendar table

Enter a starting date in A1.

Enter
=IF(DAY($A1+COLUMN()-2)=1,LOOKUP(MONTH($A1+COLUMN()-2),Month Lookup :: $A$1:$A$12,Month Lookup :: $B$1:$B$12),DAY($A1+COLUMN()-2))
in B1.

Select B1 and drag the fill control right to fill B1:H1

(See optional formatting below for adding colour to month names)

Select B1:H1 and Copy

Select B5 and Paste.

Enter
=A1+7
in A5.

Select A2:H5 (as shown in graphic above)

Drag the control down as many rows as you need.

Optional formatting

The red month names on yellow backgrounds are created using Conditional formatting for the seven cells B1:H1 (and similar cells further down the table).

If you choose to do this, it's best to do so where "See optional formatting..." is placed in the instructions.

Select B1:L1
In the Format bar above the tables, set the Text colour to red and the Fill colour to yellow. (This will change the colours in all cells in the row.)
Open the Inspector and click the Cell Format button.
Click the Conditional Format checkbox to check it. Click Show Rules.
From the Choose a Rule popup, choose "Less than or Equal to" and enter 31 in the box that appears.
Choose Black for the conditional text colour and white for the conditional fill colour.

Continue with the instructions for Constructing the Tables.

Regards,
Barry

Now is it possible to do something with the reference column (B1 on down) which has the redundant info which is too lengthy to fit in the cells they're assigned too?

There are a few things you can do,

1. Set the Text colour of the cells containing the dates to White. this will hide the text, but unfortunately will leave the overflow indicator (+ sign) still showing.

2. Insert a column after column B and move the Time and Assign labels into the new column C. Then Hide column B. You will need to Unhide all columns before filling down to add extra rows. Hide is in the Column menu (triangle in the column's reference tab); Unhide is in the Table menu (in Menu bar).

Regards,
Barry

PS: when posting screen shots, I try to keep them less than about 800 pixels wide. Wider than that, they interfere with text wrapping on the smaller screens on MacBooks and iBooks.
B