Topic 2 - Spreadsheets

Study Unit 4 / 5: Exam – Referencing Cells

YOU MUST KNOW THE FOLLOWING PRINCIPLES AND APPLY THEM TO THE CASE STUDY GIVEN TO YOU:

RELATIVE REFERENCES      =A1

ABSOLUTE REFERENCES    =$A$1

MIXED OPERATORS             =A1+B$1

WHAT IS CELL REFERENCING?

A FORMULA OR A FUNCTION USES CELL REFERENCES (I.E., USE THE CELL ADDRESSES) INSTEAD OF TYPING IN ACTUAL NUMBERS.

EVERY CELL REFERENCE POINTS TO ANOTHER CELL. FOR EXAMPLE, IF YOU WANT A REFERENCE THAT POINTS TO CELL A1 (THE CELL IN COLUMN A, ROW 1), THEN USE THIS CELL REFERENCE:   =A1

SPECIFICALLY, EXCEL RECORDS CELL ADDRESSES IN FORMULAS AND FUNCTIONS IN THREE  DIFFERENT WAYS:

  • RELATIVE CELL REFERENCE
  • ABSOLUTE REFERENCE
  • MIXED CELL REFERENCE

 

IF ONE OF THE CELLS DOESN’T CONTAIN NUMERIC INFORMATION, THEN YOU’LL SEE A SPECIAL ERROR CODE INSTEAD THAT STARTS WITH A # SYMBOL

RELATIVE REFERENCES

A RELATIVE CELL REFERENCE IN A FORMULA, SUCH AS A1, IS BASED ON THE RELATIVE POSITION OF THE CELL THAT CONTAINS THE FORMULA AND THE CELL THE REFERENCE REFERS TO.

IF THE POSITION OF THE CELL THAT CONTAINS THE FORMULA CHANGES, THE REFERENCE IS CHANGED. IF YOU COPY OR FILL THE FORMULA ACROSS ROWS OR DOWN COLUMNS, THE REFERENCE AUTOMATICALLY ADJUSTS. BY DEFAULT, NEW FORMULAS USE RELATIVE REFERENCES.

ABSOLUTE REFERENCES

AN ABSOLUTE CELL REFERENCE IN A FORMULA, SUCH AS $A$1, ALWAYS REFERS TO A CELL IN A SPECIFIC LOCATION. IF THE POSITION OF THE CELL THAT CONTAINS THE FORMULA CHANGES, THE ABSOLUTE REFERENCE REMAINS THE SAME. IF YOU COPY OR FILL THE FORMULA ACROSS ROWS OR DOWN COLUMNS, THE ABSOLUTE REFERENCE DOES NOT ADJUST.

RELATIVE REFERENCES   INCREMENTS THE CELLS A1, THEN A2, THEN A3 

ABSOLUTE REFERENCES   LOCKS DOWN A ROW OR A COLUMN

MIXED OPERATORS  LOCKS DOWN BOTH THE ROW AND COLUMN

  • COLUMN HAS THE $ IN FRONT
  • ROW HAS THE $ IN THE MIDDLE

 

FORMULAS – CHANGING THE OUTPUT

CHANGING THE SIGN OF AN AMOUNT MULTIPLY BY -1 OR ADD A – BEFORE THE CELL REFERENCE

  B40

  -IF

  +100*-1=-1

  -100*-1=+100

Study Unit 4 / 5: Exam – Operators (Prev Lesson)
(Next Lesson) Study Unit 4 / 5: Exam – Logical Test
Back to Topic 2 – Spreadsheets

No Comments

Comments are closed.