INSY 2299 · Information Systems

Session 3: Cell References & Essential Functions

Lab Exercise #1 review · $ references · SUM, COUNT, IF · Ram Retail

Dominik Molitor · Fall 2026 | Session 3

INSY 2299 · Session 3Fordham University
Session 3

Agenda

Click a line to jump straight to it.

INSY 2299 · Session 3Fordham University
Review: Lab Exercise #1

Three formulas, written once for every row

One row is one transaction. Revenue = Quantity × Unit Price, Total Cost = Quantity × Unit Cost, Gross Margin ($) = Revenue − Total Cost, in columns I to K of Lab_Data.

Pick any of the 400 rows. The structured version is identical on every row.

CellColumnFormulaWith the row’s numbersResult

T-1001 check: $7,500 revenue − $6,900 cost = $600 margin. Both styles are valid: =F2*G2 and =[@quantity]*[@[unit_price]] give the same number.

INSY 2299 · Session 3Fordham University
Review: Lab Exercise #1

Summary checkpoints: SUM, AVERAGE, MAX

The summary lives on its own sheet, outside the data. Refer to the whole revenue column: a table reference always covers every row, a typed range only covers the rows you typed.

401
MetricFormulaValueFull precision

INSY 2299 · Session 3Fordham University
Review: Lab Exercise #1

The largest five transactions

MAX returns a value, not an ID. To see which transactions are largest, sort the entire table descending on revenue — never a single column.

#transaction_idproductquantity × unit_pricerevenuetotal_costgross_margin

INSY 2299 · Session 3Fordham University
Review: Lab Exercise #1

Managerial implications: revenue is not margin

The maximum is about 10.32 times the average. Revenue leader T-1001 generates $600 gross margin; margin leader T-1002 generates $3,000. T-1001 has a cost issue.

total_cost gross_margin

Which transaction should the manager investigate first? Gross margin here is a dollar amount before other operating expenses — not net income.

INSY 2299 · Session 3Fordham University
Absolute and Relative References

Cell references: relative, absolute, or both

Relative reference

=B2

Auto-adjusts the reference cell based on the new position (when copied/dragged).

Absolute reference

=$B$1

The reference cell stays fixed (when copied/dragged).

Absolute & relative

=B2*$B$1

B2 auto-adjusts while $B$1 stays fixed (when copied/dragged).

Formula in C2
CellFormula after copyingWhat moved
INSY 2299 · Session 3Fordham University
Absolute and Relative References

Practice: relative for profit, absolute for tax

The References_Updated sheet. Profit and the totals need relative references, so they can be copied across and down. Tax needs an absolute reference to the one tax rate in G2.

Type into the blue cells — try a 20% tax rate in G2.
INSY 2299 · Session 3Fordham University
Absolute and Relative References

Copy the tax across: $G$2 has to stay put

Write the tax once in B5 and copy it right to E5. The profit reference (B4) is supposed to move with it; the tax rate is not.

Copy B5 to the right using
CellFormula after copyingTaxWhat the rate reference points at

INSY 2299 · Session 3Fordham University
Essential Functions

RECAP: SUM function

The SUM function adds values. You can add cell references or ranges or a mix of both.

=SUM(number1Required: a cell reference like SUM(C6), or a cell range like SUM(C2:C8),[number2], …Optional: other cell references or ranges, e.g. SUM(C2:C8,C16:C22))
number1

From the Functions sheet: beverages sold per beverage and store. The workbook’s own total is G2 =SUM(C2:C22) → 1103.

INSY 2299 · Session 3Fordham University
Essential Functions

COUNT counts numbers, COUNTA counts anything

The COUNT function is used to count the number of cells in a range that contain numbers. COUNT(value1, [value2], …): the first range is required, more ranges are optional, e.g. COUNT(C2:C8,C16:C22).

Try:
A · BeverageB · StoreC · Sold
FormulaResult

