Excel Spreadsheet Assignment #1CHEM 253, Fall 2021

Adapted from DOI: 10.1021/acs.jchemed.5b00122.1

Introduction: A spreadsheet is a useful tool for organizing raw data and performing basic mathematical and statistical

analysis. Learning how to use spreadsheets in the beginning of the semester and continuing to improve your skills will

save you precious time performing data analysis after each lab. Although the calculations necessary for the first few labs

can easily be done using a scientific calculator, it becomes impractical, if not impossible, to perform data analysis for the

labs later in the semester without the use of computerized spreadsheets. Learning how to compose and manipulate

spreadsheets is a useful tool for any type of data organization including social science research, public health research,

and medical research.

Some of the major advantages of using a spreadsheet are that repetitive calculations can be duplicated with just

a few keystrokes, mistakes in calculations can be fixed by changing single cells or functions, and data is organized in a clear

and understandable way.

Tutorial:

Rather than simply giving you a list of Excel commands, this assignment will walk you through some Excel basics.

Depending on your level of Excel fluency, you may already know some of the basics. Try to use this tutorial to help develop

some of the more elegant and useful commands.

Part 1: Organization of a spreadsheet

You should start by writing a title in the first line of your spreadsheet. Your name and date should also be easily seen. Your

spreadsheet should also contain documentation that tells the user how to use the sheet and what each cell does. There is

an example on Canvas for you to look at. Each box in the spreadsheet is called a cell and these cells can be referenced

using a combination of a letter and a number. The letters represent the columns of the spreadsheet and the numbers

represent the rows of the spreadsheet.

Part 2 – Spreadsheet fundamentals

Now that we have a frame of reference for discussing spreadsheets, let us discuss some of the more fundamental

aspects/uses of spreadsheets. One use of a spreadsheet is that it can be used as a basic calculator, but unlike most

calculators, it stores your results, which can later be referenced in subsequent calculations. All calculations in the

spreadsheet must begin with an equals sign, indicating to the program that you are performing a calculation. Below is a

table listing some of the basic mathematical operations and the correlated commands.

Operation

Excel Command

Addition

=A1+B1

Subtraction

=A1-B1

Multiplication

=A1*B1

Division

=A1/B1

Exponents

=A1^B1

Notice that multiplication must be done using the asterisk (*); Excel does not know how to perform multiplication using

“x” or parentheses (i.e. (A1)(B1) would not work). We will now use some of these mathematical operations to solve a

basic chemistry question using Excel.

Part 3 – Referencing Cells

When typing out the equations to fill in the blank cells, it is not necessary to retype numbers that are already present, but

rather you can click on the reference cell containing that number. Consider a formula: = (5.000/159.69) *3. Instead of

manually entering the numbers, consider the following version that references the appropriate cells in the table:

=D2*(B4/B2) When copying the value from one cell to another, you can click the standard copy and then paste. If the

initial cell does not contain an equation it will simply copy the value. However, if you copy a cell containing an equation,

the standard copy and paste sequence copy the formula, and not the value. If you want to copy and paste the value within

the cell and not the equation, first click on copy followed by paste special and then values. If you copy a formula containing

reference cells down a number of rows then it will automatically adjust the cell references to match the new cell. For

instance, let us assume that you have the formula =(G2/A2)*H2 in cell I2. If you copy this cell to I3 then the formula will

now read: =(G3/A3)*H3. If you copy the cell to J3 then the formula will read =(H3/B3)*I3. It is often the case that we want

to copy the general formula, but that some of the cell references should remain unchanged. Adding a dollar sign ($) before

the letter or number in the cell reference then it will remain unchanged when copied. If, in our previous example, cell I2

had been =(G2/$A$2)*$H2 then copying the cell to J3 would give the formula: =(H3/$A$2)*$H3.

Hopefully you now realize that it is very valuable to reference cells; especially because if you make a mistake in a

calculation you can just go back and correct the one cell with the mistake and all subsequent cells that include the

reference will adjust according to the correction.

Part 4 – Graphing

By now you’ve hopefully come to appreciate the computing and organizational abilities of Excel. In addition to its excellent

ability to keep track of data and perform calculations, Excel can also be used to make graphs. Graphs are an essential tool

in the scientist’s arsenal – they enable someone to present large amounts of data in a concise manner that immediately

draws the reader’s attention to a trend or pattern. Unfortunately, the format and types of default graphs that Excel makes

are far from on-par for science purposes. Below are a few general guidelines for making a good graph:

• Only use “xy scatter” plots (some scientific applications will also use bar graphs or pies)

• Excel can plot the points or it can connect them with lines (smoothed or straight). Rarely will points be connected with

lines – this implies data that you do not have; the only exceptions are chromatograms and spectra, where you would not

include points and only use lines.

• All axes need labels and units (check significant figures of tick mark labels)

• Excel displays vertical axis gridlines (horizontal lines on the whole graph) by default. Either remove all of the gridlines or

make a perfect grid on both axes (most graphs should not have gridlines).

• Excel includes a legend on the side of the graph by default. Single data set graphs should not have a legend. If multiple

