Css exam 1

The relational operators (>, <, =, >=, <=, <>) have a lower order of precedence than addition and subtraction in Excel formulas.

True, this means in excel addition and subtraction will be computed first before any relational operations

When you change the format of how a cell is displayed, it doesn't affect the actual value stored by the computer.

This refers to how Excel allows users to format cell contents, without altering the underlying data.

How do you view a formula or function in a cell?

double click on the cell to activate edit mode

The value that Excel displays for a cell is always the precise value you enter in the spreadsheet or the precise value resulting from a formula in that cell that is stored by the software.

False, the displayed data may be formatted differently depending on the cell setting

When writing formulas for spreadsheets in Excel, you do NOT need to pay attention to or worry about the order of precedence.

This statement is false

A cell in Excel is formatted with percentage style. You enter =10/100 in the cell. What is the displayed value and the actual value stored in the cell?

Displayed value - 10% Actual Value - 0.1

How would you enter a formula to calculate the result of $15,000 times 3 in a cell such that the formula produces no error and shows $45,000 (assume the cell is formatted to display as currency)?

=15000*3

What cells are contained in the range B8:C9?

B8, B9, C8, C9

How would you refer to a cell that is on worksheet Sheet5 in the fourth column, second row in an Excel workbook?

Sheet5!D2.

how to lock a forumla across the column

use $ in-between ex $B$1 if the letter doees’t change dont put a $

What is the most commonly used Excel function for adding values?

The SUM function.

What is the syntax of the SUM function?

=SUM

What do brackets [ ] mean in Excel function syntax?

They indicate optional arguments.

Give an example of a SUM function that adds cells A5 through A100.

=SUM(A5:A100)

Why use =SUM(A1:A100) instead of =A1+A2+...+A100?

It’s faster, saves time, and updates automatically

How does Excel update a SUM range if a row is inserted within it?

It automatically expands the range (e.g., =SUM(A1:A3) → =SUM(A1:A4))

What are three ways to insert a function into a formula?

1. Manually typing the function.

2. Using AutoSum (∑).

3. Using the Insert Function Tool.

What functions can be inserted with AutoSum (∑)?

SUM, AVERAGE, MIN, MAX, COUNT.

What is the difference between COUNT and COUNTA?

counts only numeric values vs counts all non-empty cells (including text)

What happens if a blank cell is included in the AVERAGE function?

Excel ignores blank cells

What happens if text values are included in SUM, COUNT, MIN, or MAX?

They are ignored.

What is an algorithm in Excel?

A step-by-step method coded by programmers that functions follow to calculate results.

How many levels of nesting functions does Excel allow?

Up to 64 levels.

What does the ROUND function do?

Rounds a number to a specified number of decimal places.

What is the syntax of the ROUND function?

=ROUND

In the ROUND function, what does the first argument represent?

The number to be rounded.

In the ROUND function, what does the second argument represent?

The number of decimal places to round to.

What does =ROUND(68.5, -1) return?

70 (rounds to the nearest 10).

What does =ROUND(1.49,0) return?

1.49 → 1

How would you write a formula to take the average rating in C10, round it to the nearest whole number, and multiply by 1000?

=ROUND(C10,0)*1000

absolute cell referencing

A reference that does not change when copied.

What happens when you copy plain text or numbers in Excel?

They are pasted exactly as they are (like in a word processor).

What happens when you copy a formula with cell references in Excel?

The references adjust relative to their new position.

Example: Copy =B5+B6+B7 one column to the right. What does it become?

=C5+C6+C7

What is a relative cell reference?

A reference that changes when copied

: What does $ before the column letter mean?

The column is absolute (doesn’t change).

What does $ before the row number mean?

The row is absolute (doesn’t change).

What type of reference is B5?

Relative column and relative row.

What type of reference is B$5?

Relative column, absolute row.

What type of reference is $B5?

Absolute column, relative row

What type of reference is $B$5?

Absolute column and absolute row.

When copying formulas, how do you determine the new references?

By applying the displacement in rows and columns to relative parts only.

Example: Copy =B5+B$5+$B5+$B$5 from D1 to E3. What’s the displacement?

+1 column, +2 rows.

What does B5 become after moving +1 column, +2 rows?

c7

What does B$5 become after moving +1 column, +2 rows?

C$5 (row stays locked).

####

numeric value too wide to display

#DIV/O

divide by 0 occurs

#REF!

cell reference is not valid

Max()

avoids blank cells

Why is planning a design important before starting a workbook?

It prevents time-consuming changes later by making the spreadsheet easier to read, flexible for more variables, and easy to update.

What are the three main reasons a spreadsheet may require changes?

1) Difficult to read/use, 2) Can’t easily add variables, 3) Hard to change values.

Why should formulas reference input values instead of constants?

It makes updates easier since changing one input automatically updates all related calculations.

What is "what-if analysis"?

A method where alternative input values are substituted to see how they affect the output.

When is vertical data orientation (categories in columns, cases in rows) better?

When there are few categories and many cases (e.g., 4 categories and 200 jobs).

When is horizontal data orientation (categories in rows, cases in columns) better?

When there are many categories and few cases (e.g., 200 categories and 4 jobs).

Where should common constants (like 12 inches per foot) be placed?

In a separate input area so they can be updated in one location.

Why separate inputs and outputs onto different worksheets?

It improves readability, allows easy comparisons, and organizes large sets of varying inputs.

How do you reference a cell from another worksheet?

SheetName!CellAddress

What is an advantage of placing each case (e.g., Job1, Job2) on its own worksheet?

It keeps all inputs and outputs for that case together and makes copying templates easier.

What is a disadvantage of the case-by-case worksheet method?

Adding new variables requires updating every worksheet individually.

How can you compare results across multiple case worksheets?

Use a summary sheet with formulas referencing the same cells in each case.

What is a 3-D formula in Excel?

a reference that includes multiple worksheets at once, creating a "third dimension" by referencing the same cell or range on each sheet

Example of a 3-D SUM formula across Job1 to Job6 sheets?

=SUM(Job1!B15:Job6!B15)

Example of a 3-D AVERAGE formula across Job1 to Job6 sheets?

AVERAGE(Job1!B15:Job6!B15)

When will a 3-D reference only work?

when the worksheets are adjacent in the workbook.

What is the syntax of the COUNTIF function?

=COUNTIF(range, criteria)

What does the COUNTIF function do?

It counts the number of items in a range that meet a specified criterion.

How do you count values greater than zero in range B2:B10?

=COUNTIF(B2:B10, ">0")

How do you count values greater than the value in cell B2?

=COUNTIF(B2:B10, ">"&B2)

What does COUNTIFS allow that COUNTIF does not?

It allows multiple criteria

What does SUMIF do?

It adds all values in a range that meet a specified criterion.