INSY 2299 · Session 3Fordham University
Essential Functions

IF function

IF is used to make a conditional calculation, based on criteria that must be defined (= logical tests) by the analyst, and leads to either a “true” or a “false” option.

=IF(C2="Yes"Logical test,1True value, based on test,2False value, based on test)

Example 1 — returns a number

D2 =IF(C2="Yes",1,2)

Example 2 — returns text

INSY 2299 · Session 3Fordham University
Essential Functions

IF in practice: did Downtown sell more than Midtown?

SUMIF totals each store (G7 =SUMIF(B:B,H7,C:C)). A comparison on its own returns TRUE or FALSE; IF turns that into the answer you want to show.

Change the stores or the operator.
CellFormulaResult
OperatorMeaning
INSY 2299 · Session 3Fordham University
Essential Functions

Get the workbook: Cell References & Functions

Go to Blackboard → Datasets → download the Cell Reference & Functions Example file.

Datasets

  • Excel Intro
  • Cell Reference & Functions Example

Two tabs to fill in together: References_Updated (profit, totals and tax, slides 9–10) and Functions (SUM, COUNT, COUNTA, SUMIF and IF, slides 11–14).

INSY 2299 · Session 3Fordham University
Ram Retail Planning Model

The Ram Retail Company – simple planning model

Our task: translate this information into an Excel workbook/model. Click a statement to see where it lands.

CellLabelValue
INSY 2299 · Session 3Fordham University
Ram Retail Planning Model

Model building in Excel (design principles)

#1 Goal

Correctness

GET THE MATH RIGHT!

#2 Goal

Readability & understandable

Make your model …

  • READABLE
  • USABLE
  • UNDERSTANDABLE
#3 Goal

Flexibility

Make your model flexible …

NEVER PUT NUMBERS/CONSTANTS INTO FORMULAS

In practice: start with a header (model name, analyst, today’s date), put the given rates in their own labeled input cells outside the model, and reference them absolutely. Constants that are part of the math — the 0 in an IF, the 1 in a growth factor (1+$B$7) — are fine.

INSY 2299 · Session 3Fordham University
Ram Retail Planning Model

Never put numbers/constants into formulas

Sales Revenue grows 5% a year. Put the 5% in B7 and reference it; do not type it into the formula. Then change the assumption.

5%
References the cellHard-codes the 5%
CellFormulaSales RevenueFormulaSales Revenue

INSY 2299 · Session 3Fordham University
Ram Retail Planning Model

The Ram Retail planning model

Blue cells are typed in. Select a cell to read its formula.
INSY 2299 · Session 3Fordham University
Ram Retail Planning Model

Taxes, with and without IF

Taxes are only owed on a profit. The workbook uses =IF(B22>0,B22*$B$6,0); the plain version is =B22*$B$6. They agree — until earnings before taxes turn negative.

$10,000 Push it up until 2028 makes a loss.
2025202620272028

INSY 2299 · Session 3Fordham University
Application: Lab Exercise #2

Lab Exercise #2

  • Also available on Blackboard (link: ungraded lab exercises).
  • Time: ~15 minutes.
  • This exercise is for practice. There is nothing to submit, but if you do not finish during the class session, finish it at home.
  • Ask me if you encounter any problems!
Target layout: GPUs per job 4; three data centers DC1 to DC3 with Available GPUs, Research Jobs, Product Jobs, Required GPUs, Remaining GPUs and Status (DC1 Shortage, DC2 and DC3 Adequate); Assigned jobs 30 and 18.
What your finished sheet should look like: the GPU capacity planning model. Put what you practiced today to work — an absolute reference, SUM and IF.
INSY 2299 · Session 3Fordham University
Assignments

Assignments today & next session

To prepare for today and next week

  • Excel Tutorial Module #3 — tonight (day of class), 11 PM.
  • Excel Assignment #1 (submit via Blackboard) — next week (day of class), 11 PM.