data sets are present (2 or more) then a legend or a caption is needed. If there are only a few sets (2 to 3), use only a

caption. If there are many sets (4 or more) then use a legend and a caption.

• Unless you use a color printer, do not use color lines to distinguish between data sets on a single graph. Use, instead,

different symbols for the markers corresponding to different data sets.

• Resize the axes so that the plotted lines extend across most of the graph (the origin need not be included on the graph)

• Where appropriate, for showing trends in the data, add a trendline and display its equation on the graph (include Rsquared as well).

• The title of the figure is the first part of the figure caption, not an obnoxiously large title in the top center of the figure

(Excel default).

Part 5- References Useful Commands

Average To compute the average value of a set of numbers, use the =AVERAGE(first cell:last cell) command.

Standard deviation Calculating the standard deviation is very similar to calculating the average value of a data set. Just

type =STDEV(first cell:last cell).

Count cells If you have a long list of cells and want to determine the N value (number of data points) for future calculations,

use the =COUNT(array of cells) command. This array can be one or two-dimensional.

Relative standard deviation (RSD) There is no explicit Excel command for RSD. To calculate the RSD, you should divide the

standard deviation by the average using the two values as reference cells.

t statistic To compute the student t statistic use the =TINV(probability, DOF) command, where DOF is the number of

degrees of freedom (N-1) and probability is 1-(confidence limit). Example: for 75 data points at the 95% confidence limit

use =TINV(0.05,74).

F statistic Use the =FINV(probability, DOF1, DOF2) to compute the F statistic for two data sets, where DOF1 and DOF2 are

the degrees of freedom of the numerator data set and denominator data set, respectively, and the probability is 1(confidence limit). For example, if you have 6 degrees of freedom in the numerator and 4 degrees of freedom in the

denominator and want the F-value for a 95% confidence limit, then use =FINV(.05,6,4).

If Statements If statements are used to evaluate whether a statement is true or false. If the statement is true the command

will return one value, and if the statement is false then the command will return a different value. The command is

=IF(logical test, value if true, value if false). For example, if one wanted to determine whether the value in cell C3 is greater

than 30, and if it is greater than 30 that means the person is considered moderately obese and if its less than or equal to

30 the person is only considered overweight, the command would be: =IF(C3>30,“MODERATELY OBESE”,“OVERWEIGHT”).

The Assignment

Part 6 – Working some problems Now you will try to solve some chemistry problems on your own using Excel. The answers

to problems 1, 2, and 3, should be on one Excel workbook each on a separate sheet (might be good to name the sheets!

Make sure to have documentation! You can turn this in by uploading your Excel spreadsheet to canvas.

Problem #1: What is the volume of a single water molecule in m3? You may or may not find the following information

useful: Density of water is 0.9956 g/mL, Melting Point of water is 32°F, and the molar mass of water is 18.016 g/mol.

Proper units have to be used throughout each calculation to receive credit and the final answer must contain the correct

units and the correct significant figures.

Problem #2: NaOH(aq) is often used to titrate NaOH(aq) + HCl(aq) → NaCl(aq) + H2O(l) A 0.500M NaOH solution is used to

titrate several aliquots of 25.00 mL of an unknown concentration of HCl. Use the data in the table below and the skills in

this tutorial to determine the average concentration of the hydrochloric acid solution and the standard deviation.

Trial #

1

2

3

4

5

6

7

8

9

10

Volume NaOH

(mL)

15.55

15.77

15.54

16.01

15.89

15.99

15.62

15.66

15.71

15.84

Problem #3: A public health researcher hypothesized that there is a correlation between height and weight. (Recall: 1 kg

= 2.2 lbs and 1 cm = 0.39 in). Prepare a graph of Weight (in kg) vs Height (in cm). Does the data support the hypothesis?

(Use three significant figures in this problem)

Height

5 ft 10 in

5 ft 7 in

6 ft 5 in

7 ft 2 in

5 ft 6 in

5 ft 8 in

5 ft 3 in

Weight (Ibs)

158

163

153

251

139

201

169

References:

1.

Rubin, S. J.; Abrams, B., Teaching Fundamental Skills in Microsoft Excel to First-Year Students in Quantitative

Analysis. Journal of Chemical Education 2015, 92 (11), 1840-1845.

Don't use plagiarized sources. Get Your Custom Essay on

CHEM 253 UMW Calculation of Volume Single Water Molecule Exercises

Just from $13/Page

Why Work with Us

Top Quality and Well-Researched Papers

We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.

Professional and Experienced Academic Writers

We have a team of professional writers with experience in academic and business writing. Many are native speakers and able to perform any task for which you need help.

Free Unlimited Revisions

If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account or by contacting our support.

Prompt Delivery and 100% Money-Back-Guarantee

All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.

Original & Confidential

We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text. We also promise maximum confidentiality in all of our services.

24/7 Customer Support

Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.

Try it now!

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.

Essays

No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.

Admissions

Admission Essays & Business Writing Help

An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.

Reviews

Editing Support

Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.

Reviews

Revision Support

If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.