Formulas

Formulas for Spreadsheets

You can add formulas to your spreadsheet, then use <<tag>> to put the result in your merged document or the email you'll attach that document to.

date

To get date from time stamp (where time stamp is in column A)

=to_date(int(A2))

Then highlight that column, and choose "Format" from the spreadsheet menu to format the column as a date. I sometimes use <<date>> in my merged document or in the email it is attached to.

time

to get time from time stamp (where time stamp is in column A)

=A2-INT(A2)

Then choose "Format" from the spreadsheet menu to format the column as "time."

More date functions video by Spreadsheet Solving

names

When I copy a list of my students, the name is usually in 1 column like this:

Stoodent, Ima

For autoCrat though, I usually need the first & last names separated. Select the column with the full names. Then choose "Data" from the sheets menu and to down to "Split text to columns." Check the column labels in row one.

Sum

If I'm using a form for a rubric, usually I give points in each category; ex., 5 points for introduction, 5 points for topic sentence, etc.

The form puts my answers into a spreadsheet, and now the spreadsheet can add the points. The sum of the points will be shown in the merged doc I make with autoCrat.

=SUM(A2:D2) to total the numbers in columns A through D of row 2, or

=A2+C2+E2+G2 if my scores are not in continuous columns.

copyDown

Then I use copyDown to copy my formulas into each row as I submit the form. More info about copyDown.

Paste special >Paste values only

If you need to get rid of the formula and just show the product of the formula, select the column of formulas, copy, then delete them.

Now in the sheets menu under Edit, choose Paste special > Paste values only.

Other formulas that can be helpful:

  • to get today's date

=TODAY()

  • to total scores, specify a range-- a colon means "through"--or specify the noncontinuous cells

=SUM(A2:R2)

=SUM(E2+G2+I2+K2+M2+O2+Q2+S2+U2+Y2)

  • to count cells that are not blank -- inside the parenthesis is the range to be counted

=COUNTA(A2:A200)

  • to count cells that contain certain information--inside the parenthesis is the range then the information to be counted, separated by a comma

=COUNTIF(A2:A12, ">20") =COUNTIF(A2:A12, "YES")

Concatenate has its own page on my site.