Class 12 Commerce . Accountancy . Computerised Accounting System . Spreadsheet

Chapter 2: Spreadsheet

Ch 2

Accountancy

Class 12 Commerce

Lecture 1

Introduction

Spreadsheet: Structure and Purpose

Definition

Formula — A formula is an arithmetic expression and/or function that produces a derived value from other values.

Worked example

Classify a value calculated from other spreadsheet values.

  1. A value entered directly is an independent value.
  2. A value obtained from other values using an arithmetic expression and/or function—a formula—is a derived value.
Answer: It is a derived value.

Spreadsheet Applications

  • Purpose: Spreadsheet applications let users enter, organise, calculate and compare numerical or financial data.
  • Worksheets: Data is arranged in rows and columns, and formulas or functions can calculate results from entered values.

Excel 2007 Interface

Excel interface showing the Office Button and Ribbon.
Excel interface showing the Office Button and Ribbon.
Practice

State the direction of rows and columns in a spreadsheet.

Hint: Distinguish horizontal from vertical.
Key step: Rows are horizontal; columns are vertical.
Solution: Rows run horizontally, and columns run vertically.
Practice

Name two actions available from the Office Button in Excel 2007.

Hint: Think of commands for managing a workbook.
Key step: The button includes workbook commands.
Solution: Any two: create a workbook, open a workbook, save a workbook or print.

Quick recap

  • Spreadsheet: A grid of rows and columns for numerical or financial data.
  • Rows and columns: Rows are horizontal; columns are vertical.
  • Values: Independent values are entered directly; derived values are calculated using a formula.
  • Formula: An arithmetic expression and/or function that produces a derived value.
  • Spreadsheet application: A program for entering and processing data; MS Excel is an example.
  • Excel 2007: The Ribbon provides tabs for accessing toolbars, and the Office Button provides workbook commands.
Lecture 2

Basic Concepts Of Spreadsheet Part I

Workbooks and Worksheets

Spreadsheet window showing the active cell, Name Box, row and column labels, sheet tabs and Insert Worksheet tab.
Spreadsheet window showing the active cell, Name Box, row and column labels, sheet tabs and Insert Worksheet tab.
Spreadsheet interface showing the Name Box, Formula Bar and Define Name command.
Spreadsheet interface showing the Name Box, Formula Bar and Define Name command.

Basic and Derived Values

Value from Quantity and Price
V=Q×PV=Q\times P

Quantity and price are basic values; the resulting item value is derived.

Worked example

Find the value of 6 items priced at Rs. 25 each.

  1. An item has quantity 6 and price Rs. 25 per item. Substitute the basic values into the formula.
    V=Q×PV=Q\times P
  2. Multiply quantity by price.
    V=6×25=150V=6\times25=150
Answer: The derived value is Rs. 150.

Rows, Columns and Worksheet Size

  • Rows: Horizontal lines are numbered from top to bottom.
  • Columns: Vertical lines are identified by letters from left to right.
  • Excel 2007: Each worksheet has 65,536 rows and 256 columns, providing about 16.5 million cells.

Cell Address and Cell Reference

Types of Cell Reference
Reference typeExampleWhat happens when copied?
Relative`C4`The row and column references adjust to the formula's new location; this is the default.
Absolute`CC4`Both column and row stay fixed.
Mixed`C4‘or‘CC4` or `C4`One part stays fixed: `C4‘fixesthecolumn;‘CC4` fixes the column; `C4` fixes the row.

Moving Around a Worksheet

Arrow keys move the selected cell up, down, left or right.
Arrow keys move the selected cell up, down, left or right.
Single-Cell Movement
MovementKey
One cell downDown Arrow or Enter
One cell upUp Arrow
One cell leftLeft Arrow
One cell rightRight Arrow or Tab
Faster Navigation
MovementKey
Go to the top of the worksheet (`A1`)CTRL + HOME
Go to the last row and column containing dataCTRL + END
Move through filled-cell boundaries in a rowSuccessive CTRL + Right Arrow, or END + Right Arrow
Move through filled-cell boundaries in a columnSuccessive CTRL + Down Arrow, or END + Down Arrow
Go to the beginning of the current rowHOME

Data Entered in a Cell

Practice

Classify the inputs `125`, `North Zone` and `28/10/2026` as numeric, alphanumeric or date data.

Hint: Identify whether each entry is a number, a text-and-character entry, or a date.
Key step: Match each value's form to the three data types.
Solution: `125` is numeric; `North Zone` is alphanumeric; `28/10/2026` is a date.
Practice

A formula in `D5` refers to `C4`. State how the reference behaves if copied one cell down and one cell right. How would you write it to keep the reference fixed?

Hint: A normal reference adjusts with the formula; dollar signs fix both parts.
Key step: Move both the column and row one place, then compare with an absolute reference.
Solution: The relative reference changes from `C4` to `D5`. To keep the original cell fixed, use `CC4`.

Quick recap

  • A workbook contains worksheets; only one worksheet is active at a time.
  • Rows are numbered and columns are lettered; their intersection is a cell.
  • Cell addresses combine a column letter and row number; a range uses a colon between its endpoints.
  • Relative references adjust when copied, absolute references stay fixed, and mixed references fix one part.
  • Arrow keys move one cell; CTRL + HOME and CTRL + END support faster navigation.
  • Cell entries may be numeric, alphanumeric or dates; basic values can be used to calculate derived values.
Lecture 3

Basic Concepts Of Spreadsheet Part II

Cell Values and Planning

  • Cell contents: A cell may contain a number, text, date, formula or function.
  • Data types: Excel recognises entries such as 306 as numeric, Asset as alphanumeric and 12/07/08 as a date.
  • Planning: Decide which cells will hold input values and which cells will contain relationships or calculations.
  • Basic and derived values: Enter basic values directly; formulas use them to calculate derived values, which update when inputs change.
  • What-if analysis: Change one or more input values to examine their effect on calculated results.
Lecture 4

Basic Concepts Of Spreadsheet Part III

Spreadsheet Functions

General function form
=FunctionName⁡(argument)=\operatorname{FunctionName}(\text{argument})

Enter the function name and its argument(s) after the equal sign.

The Formulas tab groups functions in the Function Library by category.
The Formulas tab groups functions in the Function Library by category.

Function Tools and Categories

  • AutoSum (∑): Inserts a SUM formula quickly; its menu also offers AVERAGE, MIN, MAX and COUNT.
  • Excel 2007: Has 12 function categories. Categories especially useful in computerised accountancy include Financial, Date and Time, Lookup and References, Database, Text and Logical.

Using AutoSum

  1. Select the cell where the total should appear.
  2. Click AutoSum and check or enter the range to add.
    =SUM⁡(D1:G1)=\operatorname{SUM}(D1:G1)
  3. Confirm the formula; the total appears in the selected cell.
    SUM⁡(D1:G1)=17\operatorname{SUM}(D1:G1)=17
SUM adds the values in cells D1:G1 to produce 17.
SUM adds the values in cells D1:G1 to produce 17.
Worked example

Find the total of the values in D1:G1, whose sum is 17.

  1. To total cells D1:G1 in H1, enter the SUM formula.
    =SUM⁡(D1:G1)=\operatorname{SUM}(D1:G1)
  2. The values in the range add to 17, which is displayed in H1.
    SUM⁡(D1:G1)=17\operatorname{SUM}(D1:G1)=17
Answer: The result is 17.

Naming Cells and Ranges

SUM using a named range
=SUM⁡(Numbers)=\operatorname{SUM}(\text{Numbers})

Use the defined name in place of the range address.

Define and use a named range

  1. Select the cell or range to name.
    B1:F1B1:F1
  2. On the Formulas tab, choose Define Name.
  3. Enter the name and check the range shown in Refers to; click OK.
    Numbers→\B\1:\F\1\text{Numbers}\rightarrow\B\1:\F\1
  4. Use the name in a formula. Apply Names can insert an existing name in place of cell references.
    =SUM⁡(Numbers)=\operatorname{SUM}(\text{Numbers})
The Define Name dialog assigns Numbers to the selected range B1:F1.
The Define Name dialog assigns Numbers to the selected range B1:F1.
Worked example

Use the named ranges Monthly_Totals and Monthly_Tax to calculate their totals.

  1. Monthly_Totals refers to B2:B5; use it to calculate the total in B6.
    =SUM⁡(Monthly_Totals)=\operatorname{SUM}(\text{Monthly\_Totals})
  2. Monthly_Tax refers to C2:C5; use it to calculate the total in C6.
    =SUM⁡(Monthly_Tax)=\operatorname{SUM}(\text{Monthly\_Tax})
  3. The formulas return the stated totals.
    SUM⁡(Monthly_Totals)=1158,SUM⁡(Monthly_Tax)=238\operatorname{SUM}(\text{Monthly\_Totals})=1158,\quad \operatorname{SUM}(\text{Monthly\_Tax})=238
Answer: B6: =SUM(Monthly_Totals) returns 1158; C6: =SUM(Monthly_Tax) returns 238.

Conditional Summation with SUMIF

SUMIF with a condition
=SUMIF⁡(Numbers,"<6")=\operatorname{SUMIF}(\text{Numbers},\text{"<6"})

This form totals values in the named range that meet the criterion.

Worked example

What is the result of summing the values in Numbers that are less than 6?

  1. Numbers is the named range to test; the criterion is less than 6.
    =SUMIF⁡(Numbers,"<6")=\operatorname{SUMIF}(\text{Numbers},\text{"<6"})
  2. Add only the values in Numbers that meet the criterion.
    SUMIF⁡(Numbers,"<6")=9\operatorname{SUMIF}(\text{Numbers},\text{"<6"})=9
Answer: The result is 9.
Practice

What does =COUNT(A1:A8) count?

Hint: COUNT counts numeric entries.
Key step: Exclude blank and text cells.
Solution: It counts the cells in A1:A8 that contain numbers.
Practice

Cells B2:B5 are named Monthly_Totals. Write a formula to calculate their average.

Hint: Use AVERAGE with the range name.
Key step: Use the defined name instead of the cell addresses.
Solution: =AVERAGE(Monthly_Totals).
⚠ Common mistake

Do not treat text cells as numeric inputs for SUM, AVERAGE or COUNT; these functions use numbers and ignore blank or text cells in the described ranges. Write comparison criteria in quotation marks, for example =SUMIF(Numbers,"<6").

Quick recap

  • Functions are built-in formulas that begin with an equal sign and process arguments to return results.
  • SUM adds numbers; AVERAGE finds their mean; COUNT counts numeric cells; MIN and MAX return the smallest and largest values.
  • AutoSum inserts a SUM formula quickly and provides other common function choices.
  • Excel 2007 provides 12 function categories, including several useful in computerised accountancy.
  • Named ranges replace cell references with descriptive names in formulas.
  • SUMIF adds only values that satisfy a condition; the example totals values less than 6 to get 9.
Lecture 5

Basic Concepts Of Spreadsheet Part IV

Named Ranges, Balances, and Freezing Values

Monthly totals, tax, and the resulting balance in a spreadsheet.
Monthly totals, tax, and the resulting balance in a spreadsheet.

Freeze formulas using Paste Special

  1. Select the formula cells, such as B6:C6B6:C6 and B7B7.
  2. Copy the selected cells.
  3. Choose Paste Special and select Values.
  4. Confirm. The formulas are replaced by their current calculated values.
The Copy command on the spreadsheet toolbar.
The Copy command on the spreadsheet toolbar.
Paste Special dialog with the Values option.
Paste Special dialog with the Values option.

IF Function

IF syntax
IF(logical_test,value_if_true,value_if_false)\mathrm{IF}(logical\_test,value\_if\_true,value\_if\_false)

Use this structure to return one result for a TRUE test and another for a FALSE test.

IF examples

  • Text result: To show Yes when A1<20A1<20 and No otherwise, use =IF(A1<20,"Yes","No")=\mathrm{IF}(A1<20,\text{"Yes"},\text{"No"}); text results must be enclosed in quotation marks.
  • Different calculations: =IF(C2>B2,(C2+D2)/2,(B2+D2)/2)=\mathrm{IF}(C2>B2,(C2+D2)/2,(B2+D2)/2) returns the first average if C2>B2C2>B2; otherwise it returns the second.
  • Combined functions: =IF(AVERAGE(A2:A6)>10,SUM(B2:B6),0)=\mathrm{IF}(\mathrm{AVERAGE}(A2:A6)>10,\mathrm{SUM}(B2:B6),0) sums B2:B6B2:B6 when the average of A2:A6A2:A6 exceeds 1010; otherwise it returns 00.

Using IF for a saving percentage

  • Condition: If the total in E4E4 exceeds 1000010000, return a saving rate of 10%10\%; otherwise, return 5%5\%.
  • Formula: In F4F4, enter =IF(E4>10000,10%,5%)=\mathrm{IF}(E4>10000,10\%,5\%) and copy it down for the other rows.
  • Amount: Multiply the total by the selected rate; for example, 8889×5%=444.458889\times5\%=444.45.
Worked example

If E4=8889E4=8889, what saving rate and amount does the formula return?

  1. For a total of 88898889 in E4E4, test whether it exceeds 1000010000.
    8889>10000    is false8889>10000\;\;\text{is false}
  2. The IF formula returns a rate of 5%5\%.
    8889×5%=444.458889\times 5\%=444.45
Answer: The test is FALSE, so the rate is 5%5\% and the amount is 444.45444.45.
IF function dialog with the logical test and the TRUE and FALSE results.
IF function dialog with the logical test and the TRUE and FALSE results.

Nested IF Functions

Nested IF example: test grades
IF(E2<96,IF(E2<91,IF(E2<55,"Fail","C Grade"),"B Grade"),"A Grade")\mathrm{IF}(E2<96,\mathrm{IF}(E2<91,\mathrm{IF}(E2<55,\text{"Fail"},\text{"C Grade"}),\text{"B Grade"}),\text{"A Grade"})

For marks in E2E2, the formula checks thresholds in order; text results are enclosed in quotation marks.

Grade ranges from the nested IF
MarksResult
Below 5555Fail
5555 to below 9191C Grade
9191 to below 9696B Grade
9696 or moreA Grade
Worked example

What grade does the nested IF formula return when E2=93E2=93?

  1. For E2=93E2=93, check the first condition.
    93<96    is true93<96\;\;\text{is true}
  2. Check the next condition; it is false, so the formula returns the B Grade result.
    93<91    is false93<91\;\;\text{is false}
Answer: It returns B Grade.
Practice

Write an IF formula that returns Yes if A1<20A1<20 and No otherwise.

Hint: Put the TRUE result before the FALSE result, and quote text results.
Key step: Use A1<20A1<20 as the logical test.
Solution: =IF(A1<20,"Yes","No")=\mathrm{IF}(A1<20,\text{"Yes"},\text{"No"})
Practice

If E2=50E2=50, what result does the nested grade formula return?

Hint: Test the conditions from the outside inward.
Key step: 50<9650<96 and 50<9150<91 are TRUE; 50<5550<55 is also TRUE.
Solution: It returns Fail.

Quick recap

  • Named ranges make formulas easier to read; subtract named tax from the named monthly total to calculate a balance.
  • Paste Special → Values replaces formulas with their current results, stopping later recalculation.
  • IF returns one result for TRUE and another for FALSE; text results need quotation marks.
  • Logical operators compare values and produce TRUE or FALSE.
  • IF examples can return text, different calculations, or results from functions such as AVERAGE and SUM.
  • Nested IFs test conditions in sequence; the final FALSE result handles cases not matched earlier.
Lecture 6

Basic Concepts Of Spreadsheet Part V

Other Useful Functions

Function libraries

  • Purpose: Spreadsheet functions use input data as arguments and return calculated information, such as ages, payment delays, interest, depreciation, or future values.
  • Categories: Date and Time; Mathematical; Text Manipulation; Logical (other than IF); Lookup and Reference; Financial.
  • Help: Use Excel Help for function details. Typing a function name after `=` and selecting it from the suggestion strip is a quick way to access help.

Date and Time Functions

Definition

Excel date serial number — Excel stores dates as serial numbers for calculations; times are stored as fractions of a day. January 1, 1900 is serial number 1, and January 1, 2009 is serial number 39814.

Date and time function outputs

  • TODAY(): Returns the serial number of the current date.
  • NOW(): Returns the current date and time.
  • DAY(serial_number): Returns the day of the month as an integer from 1 to 31.
  • MONTH(serial_number): Returns the month as an integer from 1 to 12; April returns 4.
  • YEAR(serial_number): Returns the year as an integer from 1900 to 9999.
  • DATEVALUE(date_text): Converts a text-form date into its Excel date serial number; for example, `=DATEVALUE("16-04-2009")` returns 39919.
Extracting date parts and estimating age
Age=Dtoday−Dbirth365.25\text{Age} = \frac{D_{\text{today}}-D_{\text{birth}}}{365.25}

Use `=DAY(A1)`, `=MONTH(A1)` or `=YEAR(A1)` to extract a date part. Approximate age in years by dividing the date difference by 365.25, allowing for leap years.

Estimate age from two dates

  1. Subtract the date-of-birth serial number from the later date's serial number.
    Dlater−DbirthD_{\text{later}}-D_{\text{birth}}
  2. Divide the elapsed days by 365.25 to estimate age in years.
    Dlater−Dbirth365.25\frac{D_{\text{later}}-D_{\text{birth}}}{365.25}
Worked example

Find the approximate age on 16-Apr-2009 of a person born on 16-Apr-1980.

  1. Estimate the age on 16-Apr-2009 of a person born on 16-Apr-1980; their serial numbers are 39919 and 29327.
    39919−29327=1059239919-29327=10592
  2. Convert elapsed days to years.
    10592365.25≈28.9993\frac{10592}{365.25}\approx 28.9993
  3. Interpret the approximate result.
    ≈29\approx 29
Answer: Approximately 29 years.

Mathematical Functions

Definition

SUMIF — Adds values from cells whose corresponding entries meet a specified condition.

SUMIF syntax
SUMIF⁡(r,c,s)\operatorname{SUMIF}(r,c,s)

The range is tested against the criterion; the optional sum_range contains the values to add. If sum_range is omitted, Excel sums the tested range itself.

SUMIF example: assets and depreciation
Asset valueDepreciation
Rs. 100,000Rs. 7,000
Rs. 200,000Rs. 14,000
Rs. 300,000Rs. 21,000
Rs. 400,000Rs. 28,000
TotalRs. 70,000

Apply SUMIF

  1. Set range to the cells being tested and criteria to the condition.
  2. Set sum_range to the corresponding values to add.
Worked example

Using the table, find total depreciation for assets valued above Rs. 150,000.

  1. Select depreciation for assets valued above Rs. 150,000.
    SUMIF⁡(D2:D5,">150000",E2:E5)\operatorname{SUMIF}(D2:D5,\text{">150000"},E2:E5)
  2. The qualifying assets are Rs. 200,000, Rs. 300,000 and Rs. 400,000; add their depreciation.
    14000+21000+28000=6300014000+21000+28000=63000
Answer: Rs. 63,000.
⚠ Common mistake

For the asset example, test `D2:D5` and sum the matching cells in `E2:E5`; do not use the two-column range `D2:E5` as the criteria range.

Definition

ROUND — Rounds a number to a specified number of digits.

Rounding digit rule

A positive num_digits rounds to decimal places; zero rounds to the nearest integer; a negative value rounds to the left of the decimal point.

ROUND, ROUNDUP and ROUNDDOWN
ROUND⁡(n,d);ROUNDUP⁡(n,d);ROUNDDOWN⁡(n,d)\operatorname{ROUND}(n,d);\quad \operatorname{ROUNDUP}(n,d);\quad \operatorname{ROUNDDOWN}(n,d)

ROUNDUP rounds away from zero; ROUNDDOWN rounds toward zero. All three functions use num_digits to set the rounding place.

Rounding examples
FormulaResult
`=ROUND(2.15,1)`2.2
`=ROUND(2.149,1)`2.1
`=ROUND(-1.475,2)`-1.48
`=ROUND(21.5,-1)`20
`=ROUNDUP(3.2,0)`4
`=ROUNDUP(76.9,0)`77
`=ROUNDUP(3.14159,3)`3.142
`=ROUNDUP(-3.14159,1)`-3.2
`=ROUNDUP(31415.92654,-2)`31,500
`=ROUNDDOWN(3.2,0)`3
`=ROUNDDOWN(76.9,0)`76
`=ROUNDDOWN(3.14159,3)`3.141
`=ROUNDDOWN(-3.14159,1)`-3.1
`=ROUNDDOWN(31415.92654,-2)`31,400

Round a number

  1. Choose the required rounding place using num_digits.
  2. Use ROUND for ordinary rounding, ROUNDUP to move away from zero, or ROUNDDOWN to move toward zero.
Worked example

Compare ROUND, ROUNDUP and ROUNDDOWN for 31415.92654 with num_digits -2.

  1. Round 31415.92654 to the nearest hundred with ROUND.
    ROUND⁡(31415.92654,−2)=31400\operatorname{ROUND}(31415.92654,-2)=31400
  2. ROUNDUP moves away from zero to the next hundred.
    ROUNDUP⁡(31415.92654,−2)=31500\operatorname{ROUNDUP}(31415.92654,-2)=31500
  3. ROUNDDOWN moves toward zero to the previous hundred.
    ROUNDDOWN⁡(31415.92654,−2)=31400\operatorname{ROUNDDOWN}(31415.92654,-2)=31400
Answer: ROUND: 31,400; ROUNDUP: 31,500; ROUNDDOWN: 31,400.
Practice

Cell A1 contains the date 16-Apr-2009. What do `=DAY(A1)`, `=MONTH(A1)` and `=YEAR(A1)` return?

Hint: The functions extract the day, month number and year separately.
Key step: For 16-Apr-2009, the date parts are day 16, month 4 and year 2009.
Solution: `=DAY(A1)` returns 16; `=MONTH(A1)` returns 4; `=YEAR(A1)` returns 2009.
Practice

What is `=ROUNDUP(-3.14159,1)`?

Hint: ROUNDUP rounds away from zero.
Key step: Move one decimal place away from zero.
Solution: -3.14159 rounds to -3.2.
Practice

Using the asset table, what does `=SUMIF(D2:D5,">250000",E2:E5)` return?

Hint: Include only assets strictly greater than Rs. 250,000.
Key step: The qualifying assets are Rs. 300,000 and Rs. 400,000.
Solution: Rs. 21,000 + Rs. 28,000 = Rs. 49,000.

Quick recap

  • Excel functions take arguments and return calculated results; useful groups include Date and Time, Mathematical, Text Manipulation, Logical, Lookup and Reference, and Financial.
  • Dates are serial numbers and times are fractions of a day; January 1, 1900 is serial 1 and January 1, 2009 is serial 39814.
  • TODAY returns the current date, while NOW includes the time; DAY, MONTH and YEAR extract date components, and DATEVALUE converts a text date to a serial number.
  • Approximate age in years is the difference between date serials divided by 365.25.
  • SUMIF tests a range against a criterion and adds corresponding cells in the optional sum_range.
  • ROUND uses num_digits to choose the rounding place; ROUNDUP moves away from zero and ROUNDDOWN moves toward zero.
Lecture 7

Basic Concepts Of Spreadsheet Part VI

Counting Functions

Worked example

Count_Data contains 18 total cells, 13 occupied cells, and 7 numeric entries. Find the number of blanks and the COUNT result.

  1. Find blanks by subtracting occupied cells from the total.
    18−13=518-13=5
  2. Use the sample's reported numeric-entry count for COUNT.
    COUNT(Count_Data)=7\mathrm{COUNT}(Count\_Data)=7
Answer: There are 18−13=518-13=5 blank cells; COUNT returns 77.

Array Dimensions and Array Formulas

Worked example

Find the number of cells in A1:C4.

  1. Find the number of rows and columns in A1:C4.
    ROWS(A1:C4)=4,COLUMNS(A1:C4)=3\mathrm{ROWS}(A1:C4)=4,\quad \mathrm{COLUMNS}(A1:C4)=3
  2. Multiply the dimensions to find the cell count.
    4×3=124\times3=12
Answer: A1:C4 has 4 rows and 3 columns, so it contains 1212 cells.

COUNTIF

COUNTIF examples show formulas counting entries that meet specified criteria.
COUNTIF examples show formulas counting entries that meet specified criteria.
Worked example

A2:A6 contains 10, 32, 40, 32, and 25. Count the values greater than 30.

  1. Apply COUNTIF to A2:A6 with the condition greater than 30.
    COUNTIF(A2:A6,">30")\mathrm{COUNTIF}(A2:A6,">30")
  2. Three entries satisfy the condition: 32, 40, and 32.
    32>30,40>30,32>3032>30,\quad 40>30,\quad 32>30
  3. Therefore, the function returns 3.
    33
Answer: COUNTIF(A2:A6,">30")=3\mathrm{COUNTIF}(A2:A6,">30")=3.

Text Manipulation: TEXT

Worked example

L1 contains 23.5. Use TEXT to display the value as Rs. 23.50.

  1. Format the value in L1 with two decimal places and the rupee label.
    TEXT(L1,"Rs.0.00")\mathrm{TEXT}(L1,"Rs. 0.00")
  2. The function returns formatted text.
    23.5⟶Rs. 23.5023.5\longrightarrow\text{Rs. 23.50}
Answer: TEXT(L1,"Rs.0.00")\mathrm{TEXT}(L1,"Rs. 0.00") returns Rs. 23.50 as text.
TEXT formulas can join formatted sales values with descriptive text.
TEXT formulas can join formatted sales values with descriptive text.
⚠ Common mistake

Do not confuse COUNT and COUNTA: COUNT counts numeric entries; COUNTA counts every nonblank cell, including text, logical values, and errors.

⚠ Common mistake

Do not treat TEXT as cell formatting: TEXT returns text, while number-formatting commands change the appearance of numeric cells; those commands work only when the entire cell is numeric.

Practice

A1:A5 contains apples, pears, apples, a blank cell, and oranges. How many cells contain apples?

Hint: Use the text criterion "apples".
Key step: Count only cells matching the text criterion.
Solution: COUNTIF(A1:A5,"apples")=2\mathrm{COUNTIF}(A1:A5,"apples")=2.
Practice

A range has 5 rows and 2 columns. How many cells does it contain?

Hint: Multiply the row count by the column count.
Key step: Total cells = rows × columns.
Solution: 5×2=105\times2=10 cells.
Practice

Cell B2 contains 8.4. Use TEXT to display it with two decimal places.

Hint: Use the format string "0.00".
Key step: TEXT converts the value to formatted text.
Solution: TEXT(B2,"0.00")\mathrm{TEXT}(B2,"0.00") returns 8.40 as text.

Quick recap

  • COUNT counts numeric entries, including dates; directly supplied values may include numeric text or logical values, while text and logical values in references or arrays are ignored.
  • COUNTA counts nonblank cells; COUNTBLANK counts blanks; COUNTIF counts cells meeting a criterion.
  • ROWS and COLUMNS return array dimensions; multiply them to find the total number of cells.
  • Array formulas perform multiple calculations and return one or more results; traditional entry uses Ctrl+Shift+Enter and braces.
  • TEXT converts a number to formatted text, such as 23.5 becoming Rs. 23.50.
  • Home-tab number formatting changes the appearance of a numeric cell; it works only if the entire cell is numeric.
Lecture 8

Basic Concepts Of Spreadsheet Part VII

CONCATENATE Function

Definition

CONCATENATE — Joins two or more text items into one text string.

Key points

  • Arguments: Takes 2 to 255 items, such as text strings, numbers, or single-cell references.
  • Output: Combines the items in the order supplied.
  • Spaces: Add a space as a separate text item when needed; CONCATENATE does not add spaces automatically.
Syntax
=CONCATENATE(text1,text2,…)\texttt{=CONCATENATE(text1,text2,\ldots)}

Use text strings, numbers, or single-cell references as the items to join.

Combine name fields

  1. Select the cells containing the name parts.
    A2, B2, C2\texttt{A2, B2, C2}
  2. Place a space string between the cell references.
    " "\texttt{" "}
  3. Join the parts with CONCATENATE.
    =CONCATENATE(A2," ",B2," ",C2)\texttt{=CONCATENATE(A2," ",B2," ",C2)}
Worked example

How can the first, middle, and last names in A2, B2, and C2 be combined?

  1. Suppose A2, B2, and C2 contain the first, middle, and last names.
    A2=Riya, B2=Anil, C2=Shah\texttt{A2=Riya, B2=Anil, C2=Shah}
  2. Join the three cells with spaces between them.
    =CONCATENATE(A2," ",B2," ",C2)\texttt{=CONCATENATE(A2," ",B2," ",C2)}
  3. The function returns the full name.
    Riya Anil Shah\texttt{Riya Anil Shah}
Answer: Use `=CONCATENATE(A2," ",B2," ",C2)` to return Riya Anil Shah.
Spreadsheet example combining first, middle, and last names into a full name.
Spreadsheet example combining first, middle, and last names into a full name.

Logical Functions

Definition

Logical value — A result of TRUE or FALSE produced by comparing values or arithmetic expressions.

AND Function

Definition

AND — Returns TRUE only when every argument evaluates to TRUE; otherwise, it returns FALSE.

Key points

  • Arguments: Takes up to 255 logical tests, including comparisons or expressions.
  • TRUE result: Every argument must be TRUE.
  • FALSE result: One or more arguments are FALSE.
  • With IF: Using AND as IF's logical test lets IF check several conditions together.
Syntax
=AND(logical1,logical2,…,logical255)\texttt{=AND(logical1,logical2,\ldots,logical255)}

Each logical argument is a condition to test.

Test whether a value is in a range

  1. Write one comparison for the lower limit and one for the upper limit.
    A2>1, A2<100\texttt{A2>1, A2<100}
  2. Combine both tests with AND.
    =AND(A2>1,A2<100)\texttt{=AND(A2>1,A2<100)}
  3. To return a value or message, use AND inside IF.
    =IF(AND(A2>1,A2<100),A2,"The value is out of range.")\texttt{=IF(AND(A2>1,A2<100),A2,"The value is out of range.")}
Worked example

If A2 contains 50, what does `=IF(AND(A2>1,A2<100),A2,"The value is out of range.")` return?

  1. A2 contains 50; test whether it is greater than 1 and less than 100.
    =AND(A2>1,A2<100)\texttt{=AND(A2>1,A2<100)}
  2. Both comparisons are TRUE, so AND returns TRUE.
    50>1: TRUE; 50<100: TRUE\texttt{50>1: TRUE; 50<100: TRUE}
  3. With IF, the value in A2 is returned because the combined test is TRUE.
    =IF(AND(A2>1,A2<100),A2,"The value is out of range.")=50\texttt{=IF(AND(A2>1,A2<100),A2,"The value is out of range.")=50}
Answer: It returns 50 because both conditions are TRUE.
AND result examples
FormulaResultReason
`=AND(TRUE,TRUE)`TRUEAll arguments are TRUE.
`=AND(TRUE,FALSE)`FALSEAt least one argument is FALSE.
`=AND(2+2=4,2+3=5)`TRUEBoth expressions are TRUE.

OR Function

Definition

OR — Returns TRUE when at least one argument is TRUE; it returns FALSE only when every argument is FALSE.

Key points

  • Arguments: Takes up to 255 logical tests.
  • TRUE result: One or more arguments are TRUE.
  • FALSE result: All arguments are FALSE.
Syntax
=OR(logical1,logical2,…,logical255)\texttt{=OR(logical1,logical2,\ldots,logical255)}

Each logical argument is a condition to test.

Combine alternative conditions

  1. Enter each acceptable condition as a logical argument.
    condition1, condition2\texttt{condition1, condition2}
  2. Use OR to test whether at least one condition is satisfied.
    =OR(condition1,condition2)\texttt{=OR(condition1,condition2)}
Worked example

What does `=OR((1+1)=1,(2+2)=5)` return?

  1. Evaluate the two comparisons.
    1+1=1: FALSE; 2+2=5: FALSE\texttt{1+1=1: FALSE; 2+2=5: FALSE}
  2. Since both arguments are FALSE, OR returns FALSE.
    =OR((1+1)=1,(2+2)=5)=FALSE\texttt{=OR((1+1)=1,(2+2)=5)=FALSE}
Answer: FALSE; both arguments evaluate to FALSE.
OR result examples
FormulaResultReason
`=OR(TRUE,FALSE)`TRUEAt least one argument is TRUE.
`=OR((1+1)=1,(2+2)=5)`FALSEAll arguments are FALSE.
`=OR(TRUE,FALSE,TRUE)`TRUEAt least one argument is TRUE.

LOOKUP Function

Definition

LOOKUP — Searches a range or array for a value and returns a corresponding value from another position.

Two forms

  • Vector form: Searches a single-row or single-column lookup range and returns the value at the same position in a second single-row or single-column range.
  • Array form: Searches the first row or column of an array and returns a value from the corresponding position in its last row or column.
  • Use: LOOKUP can be an alternative to elaborate IF tests or tests that exceed IF nesting limits.

LOOKUP — Vector Form

Arguments and rules

  • lookup_value: The number, text, logical value, name, or reference being searched for.
  • lookup_vector: A one-row or one-column range; its values must be in ascending order.
  • result_vector: A one-row or one-column range with the same size as lookup_vector.
  • Approximate match: If no exact match is found, LOOKUP selects the largest lookup value less than or equal to the search value.
  • Below the range: If lookup_value is smaller than the smallest lookup value, LOOKUP returns #N/A.
Vector syntax
=LOOKUP(lookup_value,lookup_vector,result_vector)\texttt{=LOOKUP(lookup\_value,lookup\_vector,result\_vector)}

The lookup and result vectors must have the same number of items.

Frequency and colour lookup data
Frequency (A)Colour (B)
4.14red
4.19orange
5.17yellow
5.77green
6.39blue

Use vector LOOKUP

  1. Check that the lookup vector is in ascending order.
    4.14,4.19,5.17,5.77,6.39\texttt{4.14,4.19,5.17,5.77,6.39}
  2. Specify the search value, lookup vector, and same-sized result vector.
    =LOOKUP(value,A2:A6,B2:B6)\texttt{=LOOKUP(value,A2:A6,B2:B6)}
  3. Read the corresponding result for the exact or next-smallest match.
    largest lookup value \leqvalue\texttt{largest lookup value \leq value}
Worked example

What does `=LOOKUP(7.66,A2:A6,B2:B6)` return for the given frequency and colour data?

  1. Search for 7.66 in the ascending frequency vector.
    =LOOKUP(7.66,A2:A6,B2:B6)\texttt{=LOOKUP(7.66,A2:A6,B2:B6)}
  2. The largest frequency not greater than 7.66 is 6.39.
    6.39\leq7.66\texttt{6.39\leq 7.66}
  3. The colour in the matching position is blue.
    LOOKUP result=blue\texttt{LOOKUP result=blue}
Answer: Blue, because 6.39 is the largest frequency not greater than 7.66.
Vector LOOKUP examples
FormulaResultReason
`=LOOKUP(4.19,A2:A6,B2:B6)`orangeExact match at 4.19.
`=LOOKUP(5.00,A2:A6,B2:B6)`orange4.19 is the largest lookup value not greater than 5.00.
`=LOOKUP(7.66,A2:A6,B2:B6)`blue6.39 is the largest lookup value not greater than 7.66.
`=LOOKUP(0,A2:A6,B2:B6)`#N/A0 is less than the smallest lookup value, 4.14.
⚠ Common mistake

Do not use an unsorted lookup vector or vectors of different sizes: the result may be incorrect, and the result vector must match the lookup vector in size.

Practice

A3 contains 104. What does `=IF(AND(A3>1,A3<100),A3,"The value is out of range.")` return?

Hint: Evaluate both conditions in AND first.
Key step: 104 is greater than 1 but is not less than 100, so AND returns FALSE.
Solution: The formula returns “The value is out of range.”
Practice

What does `=OR(TRUE,FALSE,TRUE)` return?

Hint: OR returns TRUE if at least one argument is TRUE.
Key step: Two arguments are TRUE, so the condition for a TRUE result is met.
Solution: TRUE.
Practice

Using the frequency and colour table, what does `=LOOKUP(5.00,A2:A6,B2:B6)` return?

Hint: Find the largest frequency less than or equal to 5.00.
Key step: The matching frequency is 4.19, whose colour is orange.
Solution: Orange.

Quick recap

  • CONCATENATE joins 2 to 255 items; add a separate space string when names need spaces.
  • AND returns TRUE only when all its logical arguments are TRUE.
  • OR returns TRUE when at least one logical argument is TRUE.
  • IF with AND tests several conditions and returns a value or a message based on the combined result.
  • LOOKUP vector form searches one row or column and returns the corresponding item from a same-sized result vector.
  • LOOKUP matching requires ascending lookup values and uses the largest value less than or equal to the search value; a value below the range returns #N/A.
Lecture 9

Basic Concepts Of Spreadsheet Part VIII

LOOKUP: Array Form

Definition

LOOKUP (array form) — Searches an array for a value and returns a corresponding value from the array’s opposite edge.

Key rules

  • Lookup value: May be a number, text, logical value, or a reference/name that points to a value.
  • Search direction: If the array is wider than it is tall, LOOKUP searches its first row; otherwise, it searches its first column.
  • Return value: For a wide array it returns from the last row; for a square or tall array, it returns from the last column.
  • Approximate match: If the exact value is absent, LOOKUP uses the largest value less than or equal to the lookup value.
  • Error: Returns #N/A if the lookup value is smaller than the smallest value in the search row or column.
LOOKUP syntax
LOOKUP⁡(v,A)\operatorname{LOOKUP}(v,A)

Use the array form to search an entire rectangular range.

Using LOOKUP with an array

  1. Identify the lookup value and the array range.
    LOOKUP⁡(v,A)\operatorname{LOOKUP}(v,A)
  2. Determine whether the array is wider than it is tall.
    Acolumns>ArowsA_{\text{columns}}>A_{\text{rows}}
  3. Search the first row if wide; otherwise search the first column.
    Acolumns>ArowsA_{\text{columns}}>A_{\text{rows}}
  4. Return the corresponding value from the last row or last column.
    Acolumns>ArowsA_{\text{columns}}>A_{\text{rows}}
Spreadsheet range A1:B4 contains text values a–d in column A and 10, 20, 30, 40 in column B.
Spreadsheet range A1:B4 contains text values a–d in column A and 10, 20, 30, 40 in column B.
Worked example

What does =LOOKUP("c",A1:B4) return when column A contains a, b, c, d and column B contains 10, 20, 30, 40?

  1. In A1:B4, the range is taller than it is wide, so LOOKUP searches column A and returns the corresponding value from column B.
    2<42<4
  2. Searching for c matches the third entry in column A.
    LOOKUP⁡(c,A1:B4)\operatorname{LOOKUP}(\text{c},A1:B4)
  3. The corresponding value in column B is 30.
    3030
Answer: It returns 30.
⚠ Common mistake

Do not assume LOOKUP always searches the first column: the array’s shape decides whether it searches the first row or first column.

VLOOKUP

Definition

VLOOKUP — Searches vertically in the first column of a table and returns a value from another column in the matching row.

VLOOKUP syntax
VLOOKUP⁡(v,T,c,r)\operatorname{VLOOKUP}(v,T,c,r)

Here, v is the lookup value, T is the table range, c is the return-column index, and r specifies the match type. The column index is counted from the first column of the selected table.

Arguments and results

  • lookup_value: Value or reference searched for in the table’s first column; if it is below the smallest value, the result is #N/A.
  • table_array: Range with at least two columns; its first column contains the values to search. Uppercase and lowercase text are treated as equivalent.
  • col_index_num: Column to return from the selected table; 1 returns its first column, 2 its second, and so on.
  • Invalid column index: An index below 1 returns #VALUE!; an index greater than the table’s column count returns #REF!.
  • range_lookup = TRUE or omitted: Allows approximate matching; the first column must be sorted in ascending order. An exact match is used when available; otherwise the next smaller value is returned.
  • range_lookup = FALSE: Requires an exact match; sorting is not required. If duplicates exist, the first match is used; no match returns #N/A.
Employee pay table
Employee CodeFirst NameLast NameBasic Pay (Rs.)
1873PrakashSharma5,176.00
3456ShaashikantThakur3,453.00
4537AjitMathur3,355.00
5678VinodUpadhyay428.00
16782KanuPatel3,457.00
34567AnilVaya5,467.00

Using VLOOKUP

  1. Choose the lookup value and a table range whose first column contains it.
    VLOOKUP⁡(v,T,c,r)\operatorname{VLOOKUP}(v,T,c,r)
  2. Set the column index to the position of the value to return.
    c≥1c\geq 1
  3. Use FALSE for an exact match or TRUE for an approximate match.
    r∈{TRUE⁡,FALSE⁡}r\in\{\operatorname{TRUE},\operatorname{FALSE}\}
  4. Check the returned value in the same row as the match.
    Tmatch row,cT_{\text{match row},c}
Worked example

Find the basic pay for employee code 3456 using the employee table.

  1. Search for employee code 3456 in the first column and request the fourth column with an exact match.
    VLOOKUP⁡(3456,A1:D7,4,FALSE⁡)\operatorname{VLOOKUP}(3456,A1:D7,4,\operatorname{FALSE})
  2. The matching row contains a basic pay of Rs. 3,453.00 in column 4.
    3,453.003{,}453.00
Answer: The basic pay is Rs. 3,453.00.
Baby products: cost and markup
Item IDItemCost (Rs.)Markup
AS-469Aspirator2.5645%
BI-567Bib3.5640%
DI-328Diapers21.4535%
ST-340Stroller145.6730%
WI-989Wipes5.1240%

VLOOKUP in calculations

  • Retail price: Multiply cost by one plus the markup rate; for DI-328, the price is 21.45×1.35=28.9621.45\times1.35=28.96 (rounded).
  • Discounted sale price: Multiply cost by one plus markup, then by one minus the discount; WI-989 with a 20% discount gives 5.12×1.40×0.80=5.735.12\times1.40\times0.80=5.73 (rounded).
  • Conditional message: IF can test whether the looked-up cost is at least Rs. 20 and display the markup; otherwise, it can report the cost or that it is under Rs. 20.
Price calculation examples
21.45(1+0.35)=28.9575≈28.96,5.12(1+0.40)(1−0.20)=5.7344≈5.7321.45(1+0.35)=28.9575\approx 28.96,\qquad 5.12(1+0.40)(1-0.20)=5.7344\approx 5.73

The examples use exact-match lookups; multiply the looked-up cost by the markup and discount factors.

Practice

Using LOOKUP array form, what is returned for lookup value b in A1:B4, where the first column is a, b, c, d and the second is 10, 20, 30, 40?

Hint: The array is taller than it is wide.
Key step: Search the first column and return the value from the last column in the same position.
Solution: b is in the second position, so the result is 20.
Practice

Find the retail price of diapers (DI-328) using the product table.

Hint: Look up the cost in column 3 and markup in column 4 with FALSE.
Key step: Apply cost × (1 + markup).
Solution: Cost = Rs. 21.45 and markup = 35%; therefore Rs. 21.45 × 1.35 = Rs. 28.9575, or Rs. 28.96 rounded.
Practice

A product costs Rs. 3.56 and has a 40% markup. What message should an IF test with a Rs. 20 cost threshold display?

Hint: Compare the looked-up cost with Rs. 20.
Key step: Since Rs. 3.56 is less than Rs. 20, use the under-threshold result.
Solution: It should display “Cost is under Rs. 20.00”.

Quick recap

  • LOOKUP array form: Searches the first row of a wide array or first column of a square/tall array.
  • LOOKUP result: Returns the corresponding value from the opposite edge; if no exact value exists, it uses the largest value not exceeding the lookup value.
  • VLOOKUP: Searches the table’s first column and returns a value from the matching row.
  • Column index: Counts from the first column of the selected table; invalid indices return #VALUE! or #REF!.
  • Match setting: FALSE requires an exact match; TRUE or omission permits approximate matching and requires ascending order.
Lecture 10

Basic Concepts Of Spreadsheet Part IX

HLOOKUP

Definition

HLOOKUP — HLOOKUP (Horizontal Lookup) searches the first row of a table array and returns a value from another row in the same column.

HLOOKUP arguments and rules

  • Lookup value — The value to search for in the table array’s first row.
  • Table array — A range with at least two rows; the first row may contain text, numbers, or logical values. Text matching is not case-sensitive.
  • Row index number — The row within the selected table array from which to return a value; for example, 22 means its second row.
  • Range lookup — FALSE requires an exact match. TRUE allows the nearest match when no exact match is found and generally requires the first row to be sorted in ascending order.
  • Copying formulas — Make the table-array reference absolute so it does not shift when the formula is copied.
HLOOKUP syntax
HLOOKUP⁡(lookup_value,table_array,row_index_num,range_lookup)\operatorname{HLOOKUP}(lookup\_value,table\_array,row\_index\_num,range\_lookup)

Use FALSE when the lookup must match exactly.

Using HLOOKUP

  1. Select the value to look up from the table’s first row.
  2. Select the complete table range and lock its cell references before copying the formula.
  3. Enter the row number containing the result and choose FALSE for an exact match.
  4. Retrieve the matching quarter’s budget, then subtract the amount spent to find the pending budget.
    F4=D4−E4F4=D4-E4
Spreadsheet example showing a budget table and the HLOOKUP formula used to retrieve quarterly budgets.
Spreadsheet example showing a budget table and the HLOOKUP formula used to retrieve quarterly budgets.
Worked example

How can the budget for the quarter in C4 be retrieved and its pending amount calculated?

  1. In D4, find the quarter named in C4 and return its budget from the second row of the Budget worksheet’s table.
    HLOOKUP⁡(C4;Budget!\A\1:\E\2;2;FALSE)\operatorname{HLOOKUP}(C4;\text{Budget!}\A\1:\E\2;2;\text{FALSE})
  2. In F4, subtract the amount spent in E4 from the retrieved budget.
    D4−E4D4-E4
Answer: Enter the HLOOKUP formula in D4 and calculate the pending amount in F4 as D4−E4D4-E4. The absolute table-array reference stays fixed when formulas are copied.
⚠ Common mistake

Do not confuse the row index with a column number: HLOOKUP returns from the specified row of the table array. Lock the table range before copying the formula.

Financial Functions

  • Purpose: Financial functions calculate values used in investments, loans and other financial applications.
  • Inputs: Their arguments can include an interest rate, number of periods, payments and present or future values.
  • Applications: Excel provides functions for interest, loan payments, investment values and net present value.

ACCRINT

Definition

ACCRINT — ACCRINT calculates accrued interest for a security that pays interest periodically.

ACCRINT arguments and dates

  • Issue — The security’s issue date; first_interest — The date of its first interest payment.
  • Settlement — The date after issue when the security is traded to the buyer.
  • Rate — The annual coupon rate; par — The security’s par value, defaulting to Rs. 1,000.
  • Frequency — Coupon payments per year: annual 11, semi-annual 22, and quarterly 44.
  • Basis — The day-count basis used; calc_method — The calculation-method argument.
  • Date inputs — Use actual date values, preferably with the DATE function; text dates can cause problems.
  • Date serials — Spreadsheet dates are stored as sequential numbers: January 1, 1900 is serial 11, and January 1, 2008 is serial 3944839448.
ACCRINT syntax
ACCRINT⁡(issue,first_interest,settlement,rate,par,frequency,basis,calc_method)\operatorname{ACCRINT}(issue,first\_interest,settlement,rate,par,frequency,basis,calc\_method)

Enter dates as date values, not text.

Accrued-interest calculation
ACCRINT=par×ratefrequency×∑i=1NCAiNLi\mathrm{ACCRINT}=\mathrm{par}\times\frac{\mathrm{rate}}{\mathrm{frequency}}\times\sum_{i=1}^{\mathrm{NC}}\frac{A_i}{\mathrm{NL}_i}

Here, NCNC is the number of coupon periods; Ai/NLiA_i/NL_i is the fraction of period ii accrued.

Preparing an ACCRINT calculation

  1. Enter the issue, first-interest, and settlement dates as date values.
  2. Supply the annual rate, par value, payment frequency, basis, and calculation method.
  3. Use the returned value as the accrued interest for the security.
Worked example

How should May 23, 2008 be entered as a date for an ACCRINT calculation?

  1. To enter May 23, 2008 as a date value, use the DATE function.
    DATE⁡(2008,5,23)\operatorname{DATE}(2008,5,23)
  2. Use this date in the appropriate date argument of ACCRINT; the example date alone does not identify which date argument it represents.
Answer: Enter it using DATE⁡(2008,5,23)\operatorname{DATE}(2008,5,23), placing the resulting date in the appropriate argument. A numerical accrued-interest result also requires the security’s other inputs.

CUMIPMT

Definition

CUMIPMT — CUMIPMT returns the cumulative interest paid over a selected range of payment periods.

CUMIPMT arguments

  • Rate — Interest rate per payment period; nper — Total number of payment periods.
  • pv — Present value of the loan.
  • Start period and end period — First and last periods included; payment periods are numbered from 11.
  • Type — Payment timing: 00 means end of period and 11 means beginning of period.
  • Returned sign — Interest paid is commonly returned as a negative cash flow.
CUMIPMT syntax
CUMIPMT⁡(rate,nper,pv,start_period,end_period,type)\operatorname{CUMIPMT}(rate,nper,pv,start\_period,end\_period,type)

Use the rate and number of periods in the same payment-period units.

Using CUMIPMT

  1. Convert the annual interest rate and loan term to the payment-period units.
  2. Enter the present value and the first and last periods to include.
  3. Set type to 0 for end-of-period payments or 1 for beginning-of-period payments.
Worked example

What is the cumulative interest for periods 13–24 on a 30-year loan of Rs. 125,000 at an annual interest rate of 9%, with end-of-month payments?

  1. Convert the 9%9\% annual rate to a monthly rate and the 30-year term to monthly periods.
    9%12,30×12=360\frac{9\%}{12},\quad 30\times12=360
  2. Select periods 13 through 24 and use type 0 for payments at the end of each month.
    CUMIPMT⁡(A2/12,A3×12,A4,13,24,0)\operatorname{CUMIPMT}(A2/12,A3\times12,A4,13,24,0)
  3. For the loan present value of Rs. 125,000, the function returns the second year’s total interest.
    −11,135.29-11{,}135.29
Answer: Use CUMIPMT⁡(A2/12,A3×12,A4,13,24,0)\operatorname{CUMIPMT}(A2/12,A3\times12,A4,13,24,0). The result is −11,135.29-11{,}135.29.
Practice

A table has quarter names in its first row and budgets in its second row. Which HLOOKUP row index returns a budget?

Hint: The row index counts rows within the selected table array.
Key step: Budgets are in the table array’s second row.
Solution: Use row index 22; for an exact lookup, set range_lookup to FALSE.
Practice

What frequency should be used for a security that pays interest quarterly?

Hint: Frequency is the number of coupon payments per year.
Key step: There are four quarters in a year.
Solution: Use frequency 44.
Practice

Using the loan data in the example, find the interest for the first month when payments are made at period end.

Hint: Use periods 1 to 1 and type 0.
Key step: Enter the monthly rate and total monthly periods.
Solution: Use CUMIPMT⁡(A2/12,A3×12,A4,1,1,0)\operatorname{CUMIPMT}(A2/12,A3\times12,A4,1,1,0). The result is −937.50-937.50.

Quick recap

  • HLOOKUP searches the first row and returns a value from the specified row in the matching column.
  • Use FALSE for an exact HLOOKUP match; use an absolute table-array reference when copying formulas.
  • ACCRINT calculates accrued interest using security dates, coupon rate, par value, frequency, basis, and calculation method.
  • Enter ACCRINT dates as date values, such as with the DATE function, rather than as text.
  • CUMIPMT calculates cumulative interest for a selected range of payment periods.
  • For CUMIPMT, match the rate and total periods to the payment frequency; type 0 means end-of-period payments and type 1 means beginning-of-period payments.
Lecture 11

Basic Concepts Of Spreadsheet Part X

Present Value (PV)

PV function syntax
PV⁡(rate,nper,pmt,[fv],[type])\operatorname{PV}(rate,nper,pmt,[fv],[type])

Use the fvfv and typetype arguments when needed. If fvfv is omitted, pmtpmt must be supplied; if pmtpmt is omitted, fvfv must be supplied.

PV cash-flow relationship

For a non-zero periodic rate, the present value, payments and future value balance to zero when cash-flow signs are entered consistently.

PV(1+rate)nper+pmt(1+rate⋅type)((1+rate)nper−1rate)+fv=0PV(1+rate)^{nper}+pmt(1+rate\cdot type)\left(\frac{(1+rate)^{nper}-1}{rate}\right)+fv=0
PV relationship when the rate is zero
pmt⋅nper+PV+fv=0pmt\cdot nper+PV+fv=0

Use this special case when the periodic interest rate is zero.

Worked example

What is the present value of the payment series?

  1. Find the present value of monthly payments of Rs. 500 for 20 years at an annual interest rate of 8%, paid at each month's end.
  2. Convert the annual rate and years into monthly terms.
    rate=8%12,nper=20⋅12=240rate=\frac{8\%}{12},\qquad nper=20\cdot12=240
  3. Use the payment as a positive cash flow; the calculated present value has the opposite sign.
    PV⁡(8%12,240,500,0,0)=−59,777.15\operatorname{PV}\left(\frac{8\%}{12},240,500,0,0\right)=-59{,}777.15
Answer: The present value is Rs. 59,777.15 in magnitude; the function returns -Rs. 59,777.15 because the payment and present value are opposite cash flows.
⚠ Common mistake

Do not pair an annual rate with a monthly period count: convert both to the same time unit. Also, a negative PV result may simply reflect the cash-flow sign convention.

Future Value (FV)

FV function syntax
FV⁡(rate,nper,pmt,[pv],[type])\operatorname{FV}(rate,nper,pmt,[pv],[type])

If pmtpmt is omitted, include pvpv; if pvpv is omitted, include pmtpmt.

FV cash-flow relationship

FV balances the grown present value and accumulated payments, with cash-flow signs entered consistently.

FV=−[PV(1+rate)nper+pmt(1+rate⋅type)((1+rate)nper−1rate)]FV=-\left[PV(1+rate)^{nper}+pmt(1+rate\cdot type)\left(\frac{(1+rate)^{nper}-1}{rate}\right)\right]
FV relationship when the rate is zero
FV=−(PV+pmt⋅nper)FV=-(PV+pmt\cdot nper)

At a zero rate, the future value is the opposite of the present value plus all payments.

Worked example

How does the payment timing affect the result?

  1. Find the future value using a monthly rate of 6% per year divided by 12, 10 periods, payments of Rs. 200 and a present value of Rs. 500; payments are at the beginning of each period.
  2. Enter the payment and present value as negative cash flows, and set the payment timing to the beginning of the period.
    FV⁡(6%12,10,−200,−500,1)=2,581.40\operatorname{FV}\left(\frac{6\%}{12},10,-200,-500,1\right)=2{,}581.40
  3. Changing only the payment timing to the end of each period gives a lower future value.
    FV⁡(6%12,10,−200,−500,0)=2,571.18\operatorname{FV}\left(\frac{6\%}{12},10,-200,-500,0\right)=2{,}571.18
Answer: Beginning-of-period payments give Rs. 2,581.40, compared with Rs. 2,571.18 for end-of-period payments.
⚠ Common mistake

Do not confuse pvpv (the initial amount) with fvfv (the final balance). Use consistent cash-flow signs and the correct payment timing.

Practice

What is the present value of 12 end-of-period payments of Rs. 100 when the periodic rate is zero?

Hint: Use the zero-rate PV relationship and enter payments as positive cash flows.
Key step: PV+100⋅12=0PV+100\cdot12=0
Solution: The calculation gives PV=−1,200PV=-1{,}200; the present value is Rs. 1,200 in magnitude.
Practice

Find the future value after 12 end-of-period payments of Rs. 100 and an initial investment of Rs. 500, when the periodic rate is zero.

Hint: Enter the payment and initial investment as negative cash flows.
Key step: FV=−[−500+(−100)⋅12]FV=-\left[-500+(-100)\cdot12\right]
Solution: The future value is Rs. 1,700.

Quick recap

  • PV calculates the current worth of future annuity payments; FV calculates an investment's value at the end.
  • Match the rate period to the payment-period count.
  • Payments are fixed across periods and usually include principal and interest, not fees or taxes.
  • For PV, omitted fvfv requires pmtpmt; omitted pmtpmt requires fvfv.
  • For FV, omitted pvpv requires pmtpmt; omitted pmtpmt requires pvpv.
  • type=0type=0 means end-of-period payments; type=1type=1 means beginning-of-period payments.
  • Use positive and negative cash flows consistently; the calculated balancing value may have the opposite sign.
Lecture 12

Basic Concepts Of Spreadsheet Part XI

PMT Function

Definition

PMT function — Calculates the equal periodic payment for an annuity or loan when the interest rate is constant.

Arguments and use

  • rate is the interest rate per payment period; convert an annual rate to match the payment frequency.
  • nper is the total number of payment periods; pv is the present value or loan amount.
  • fv is the balance desired after the last payment; if omitted, it is assumed to be zero.
  • type is 00 or omitted for end-of-period payments and 11 for beginning-of-period payments.
  • Use: PMT is commonly used to calculate fixed mortgage or loan payments.
PMT syntax
PMT⁡(rate,nper,pv,[fv],[type])\operatorname{PMT}(rate,nper,pv,[fv],[type])

Match the rate and period count to the payment frequency.

Worked example

Find the monthly payment for both payment timings.

  1. For a Rs. 10,000 loan at 8% annually with monthly payments over 10 months, convert the rate to a monthly rate.
    rate=0.08/12,nper=10,pv=10000rate=0.08/12,\quad nper=10,\quad pv=10000
  2. Use the default type for payments at each month-end.
    PMT⁡(0.08/12,10,10000)\operatorname{PMT}(0.08/12,10,10000)
  3. Set type to 1 for payments at each month-beginning.
    PMT⁡(0.08/12,10,10000,0,1)\operatorname{PMT}(0.08/12,10,10000,0,1)
Answer: End of period: approximately (Rs. 1,037.03). Beginning of period: approximately (Rs. 1,030.16). Parentheses indicate a cash outflow.
⚠ Common mistake

Do not use the annual rate directly with monthly payments: divide it by 1212 and use the total number of months for nper.

RATE Function

Definition

RATE function — Finds the interest rate per period of an annuity from its payment and present or future value.

Arguments and calculation

  • nper is the total number of payment periods; pmt is the fixed payment each period.
  • pv is the present value; fv is the balance after the last payment and defaults to zero.
  • If pmt is omitted, include fv as an argument.
  • type is 00 or omitted for end-of-period payments and 11 for beginning-of-period payments; guess is the starting estimate and defaults to 10%10\%.
  • Iteration: RATE may have zero or more solutions. If results do not converge within 0.00000010.0000001 after 2020 iterations, it returns #NUM!.
RATE syntax
RATE⁡(nper,pmt,pv,[fv],[type],[guess])\operatorname{RATE}(nper,pmt,pv,[fv],[type],[guess])

RATE returns the rate per payment period. For monthly payments, multiply the monthly rate by 1212 to obtain a nominal annual rate.

Worked example

Find the monthly rate and nominal annual rate for the loan.

  1. A four-year loan has monthly payments of Rs. 200 and a present value of Rs. 8,000; convert the term to months.
    nper=4×12=48nper=4\times12=48
  2. Use the payment as a cash outflow and find the monthly rate.
    RATE⁡(48,−200,8000)≈0.00770\operatorname{RATE}(48,-200,8000)\approx0.00770
  3. Annualise the monthly rate by multiplying by 12.
    0.00770×12≈0.09240.00770\times12\approx0.0924
Answer: Monthly rate: approximately 0.77%. Nominal annual rate: approximately 9.24%.
⚠ Common mistake

Do not confuse the rate per payment period with the annual rate. RATE requires pmt or the appropriate fv argument.

NPV Function

Definition

NPV function — Calculates the present value of a series of future cash flows using a discount rate.

Arguments and interpretation

  • rate is the discount rate for one period; payments are negative cash flows and income is positive.
  • Cash-flow arguments: value1 through value254 allow 11 to 254254 cash-flow values.
  • Timing: cash flows must be equally spaced and are treated as occurring at the end of each period.
  • Order matters: enter cash flows chronologically; NPV discounts the first listed value by one period.
  • IRR link: IRR is the rate that makes NPV equal to zero.
NPV syntax and formula
NPV⁡(rate,value1,…,valuen)=∑i=1nvaluei(1+rate)i\operatorname{NPV}(rate,value_1,\ldots,value_n)=\sum_{i=1}^{n}\frac{value_i}{(1+rate)^i}

NPV discounts future cash flows beginning with period 11.

PV vs NPV
PointPVNPV
Cash flowsConstant periodic cash flowsCash flows may vary from period to period
TimingCan allow payments at the beginning or end of a periodListed values are treated as end-of-period future cash flows
Worked example

Find the NPV of the listed cash flows at 10%.

  1. Discount the four cash flows in order at 10% per period.
    −100001.1+30001.12+42001.13+68001.14\frac{-10000}{1.1}+\frac{3000}{1.1^2}+\frac{4200}{1.1^3}+\frac{6800}{1.1^4}
  2. Enter the rate and cash flows in chronological order.
    NPV⁡(10%,−10000,3000,4200,6800)\operatorname{NPV}(10\%,-10000,3000,4200,6800)
  3. Add the discounted values to obtain the NPV.
    −9090.9091+2479.3388+3155.5229+4644.7647≈1188.7173-9090.9091+2479.3388+3155.5229+4644.7647\approx1188.7173
Answer: Approximately Rs. 1,188.72 from the listed cash flows. The reference table reports Rs. 1,188.44, but direct calculation gives approximately Rs. 1,188.72.
⚠ Common mistake

NPV discounts the first listed value by one period. If an investment cost occurs immediately at time zero, keep it outside NPV and add it to the discounted future cash flows.

Practice

A loan of Rs. 5,000 is repaid monthly over 12 months at 6% annual interest. Write the PMT formula for end-of-month payments.

Hint: Convert the annual rate to a monthly rate.
Key step: Use rate =6%/12=6\%/12 and nper =12=12.
Solution: Use PMT⁡(6%/12,12,5000)\operatorname{PMT}(6\%/12,12,5000). The returned payment is negative under the usual cash-flow sign convention.
Practice

A three-year loan has monthly payments. What nper value should be used in RATE?

Hint: Convert years to months.
Key step: Multiply the number of years by the payment periods per year.
Solution: Use nper=3×12=36nper=3\times12=36; for example, start with RATE⁡(36,pmt,pv)\operatorname{RATE}(36,pmt,pv).
Practice

An investment costs Rs. 5,000 immediately and returns Rs. 2,000 at the end of each of the next three years. Write its NPV formula at a 10% annual discount rate.

Hint: Keep the immediate cost separate from future cash flows.
Key step: Add the time-zero cost to NPV of the three future returns.
Solution: −5000+NPV⁡(10%,2000,2000,2000)-5000+\operatorname{NPV}(10\%,2000,2000,2000).

Quick recap

  • PMT calculates equal periodic payments; match the rate and number of periods to the payment frequency.
  • PMT timing: type 00 means end-of-period payments; type 11 means beginning-of-period payments.
  • RATE finds the interest rate per period; if pmt is omitted, include fv.
  • NPV discounts 11 to 254254 equally spaced, chronological cash flows, with the first flow discounted by one period.
  • Time-zero costs are handled separately; IRR is the rate that makes NPV equal zero.
Lecture 13

Data Entry Text Management And Cell Formatting Part I

Entering and Recognising Data

Worked example

What should happen when you enter 16/04/1980 in a cell?

  1. Enter a date into a cell.
  2. When the entry is recognised as a date, the spreadsheet can process and display it as date data.
Answer: The spreadsheet recognises it as a date, provided the entry is interpreted as a valid date in the spreadsheet’s settings.

Filling Data with the Fill Handle and Series Command

Fill Handle Settings

  • Show or hide: Open Microsoft Office Button → Excel Options → Advanced → Editing options, then select or clear Enable Fill handle and cell drag-and-drop.
  • Overwrite warning: Keep Alert before overwriting cells selected to receive a warning before dragging over non-blank cells.

Extend a Pattern Using the Fill Handle

  1. Enter enough starting values to show the pattern.
  2. Select the starting cells and drag the fill handle across the adjacent cells to fill.
  3. Choose the required option from Auto Fill Options, if needed.
Linear Series
an=a1+(n−1)da_n=a_1+(n-1)d

Use this rule to find the nnth value when each value increases by a constant step.

Fill a Series Using the Series Command

  1. Enter the starting value in the first cell.
  2. Select the range, then choose Home → Editing → Fill → Series.
  3. Choose whether to fill by rows or columns, and enter the Step Value and Stop Value.
  4. Click OK to fill the series until the stop value is reached.
Worked example

Fill the range A1:A10A1:A10 with a series starting at 10 and increasing by 10.

  1. Enter the first two values in the starting cells.
    10, 2010,\ 20
  2. The difference between consecutive values is the step.
    d=20−10=10d=20-10=10
  3. Continue adding the step until the stop value is reached.
    10, 20, 30, 40, 50, 60, 70, 80, 90, 10010,\ 20,\ 30,\ 40,\ 50,\ 60,\ 70,\ 80,\ 90,\ 100
Answer: Set the Step Value to 10 and the Stop Value to 100. The range contains 10 through 100 in increments of 10.

Filling a Date Series

  • Date format: Set the cells to a suitable date format before filling.
  • Pattern: Enter two dates that show the intended sequence, then use the fill handle to continue it.
  • Example: Starting with 24-Nov-1952 and 24-Dec-1952 continues the monthly sequence into later months.
Practice

You enter 5 and 8 in two consecutive cells, then drag the fill handle. What pattern should continue?

Hint: Find the difference between the first two values.
Key step: The step is 8−5=38-5=3.
Solution: The series continues 11, 14, 17, and so on.
Practice

Which Series settings fill a column from 10 to 100 in increments of 10?

Hint: Identify the direction, increment and final value.
Key step: Choose columns, Step Value 10 and Stop Value 100.
Solution: Select a column range and set Step Value to 10 and Stop Value to 100.
⚠ Common mistake

Do not assume the fill handle always repeats a value: it may extend a pattern from the selected starting data. Use Auto Fill Options to choose the intended result.

Quick recap

  • Enter accurate data in an appropriate format so the spreadsheet can interpret it correctly.
  • Numbers are right-aligned by default; text is left-aligned.
  • The fill handle extends data patterns into adjacent cells.
  • Auto Fill Options controls whether contents, formatting or both are filled.
  • The Series command uses direction, Step Value and Stop Value to generate a series.
  • Use date-formatted cells and starting dates that show the desired date pattern.
Lecture 14

Data Entry Text Management And Cell Formatting Part II

Copying and importing data

Importing data from text files

  • Importing data: Data can be copied or imported into a worksheet from text or non-text files.
  • Text files: Files such as `.txt` and `.csv` can be read by a text editor and imported into Excel.
  • CSV structure: Each line represents a spreadsheet row; commas separate the fields placed in that row’s columns.
  • Headings and records: Put column names on the first line and corresponding data records on the following lines.
  • Consistent fields: Keep fields in the same order as the headings; separate every field with a comma, including blank fields.
  • Import route: In the version described, select Data → Get External Data → From Text, then choose the saved text file.
  • Result: Imported data is placed in worksheet rows and columns; the worksheet can then be saved as an Excel data file.
Data tab options for importing external data and managing connections.
Data tab options for importing external data and managing connections.

Types and appearance of cell data

Cell content and display

  • Labels: Descriptive text, such as names or month names, usually contains letters and is aligned to the left.
  • Values: Numeric entries include whole numbers and decimals; whole and decimal values are aligned to the right.
  • Decimal display: Excel may omit trailing zeros: for example, `246.70` may display as `246.7`.
  • Formulas: Formulas are instructions that tell Excel to perform calculations.
  • Dates: Excel may display an entered date in a different format. For example, an entry such as `16/12` may display as `16-Dec`; the formula bar may show `16/12/2008`.
  • Date format: Display depends partly on the country-specific format setting.
  • Formatting: Cell display, column width and alignment can be changed.
  • Data validation: The source names data validation alongside labels, values, dates and formulas, but does not explain it.
⚠ Common mistake

Do not assume that a cell’s displayed value exactly matches its entered form: trailing decimal zeros may be hidden, and dates may display in different formats. Excel aligns text labels to the left and numeric values, including dates, to the right; formula results are aligned according to their value type.

How does a CSV file map to spreadsheet rows and columns?

Answer: Each line becomes a row, and commas separate the fields placed in that row’s columns. The first line can provide column headings.

How do labels and numeric values typically align in Excel?

Answer: Labels are normally left-aligned; whole and decimal values are normally right-aligned.

Quick recap

  • Data can be copied or imported from text and non-text files.
  • In a CSV file, each line becomes a row and commas separate fields into columns.
  • The first line can contain column headings; keep later fields in the same order, including blank fields.
  • Text labels are usually left-aligned; whole and decimal values are right-aligned.
  • Excel may hide trailing decimal zeros and display dates in a different format.
  • Formulas instruct Excel to calculate; the source mentions data validation but does not define it.
Lecture 15

Data Entry Text Management And Cell Formatting Part III

Data Validation

Definition

Data validation — A spreadsheet feature that sets rules for the type or values of data allowed in a cell.

Purpose and benefits

  • Accuracy: Restricts invalid entries and helps keep spreadsheet data accurate and consistent.
  • Guidance: Input messages tell users what to enter; error alerts respond when an entry breaks a rule.
Data tab tools include Data Validation and Conditional Formatting.
Data tab tools include Data Validation and Conditional Formatting.

Creating a Drop-Down List

Uses and examples

  • Drop-down list: Offers predefined values and limits entries to the options provided.
  • Examples: Department names such as Sales, Finance, R&D, Operations, HR and IT; qualifications such as MBA, PhD, CA, MCA, Architect and MBBS; sex codes Male or Female.
Ways to provide drop-down list values
MethodHow it works
Delimited listType values separated by commas, for example Male, Female.
Worksheet cellsSelect a row or column of cells containing the list values.
Named rangeName a range containing the values and use that name as the source.

Set up a named-range drop-down list

  1. Create worksheet headers such as Employee Name, Department, Qualification and Sex Code; enter employee names beneath the first header.
  2. Enter the department options in a worksheet range, for example G3:G9, then define the range name DEPT using Formula > Define Name.
  3. Select the Department cells to validate, then open Data > Data Validation from the Data Tools group.
  4. On the Settings tab, choose List and enter =DEPT in Source; enable Ignore Blank and the in-cell drop-down.
The Settings tab provides the allowed-entry types for validation.
The Settings tab provides the allowed-entry types for validation.
A named range such as DEPT can be entered as the drop-down list source.
A named range such as DEPT can be entered as the drop-down list source.

Ignore Blank option

  • Effect: As described in this lecture, selecting Ignore Blank means empty or blank values will not be allowed.

Input Messages and Error Alerts

Input message

  • Purpose: Displays guidance when a validated cell is selected, such as B2 or the range B3:B10.
  • Setup: On the Input Message tab, enter a title (for example, DEPT_T) and a short instruction (for example, SELECT/ENTER DEPARTMENT); enable display when the cell is selected.
An input message can show the user a short instruction when a validated cell is selected.
An input message can show the user a short instruction when a validated cell is selected.
Error Alert styles
StyleEffect
StopPrevents invalid data from being entered.
WarningShows a warning but does not prevent the invalid entry.
InformationDisplays an information message and prevents invalid data from being entered.

Configure an error alert

  • Setup: On the Error Alert tab, enable the alert, enter a title and message, then select Information, Warning or Stop according to the required level of control.
The Error Alert tab lets you set the alert message and choose its severity style.
The Error Alert tab lets you set the alert message and choose its severity style.

Quick recap

  • Data validation restricts cell entries to improve accuracy and consistency.
  • Drop-down values can come from a comma-separated list, worksheet cells or a named range.
  • For a named-range list, select List in Settings and enter the range name as the Source.
  • Selecting Ignore Blank means blank values will not be allowed, as described in this lecture.
  • Input messages guide users when they select a validated cell.
  • Information and Stop prevent invalid entries; Warning displays a warning but allows them.
Lecture 16

Data Entry Text Management And Cell Formatting Part IV

Data Validation Using a List

A department drop-down list and a data-validation error alert.
A department drop-down list and a data-validation error alert.

Set up a list

  1. Select the cells to control and open Data Validation.
    Select cells\text{Select cells}
  2. Choose List as the allowed data type.
    Allow: List\text{Allow: List}
  3. Enter the allowed items in the Source box, separated by commas, then set an error alert if needed.
    Source: Male,Female\text{Source: Male,Female}
Worked example

How can a cell be restricted to Male or Female?

  1. To restrict a Sex Code cell to two entries, choose List and enter the allowed values.
    Male,Female\texttt{Male,Female}
  2. A valid listed value is accepted; an unlisted or differently capitalised value may be rejected.
    Male or Female\text{Male or Female}
Answer: Use List validation with Male,Female as the Source.

Setting Numeric and Text Limits

Whole-number age validation with a minimum of 18 and maximum of 58.
Whole-number age validation with a minimum of 18 and maximum of 58.

Set a permitted range

  1. Select the cells and open Data Validation.
    Select cells\text{Select cells}
  2. Choose the required type, such as Whole Number or Text Length.
    Choose type\text{Choose type}
  3. Select the boundary condition and enter the minimum and/or maximum values.
    18≤x≤5818\leq x\leq58
  4. Set an error alert to explain rejected entries.
    Set alert\text{Set alert}
Worked example

What validation rule allows employee ages from 18 through 58?

  1. For an employee age cell, choose Whole Number and set the inclusive lower and upper limits.
    18≤x≤5818\leq x\leq58
  2. An age within the range is accepted; an age below 18 or above 58 is rejected.
    18≤x≤5818\leq x\leq58
Answer: Choose Whole Number and set the minimum to 18 and maximum to 58.

Formula-Based Data Validation

Custom validation settings use a COUNTIF formula to prevent duplicate employee numbers.
Custom validation settings use a COUNTIF formula to prevent duplicate employee numbers.
An error alert appears when a duplicate employee number is entered.
An error alert appears when a duplicate employee number is entered.
A custom formula checks whether the total budget allocation stays within its limit.
A custom formula checks whether the total budget allocation stays within its limit.
Useful custom validation formulas
=COUNTIF(\B\2:\B\100,B2)=1=SUM(\C\2:\C\10)<=\F\1=B2=TRIM(B2)=AND(WEEKDAY(A2)<>1,WEEKDAY(A2)<>7)\begin{aligned}\texttt{=COUNTIF(\B\2:\B\100,B2)=1}\\\texttt{=SUM(\C\2:\C\10)<=\F\1}\\\texttt{=B2=TRIM(B2)}\\\texttt{=AND(WEEKDAY(A2)<>1,WEEKDAY(A2)<>7)}\end{aligned}

Adjust cell references to match the worksheet. To exclude holidays, also check that the date is absent from a holiday list.

A validation formula rejects employee names with extra spaces at the beginning or end.
A validation formula rejects employee names with extra spaces at the beginning or end.
A weekend date is rejected and an error alert is displayed.
A weekend date is rejected and an error alert is displayed.

Create a custom validation rule

  1. Select the cell or range to validate and open Data Validation.
    Select cells\text{Select cells}
  2. Choose Custom in the Allow menu.
    Allow: Custom\text{Allow: Custom}
  3. Enter a formula that evaluates to TRUE for valid entries and FALSE for invalid entries.
    TRUE valid; FALSE invalid\text{TRUE valid; FALSE invalid}
  4. Configure the error alert so users know why an entry was rejected.
    Set alert\text{Set alert}
Worked example

How can duplicate employee numbers be blocked in B2:B100?

  1. To prevent duplicate codes in B2:B100, count how many times the current entry occurs in the range.
    =COUNTIF(\B\2:\B\100,B2)\texttt{=COUNTIF(\B\2:\B\100,B2)}
  2. Allow the entry only when its count is exactly one.
    =COUNTIF(\B\2:\B\100,B2)=1\texttt{=COUNTIF(\B\2:\B\100,B2)=1}
  3. If the code is already present, the formula returns FALSE and validation blocks the duplicate.
    FALSE\text{FALSE}
Answer: Apply the custom formula =COUNTIF($B$2:$B$100,B2)=1\texttt{=COUNTIF(\$B\$2:\$B\$100,B2)=1} to the range.
⚠ Common mistake

Do not assume a custom formula rejects entries when it returns TRUE: TRUE means the entry is valid. Check that formula references match the validated cells and range.

Practice

Write a custom validation formula to prevent extra leading or trailing spaces in cell B2.

Hint: Compare the cell value with the result of TRIM.
Key step: The entry must equal its trimmed version.
Solution: =B2=TRIM(B2)\texttt{=B2=TRIM(B2)}
Practice

Write a validation formula to reject weekend dates entered in A2.

Hint: With the default WEEKDAY numbering, Sunday is 1 and Saturday is 7.
Key step: Require the weekday number to be neither 1 nor 7.
Solution: =AND(WEEKDAY(A2)<>1,WEEKDAY(A2)<>7)\texttt{=AND(WEEKDAY(A2)<>1,WEEKDAY(A2)<>7)}
Practice

Amounts are in C2:C10 and the budget limit is in F1. Write a formula that prevents the total from exceeding the budget.

Hint: Compare the sum of the amount cells with the budget cell.
Key step: The sum must be less than or equal to the budget.
Solution: =SUM($C$2:$C$10)<=$F$1\texttt{=SUM(\$C\$2:\$C\$10)<=\$F\$1}

Quick recap

  • List validation restricts entries to specified values; the list method may be case-sensitive.
  • Use Whole Number, Decimal, Date, Time, or Text Length limits to control permitted entries.
  • For employee ages, use Whole Number from 18 through 58, or enter a Date of Birth and calculate age.
  • Custom validation accepts an entry when its formula evaluates to TRUE and rejects it when FALSE.
  • COUNTIF can block duplicate codes, while SUM can enforce a budget ceiling.
  • TRIM can detect extra leading or trailing spaces; WEEKDAY can block weekend dates.
Lecture 17

Data Entry Text Management And Cell Formatting Part V

Data Forms

Definition

Form — A form is a document with a standard structure for capturing, organising and editing information.

Definition

Data form — A data form is a dialog box that displays one complete spreadsheet record at a time, making it easier to add, find, change or delete records.

Purpose and use

  • Easier entry: Use a data form instead of moving across many columns, especially when all columns cannot fit on the screen.
  • Other entry option: Spreadsheet data may also be entered using drop-down lists.
  • Best suited to: Simple records where text boxes labelled with column headings are enough; it is not designed for sophisticated custom controls such as list boxes or spin buttons.

Adding the Form Button in Excel 2007

Before opening a data form

  • Column headings: Enter all data names in the worksheet’s first row; the form uses these names as field labels.
  • Button location: The Form button is not included on the Ribbon by default, so add it to the Quick Access Toolbar.

Add Form to the Quick Access Toolbar

  1. Click the arrow beside the Quick Access Toolbar, then choose More Commands.
  2. In “Choose commands from,” select All Commands.
  3. Select Form in the command list and click Add.

Using the Built-In Data Form

Form features and limits

  • Layout: Excel creates a dialog box showing the column headings as labels, each beside a text box for that field.
  • Column limit: A data form can display up to 32 columns.
  • Record actions: Enter new rows; navigate to or find rows using their contents; update existing rows; and delete rows.
  • Formula cells: The form displays a formula’s result, but the formula itself cannot be changed through the form.
Spreadsheet vendor table with a data-entry form displaying the third vendor record.
Spreadsheet vendor table with a data-entry form displaying the third vendor record.
★ Remember
  • First row = field names: Data forms use the worksheet’s first-row headings as labels.
  • Formula results are viewable, not editable: Change formulas in the worksheet, not through the data form.

Quick recap

  • A form uses a standard structure to capture, organise and edit information.
  • A data form displays one complete spreadsheet record at a time.
  • It helps enter, find, update and delete spreadsheet records.
  • Put the column names in the worksheet’s first row before using the form.
  • In Excel 2007, add the Form button to the Quick Access Toolbar through More Commands.
  • The built-in form supports up to 32 columns and uses labelled text boxes.
  • Formula results appear in the form, but formulas cannot be edited there.
Lecture 18

Data Formatting Part I

Definition

Spreadsheet formatting — Formatting changes how spreadsheet information is displayed so that important data is easier to read and understand.

Number formats

  • Number formatting controls how values appear, including currency, percentages, commas, decimal places, dates, times, scientific values and special numbers.
  • Automatic recognition Excel may assign a format when a value is entered in a recognizable pattern.
  • Currency Entering a value with a currency sign and two decimal places can cause Excel to apply a Currency format.
  • Percentage Entering a whole-number percentage followed by % can cause Excel to apply Percentage format.
  • Date A recognizable date such as 16-04-2009 or 16 Apr-2009 is treated as a date and displayed in a date format.
  • Display vs value Number formatting changes how a value appears; it does not change the underlying value.
Examples of number formats
FormatExample displayPurpose
CurrencyRs. 40Shows a currency symbol with a financial value.
Percentage0.856 displayed as 85.60%Shows a value as a percentage; decimal places can be set.
Date16-04-2009Displays a date using a recognized date pattern.
SpecialPhone number or ZIP codeDisplays numbers in a format appropriate to a specific country or use.

Applying number formats

Apply Currency or Percentage format

  1. Select the cell or range to format.
  2. Open the Format Cells dialog from the Home tab’s Number options.
  3. On the Number tab, choose the required category, such as Currency or Percentage.
  4. Set options such as currency symbol, negative-value style or number of decimal places.
  5. Click OK to apply the selected format.
Format Cells dialog for number formats; Currency options include decimal places and negative-value styles. Accounting formats align decimal points.
Format Cells dialog for number formats; Currency options include decimal places and negative-value styles. Accounting formats align decimal points.
Worked example

How can ranges B4:E7 and H4:H8 be formatted as Currency with no decimals and Percentage with two decimals, respectively?

  1. To format B4:E7 as Currency with zero decimal places, select the range B4:E7.
  2. Open Format Cells and choose Currency on the Number tab.
  3. Set decimal places to 0 and choose the required negative-number style.
  4. Click OK; the selected values display as currency rounded to whole units, for example Rs. 40.
  5. To show H4:H8 as percentages to two decimal places, repeat the process using Percentage and set decimal places to 2; 0.856 displays as 85.60%.
Answer: Select each range, choose its category in Format Cells, set the decimal places, and click OK.
⚠ Common mistake

Do not confuse a displayed percentage with the stored value: formatting 0.856 as Percentage displays 85.60%, but the underlying value remains 0.856.

Special number formats

  • Use Special formats are designed for numbers such as telephone numbers, ZIP codes and Social Security numbers.
  • Set format In Format Cells, choose Special, then select the suitable format from the Locale list for the relevant country.
Format Cells dialog showing Special number formats.
Format Cells dialog showing Special number formats.

Cell colours and patterns

Change cell fill

  1. Select the cell or range to format.
  2. Open Format Cells from the Home tab’s Number options and choose the Fill tab.
  3. Choose a background colour, pattern colour and pattern style as needed.
  4. Click OK to apply the fill to the selected cells.
Format Cells Fill tab with background colour, pattern colour and pattern style options.
Format Cells Fill tab with background colour, pattern colour and pattern style options.

Ribbon number-formatting controls

  • Comma Style Adds comma separators to large numbers to make them easier to read; it may also display two decimal places.
  • Decrease Decimal Removes one displayed decimal place each time the button is clicked.
  • Increase Decimal Adds displayed decimal places; click as often as required.
Ribbon controls for number formats, percentage, currency and decimal places.
Ribbon controls for number formats, percentage, currency and decimal places.

Custom formats and checking existing formats

  • Custom format In Format Cells, choose Custom, select an existing format and add characters or remove unwanted ones to create the desired display.
  • Inspect or change Right-click the cell, cells or column and choose Format Cells; identify the current category or select a suitable new or custom format.
Format Cells dialog showing number-format categories and Custom formats.
Format Cells dialog showing number-format categories and Custom formats.

How do you format a selected range as Currency with zero decimal places?

Answer: Open Format Cells, choose Currency, set decimal places to 0, select the required negative-value style and click OK.

Which format is suitable for telephone numbers, and how is it selected?

Answer: Use Special; choose the appropriate number format from the Locale list.
Practice

A cell contains 1.2636. What will it display after applying Percentage format with two decimal places?

Hint: Percentage format displays the value as a percent and rounds to the chosen decimal places.
Key step: Convert the display to a percentage and retain two decimal places.
Solution: It displays as 126.36%. The underlying value remains 1.2636.
Practice

How would you display a number with a comma separator and remove its displayed decimal places?

Hint: Use the Number section of the Ribbon.
Key step: Apply Comma Style, then decrease the number of displayed decimal places.
Solution: Click Comma Style, then click Decrease Decimal repeatedly until no decimal places are displayed.

Quick recap

  • Number formatting controls the display of currency, percentages, dates, decimals, commas and special numbers.
  • Format Cells lets you choose a category and set options such as decimal places and negative-value style.
  • Percentage formatting changes how a value is displayed, not its stored value.
  • Fill formatting offers background colours and cell patterns.
  • Ribbon controls provide Comma Style and quick decimal-place adjustments.
  • Custom formats can be edited, and existing formats can be checked through Format Cells.
Lecture 19

Data Formatting Part II

Text Formatting

Insert-tab text tools and Home-tab formatting controls.
Insert-tab text tools and Home-tab formatting controls.

Cell Borders

Font-formatting controls, including border and text emphasis options.
Font-formatting controls, including border and text emphasis options.

Custom Cell Border Styles

Styles group options for cell styles and conditional formatting.
Styles group options for cell styles and conditional formatting.

Conditional Formatting

Conditional Formatting menu and available rule options.
Conditional Formatting menu and available rule options.

Quick recap

  • Text formatting improves readability and presentation through font, size, alignment, colour and cell merging.
  • Use the Home-tab Font group to change text appearance.
  • The Borders menu applies, changes or removes borders; the button reapplies the most recently used style.
  • Custom cell styles can save border settings for reuse on selected cells.
  • Conditional formatting makes data easier to analyse by formatting cells according to criteria.
  • Data bars, colour scales and icon sets can help reveal values, exceptions and trends.
Lecture 20

Data Formatting Part III

Colour Scales

Conditional Formatting menu showing Colour Scales options.
Conditional Formatting menu showing Colour Scales options.

Creating a Three-Colour Scale

Minimum and Maximum Types
TypeHow it works / requirement
Lowest Value / Highest ValueUses the lowest and highest values in the range; no Minimum or Maximum value needs to be entered.
NumberUses specified numeric Minimum and Maximum values.
Per centEnter values from 0 to 100 without a percent sign. Use when the distribution of values is proportional and you want to visualise values proportionally.
PercentileEnter percentile values from 0 to 100. Useful for highlighting groups of high or extreme values that may skew the visualisation.
FormulaThe formula must start with an equal sign and return a number, date or time. Test it in the worksheet and ensure it does not return an error.
Three-colour scale applied to highlight values.
Three-colour scale applied to highlight values.
Worked example

How do you apply a three-colour scale to a selected range?

  1. Select the required cell range and open Manage Rules from Conditional Formatting.
  2. Click New Rule, choose Format all cells based on their values, and select 3-Colour Scale.
  3. Choose the required Minimum and Maximum types and values, then confirm the rule.
Answer: Use Conditional Formatting → Manage Rules → New Rule; select Format all cells based on their values, choose 3-Colour Scale, set the required types and values, and confirm.

Changing Text Alignment

Set Cell Alignment

  1. Select the cell range.
  2. Open the Alignment options from the Home tab and display the Format Cells dialog.
  3. On the Alignment tab, select the required horizontal and vertical alignment.
  4. Adjust text orientation or cell text-fit options if required, then click OK.
Format Cells dialog showing text alignment and orientation options.
Format Cells dialog showing text alignment and orientation options.
Worked example

How can you centre data horizontally and vertically in selected cells?

  1. Select the cells whose data needs alignment.
  2. Open Format Cells and select the Alignment tab.
  3. Choose the required horizontal and vertical alignment, then click OK.
Answer: Select the cells, open Format Cells → Alignment, choose centred horizontal and vertical alignment, and click OK.
Practice

A selected range contains formula errors, so its colour scale does not apply throughout. What should you do?

Hint: Consider functions that test for errors or replace error results.
Key step: Use IS or IFERROR to return a non-error value.
Solution: Use an IS or IFERROR function so the formulas return a value other than an error; then the conditional formatting can apply to the full range.
Practice

What values are allowed for a Percentile type, and when is it useful?

Hint: Recall the permitted range and its use for unusual high values.
Key step: Values range from 0 to 100; percentiles can help identify extreme values.
Solution: Enter a percentile from 0 to 100. It is useful for visualising a group of high or extreme values that may skew the data display.

Quick recap

  • Colour scales compare values through graduated colours; red may indicate lower values and green higher values.
  • Use Home → Conditional Formatting → Colour Scales to apply a two-colour scale.
  • Create or edit a three-colour rule through Manage Rules; choose Format all cells based on their values and 3-Colour Scale.
  • Minimum and Maximum types include Lowest/Highest Value, Number, Per cent, Percentile and Formula, each with its own input requirements.
  • Formula errors can prevent a colour scale applying to a whole range; use IS or IFERROR to return a non-error value.
  • Cell alignment settings control horizontal and vertical position, orientation and text fit; chart elements also offer direction, autofit, margins and columns.
Lecture 21

Data Formatting Part IV

Merging and Splitting Cells

Merge or split cells

  1. Select two or more adjacent cells to merge.
  2. On Home > Alignment, click Merge and Centre, or use its arrow to choose Merge Across or Merge Cells.
  3. To split cells, select the merged cell and click Merge and Centre again.
Worked example

How can a heading be centred across cells A1:D1 and then unmerged?

  1. To centre a heading over cells A1:D1, select A1:D1 and click Merge and Centre.
  2. The range becomes one cell, referenced as A1, with the heading centred.
  3. To restore the four cells, select the merged cell and click Merge and Centre again; the text returns to A1.
Answer: Select A1:D1 and choose Merge and Centre. Select the merged cell and click Merge and Centre again to split it.
Spreadsheet ribbon controls for cell alignment.
Spreadsheet ribbon controls for cell alignment.

Formatting a Table

Worked example

How do you quickly apply a predefined style to a selected data range?

  1. Select the sales-data range that you want to format as a table.
  2. Choose Home > Format as Table and select a style from Light, Medium or Dark.
  3. The chosen table style formats the selected data; table-element options can emphasize headings, totals, columns or alternating bands.
Answer: Select the range, choose Home > Format as Table, and select a Light, Medium or Dark style.
Sales data displayed with a selected table style.
Sales data displayed with a selected table style.

Using the NOW Function

Worked example

What does NOW() return, and what does the decimal part of its serial value represent?

  1. Enter =NOW() in a cell formatted as General.
  2. Excel returns the current date-and-time serial value and displays it as a date and time.
  3. In the serial value, the integer part represents the date and the decimal part represents the time.
Answer: NOW() returns the current date-and-time serial number; its decimal part represents the time.

Adding Headers and Footers

Add or change worksheet header or footer text

  1. Select the worksheet and choose Insert > Text > Header and Footer.
  2. Click the left, centre or right header/footer text box at the top or bottom of the page.
  3. Type new text, or select and replace existing text.
Worked example

How can you place a title at the top centre of printed worksheet pages?

  1. Select the worksheet and open Insert > Header and Footer.
  2. Click the centre header box and enter the spreadsheet title.
  3. The title will appear in the header when the worksheet is printed.
Answer: Open Insert > Header and Footer, select the centre header box, and type the title.
Page Setup options for headers and footers.
Page Setup options for headers and footers.

Format Painter

Worked example

How can newly added cells be made to match the formatting of existing cells?

  1. Select a cell that has the formatting you want to copy.
  2. Use Format Painter and apply it to the newly added cells.
  3. The target cells take on the source formatting.
Answer: Use Format Painter to copy the formatting from an existing cell or range to the new cells.

AutoFormat

Worked example

What is the purpose of AutoFormat?

  1. Choose an AutoFormat style from the available Excel 2007 options.
  2. The style applies its combined formatting features to the spreadsheet.
Answer: It quickly applies a preset combination of formatting features; Excel 2007 offers 17 styles.

Quick recap

  • Merge: A merged range uses the upper-left cell’s reference; Merge Across works across selected rows.
  • Split: Unmerging restores the cells and places the content in the upper-left cell.
  • Table styles: Choose Light, Medium or Dark styles; options can format headers, totals, columns and bands.
  • NOW(): Returns the current date-and-time serial value; its integer and decimal parts represent date and time.
  • Serial dates: January 1, 1900 = 1; January 1, 2008 = 39448; 0.5 represents noon.
  • Page and cell formatting: Headers/footers add print information, Format Painter copies formatting, and AutoFormat applies presets.
Lecture 22

Output Reports

Printing Worksheets and Workbooks

Definition

Output report — A printed or file-based copy of spreadsheet data, worksheets, workbooks, charts, or PivotTables.

What can be printed
Print optionPurpose
Worksheets or workbooksPrint all or selected parts; print one or several worksheets or workbooks.
Tables, charts, and PivotTablesPrint spreadsheet tables and visual or summary reports.
Workbook to a fileSave the workbook as a print output instead of sending it directly to a printer; useful when printing later on another printer.
Quick Print / Print PreviewQuick Print sends the output directly to print; Print Preview lets you check it before printing.
Spreadsheet Print menu with Print, Quick Print, and Print Preview options.
Spreadsheet Print menu with Print, Quick Print, and Print Preview options.

Defining the Print Area

Definition

Print area — The selected worksheet cell range that Excel is set to print; by default, Excel prints all data on the current worksheet.

Page Layout tab and Page Setup options for printing.
Page Layout tab and Page Setup options for printing.

Set a print area through Page Setup

  1. Open the Page Layout tab and choose Page Setup.
  2. In the Page Setup dialog box, open the Sheet tab.
  3. Enter the cell range in the Print Area box, or use Collapse Dialog to select the range and Restore Dialog to return.
  4. Click OK to define the print area.

Set, expand, or clear a print area

  1. Select the cells to print, such as A1:H10.
  2. Choose Page Layout → Print Area → Set Print Area.
  3. To include additional cells, select them and choose Print Area → Add to Print Area; repeat as needed.
  4. To remove the defined area, choose Print Area → Clear Print Area.
★ Remember
  • Print-area setting: It remains active until it is cleared or replaced.
  • Add to Print Area: Adds selected cells to an existing print area.

Printing Selected Cell Ranges

Print a selected range

  1. Select the cell range, or choose a named range from the Name Box list.
  2. Open Print using the Office Button menu or press Ctrl+P.
  3. In the Print what section, choose Selection, then confirm to print.

Print multiple non-contiguous ranges

  1. Select the first cell range.
  2. Hold Ctrl while selecting each additional range.
  3. Open Print using the Office Button menu or press Ctrl+P.
  4. Choose Selection in the Print what section and print; each selected range prints on a separate page.
⚠ Common mistake

Do not confuse a defined print area with printing a temporary selection: a print area stays set until changed or cleared, while Selection prints the ranges currently selected.

Quick recap

  • Excel can print entire or partial worksheets and workbooks, multiple sheets or workbooks, tables, charts, and PivotTables.
  • Quick Print sends output directly to print; Print Preview checks the layout first.
  • By default, Excel prints all data on the current worksheet; define a print area for a specific range.
  • A print area can be set through Page Setup or Page Layout → Print Area and expanded or cleared there.
  • Select a range and choose Selection in the Print dialog to print only that range.
  • Use Ctrl while selecting ranges to print multiple non-contiguous ranges; each prints on a separate page.
Lecture 23

Preparation Of Reports Using Pivot Table Part I

Pivot Table Reports and Data Tables

  • Data tables: What-if data tables show how changing one or two input values affects a formula’s result.
  • PivotTable reports: These summarise and arrange data from a list or table, allowing fields to be grouped and rearranged for different views.
  • Use: Both tools help analyse data and present results in a useful report format.

One-Variable Data Table

Budget Increase Formula
A2×C1A2 \times C1

Enter this spreadsheet formula in C4; each percentage is substituted into C1.

Set Up a Column-Oriented One-Variable Table

  1. Enter the formula `=A2*C1` in C4 and list the percentages in B5:B10.
  2. Select B4:C10, then choose Data > What-if Analysis > Data Table.
  3. Leave Row input cell blank and enter `CC1` as the Column input cell; click OK.
Spreadsheet setup for calculating budget values at different cost-increase percentages.
Spreadsheet setup for calculating budget values at different cost-increase percentages.
What-if table showing calculated budget increases for the listed percentages.
What-if table showing calculated budget increases for the listed percentages.

Two-Variable Data Table

Multiplication Table Formula
B1×C1B1 \times C1

Enter this spreadsheet formula in B2; the two input lists supply values to B1 and C1.

Set Up a Two-Variable Data Table

  1. Enter `=B1*C1` in B2.
  2. Enter one list down B3:B17 and the other across C3:Q3.
  3. Select B2:Q17 and choose Data > What-if Analysis > Data Table.
  4. Set Row input cell to `CC1` for the top-row values and Column input cell to `BB1` for the first-column values; click OK.
Spreadsheet setup and generated table for a two-variable multiplication table.
Spreadsheet setup and generated table for a two-variable multiplication table.
Worked example

What is the increase on a current budget of Rs. 1,55,000 when costs rise by 1.25%?

  1. A current budget is Rs. 1,55,000 and the cost-increase percentage is 1.25%.
  2. Substitute the percentage into C1 and multiply by the budget using `=A2*C1`.
    155000×0.0125=1937.50155000 \times 0.0125 = 1937.50
  3. The resulting budget increase is Rs. 1,937.50.
Answer: Rs. 1,937.50
Practice

For percentages listed down B5:B10, how should the one-variable budget table be set up?

Hint: The formula is in C4 and the variable input cell is C1.
Key step: Use column-oriented input settings.
Solution: Enter `=A2*C1` in C4, select B4:C10, leave Row input cell blank, and set Column input cell to `CC1`.
Practice

For a multiplication table with values across C3:Q3 and down B3:B17, which input cells should be assigned?

Hint: The top row uses the Row input cell; the first column uses the Column input cell.
Key step: The formula in B2 refers to B1 and C1.
Solution: Use `=B1*C1` in B2; set Row input cell to `CC1` and Column input cell to `BB1`.
⚠ Common mistake

Do not swap the two-variable input settings: values across the top row map to Row input cell `CC1`, while values down the first column map to Column input cell `BB1`.

Quick recap

  • Pivot Table reports organise list data into readable reports using fields, details, calculations and groups.
  • A one-variable data table substitutes a series of values into one input cell; values can be arranged in a row or column.
  • For the budget table, enter `=A2*C1` and use `CC1` as the Column input cell for percentages listed vertically.
  • A two-variable data table uses one formula and two input lists mapped to two distinct input cells.
  • With values across C3:Q3 and down B3:B17, use `CC1` as Row input cell and `BB1` as Column input cell.
Lecture 24

Preparation Of Reports Using Pivot Table Part II

Pivot Tables

Surplus calculation
S=A−QS=A-Q

Use this to compare actual consumption with the fixed quota.

Creating and arranging a PivotTable

Create and set up the report

  1. Select the complete source data range, A1:E37A1:E37.
    A1:E37A1:E37
  2. On the Insert tab, choose PivotTable in the Tables group to open the report-creation options.
  3. Choose the data location and select Existing Worksheet; enter a destination such as G19G19.
    G19G19
  4. Click OK to create a blank PivotTable and display the field list.
  5. Drag fields into the report areas: Day to Report Filter, Vegetable to Column Labels, City to Row Labels, and Actual to Values.
  6. Use Sum of Actual to aggregate consumption. To change the view, rearrange the fields; for example, place City and then Vegetable in Row Labels and leave Column Labels empty.
Insert tab option for creating a PivotTable.
Insert tab option for creating a PivotTable.
Dialog for selecting the source data and where to place the PivotTable.
Dialog for selecting the source data and where to place the PivotTable.
PivotTable field list and report areas for filters, columns, rows and values.
PivotTable field list and report areas for filters, columns, rows and values.
Worked example

Prepare a report of actual vegetable consumption by city and vegetable, with Day as a filter.

  1. Select the consumption data for the three vegetables, four cities and three days: A1:E37A1:E37.
    A1:E37A1:E37
  2. Insert a PivotTable and choose Existing Worksheet, placing it at G19G19.
    G19G19
  3. Set Day as Report Filter, Vegetable as Column Labels, City as Row Labels and Actual as Values, summarized by Sum.
  4. The resulting report shows summed actual consumption by city and vegetable, and allows the user to filter by day.
Answer: Use Sum of Actual in Values, City in Row Labels, Vegetable in Column Labels and Day in Report Filter.
Two PivotTable layouts showing actual vegetable consumption by city, including totals.
Two PivotTable layouts showing actual vegetable consumption by city, including totals.

Advantages and uses of PivotTable reports

  • Summarize: Query large datasets, aggregate numerical data, calculate subtotals and summarize by categories or subcategories.
  • Analyse: Create custom calculations and formulas, and compare related totals or several facts across a long list of figures.
  • Expand or collapse: Move between detailed data and summary levels to focus on information of interest.
  • Rearrange: Pivot rows into columns or columns into rows to view different summaries of the same source data.
  • Filter and organize: Filter, sort, group and conditionally format useful subsets of data.
  • Present: Produce concise, attractive and annotated reports for on-screen use or printing.
PivotTable Tools toolbar.
PivotTable Tools toolbar.
Practice

What report layout results from placing City and then Vegetable in Row Labels, with no field in Column Labels?

Hint: The position of each field determines how the summary is arranged.
Key step: Place Vegetable beneath City in the row arrangement.
Solution: Vegetables are listed under each city in the rows; there are no separate vegetable columns.
Practice

Which report areas summarize actual consumption by city while allowing one day to be viewed at a time?

Hint: Use one area for the grouping, one for the numeric summary and one for filtering.
Key step: Put City in Row Labels, Actual in Values and Day in Report Filter.
Solution: Set City as Row Labels, Actual as Values summarized by Sum, and Day as Report Filter.

Quick recap

  • A PivotTable interactively summarizes large amounts of spreadsheet data.
  • Report Filter, Column Labels, Row Labels and Values determine the report layout.
  • The example summarizes Sum of Actual by city and vegetable, with Day as a filter.
  • Rearranging or filtering fields creates different views of the same source data.
  • PivotTables support aggregation, subtotals, sorting, grouping and comparisons.
  • Surplus is actual consumption minus quota: S=A−QS=A-Q.
Lecture 25

Common Errors (Messages) In Spreadsheet Part I

Checking and marking formula errors

Formula-auditing tools include Trace Precedents, Trace Dependents and Error Checking.
Formula-auditing tools include Trace Precedents, Trace Dependents and Error Checking.

Correcting common error values

  • Error checking: Excel can mark possible formula errors and provide an Error Checking tool to review them.
  • Diagnosis: Identify the error value and check the formula, its references and the data used in its arguments.
  • Correction: Correct the cause of the error or adjust the formula so it handles the relevant conditions appropriately.

The ##### display

⚠ Common mistake

Do not assume ##### always means a formula is wrong: first check column width and number format, then check for a negative date or time.

The #DIV/0! error

Preventing division-by-zero display
IF⁡(B5=0,"",A5/B5)\operatorname{IF}(B5=0,\text{""},A5/B5)

For a calculation using cells A5 and B5, return a blank result if B5 is zero.

Worked example

How can a formula avoid displaying #DIV/0! when B5 is zero?

  1. Suppose A5 contains the amount and B5 contains zero; the direct division formula produces a division-by-zero error.
    =A5/B5=A5/B5
  2. Check whether B5 is zero before carrying out the division.
    IF⁡(B5=0,"",A5/B5)\operatorname{IF}(B5=0,\text{""},A5/B5)
  3. The formula returns a blank string when B5 is zero; otherwise it calculates A5 divided by B5.
Answer: Use the IF formula shown above; it displays a blank when B5 is zero and performs the division otherwise.

The #N/A error

  • Meaning: #N/A appears when a value required by a formula or function is unavailable.
  • Common causes: A lookup may use an inappropriate value or an unsorted table; required data may also be missing.
  • Check: Verify the lookup value, table arrangement and function arguments.

The #NAME? error

Practice

A cell displays #####. Give two possible causes and one correction for each.

Hint: Check both the cell width and the sign/format of a date or time.
Key step: Distinguish a display-width problem from a negative date/time result.
Solution: The column may be too narrow: AutoFit the column or use Shrink to fit. Alternatively, the result may be a negative date/time: check the subtraction and display the result with a non-date/time format if appropriate.
Practice

A formula divides A5 by B5, and B5 is blank. Why does an error appear, and how can it be prevented?

Hint: Treat a blank divisor like zero and test it with IF.
Key step: Check B5 before carrying out the division.
Solution: A blank divisor causes #DIV/0!. Use IF⁡(B5=0,"",A5/B5)\operatorname{IF}(B5=0,\text{""},A5/B5) to show a blank instead of the error.
Practice

A lookup formula returns #N/A. Name two possible causes.

Hint: Check the lookup value, table order and any array dimensions.
Key step: Verify that the lookup value and lookup range meet the function’s requirements.
Solution: Possible causes include a missing or inappropriate lookup value, an unsorted table where sorting is required, or an array argument with mismatched row or column dimensions.

Quick recap

  • Background checking marks possible formula errors; use Error Checking to review or ignore them.
  • ##### usually indicates insufficient column width; it may also signal a negative date/time.
  • #DIV/0! results from a zero or blank divisor; correct the divisor or test it with IF.
  • #N/A means a required value is unavailable; check data, lookup arguments, sorting and array dimensions.
  • #NAME? means Excel does not recognise formula text; a missing required add-in can be the cause.
Lecture 26

Common Errors (Messages) In Spreadsheet Part II

#NULL! Error

  • Meaning: #NULL! occurs when a formula specifies the intersection of two ranges that do not intersect.
  • Range operators: Use a colon for a continuous range, such as A1:A10, and a comma to combine separate ranges.
  • Check: Review the formula’s references and range operators to ensure they describe the intended cells.

#NUM! Error

  • Meaning: #NUM! indicates an invalid numeric value or an unsuccessful numerical calculation.
  • Common causes: A function may receive an unacceptable numeric argument, or an iterative function such as RATE may fail to find a result.
  • Correction: Check the arguments and, for an iterative function, try a different starting estimate.

#REF! Error

  • Meaning: #REF! indicates that a formula contains an invalid cell reference.
  • Common cause: Deleting referenced cells or pasting moved cells over them can invalidate formulas.
  • Correction: Restore the affected cells if possible, or edit the formula to use a valid reference.

#VALUE! Error

Worked example

Why does =A5+A6=A5+A6 return #VALUE! in this case, and how can it be fixed?

  1. A5 contains a number and A6 contains the text “Not available.” The formula adds both cells.
  2. The text is not a valid numeric operand, so the formula returns #VALUE!.
  3. Replace the invalid text with a number, or change the formula to use valid numeric cells.
Answer: A6 contains text where the formula needs a number. Correct A6 or use a valid numeric reference.
Practice

A formula uses SUM(A1:A10 C1:C10)\text{SUM}(A1:A10\ C1:C10), but the ranges do not intersect. What error may appear, and how should the formula be corrected to sum both ranges?

Hint: A space means intersection; a comma joins separate areas.
Key step: Replace the intersection operator with the union operator.
Solution: It may return #NULL!. Use SUM(A1:A10,C1:C10)\text{SUM}(A1:A10,C1:C10).
Practice

A formula returns #REF! immediately after cells it referred to were deleted. Give one way to correct the problem.

Hint: The formula's reference is no longer valid.
Key step: Restore the cells if possible, or edit the formula to use valid references.
Solution: Undo the deletion immediately if possible; otherwise, correct the formula's cell references.

Quick recap

  • #NULL! usually signals an incorrect intersection; use a colon for a continuous range and a comma for separate ranges.
  • #NUM! indicates an invalid numeric value, an unsuccessful iterative function, or a number outside Excel's range.
  • #REF! means a formula's cell reference is invalid, often because cells were deleted or overwritten.
  • #VALUE! means a formula has received an argument or operand of the wrong type.
  • Array formulas in legacy Excel are confirmed with Ctrl+Shift+Enter when required.
Lecture 27

Summary

Spreadsheet Structure and Cells

  • Worksheet: Spreadsheet information is organised in a grid of horizontal rows and vertical columns.
  • Cell: The intersection of a row and column forms a cell, identified by its column letter and row number, such as B4.
  • Workbook: A workbook can contain multiple worksheets, which provide separate grids within one file.

Formulas, Cell References and Ranges

R1C1 reference style

A reference uses RR followed by a row number and CC followed by a column number. A relative row or column number is enclosed in square brackets; some spreadsheets offer this style as a compatibility option.

Definition

Cell range — A cell range is a group of cells specified by its starting and ending references, such as A1:A6A1:A6.

Definition

Function — A function is a named instruction that processes the argument or arguments supplied in brackets and returns a result.

Add the values in a range
=SUM⁡(A1:A6)=\operatorname{SUM}(A1:A6)

Use the SUM function to add all cells from the first reference through the last reference.

Worked example

What result does the formula produce?

  1. Suppose cells A1A1, A2A2 and A3A3 contain 44, 66 and 33. Enter the SUM formula in another cell.
    =SUM⁡(A1:A3)=\operatorname{SUM}(A1:A3)
  2. The formula adds the three values in the range.
    4+6+3=134+6+3=13
Answer: 1313

Formatting a Spreadsheet

  • Purpose: Formatting makes spreadsheet information easier to read and understand.
  • Options: Number, text and cell formatting can change how values and labels are displayed.
  • Display: Formatting changes a cell’s appearance without changing the value stored in it.

Printing a Spreadsheet

  • Print options: Excel can print a whole or partial worksheet, selected ranges, multiple sheets or workbooks, and charts or PivotTable reports.
  • Print area: Define the cells to print using the Page Setup dialog box or the Print Area command; the defined area remains until cleared or replaced.
  • Preview: Use Print Preview to check the output before printing, or choose Quick Print to print directly.

Saving a Spreadsheet

Practice

Cells B1B1, B2B2 and B3B3 contain 55, 22 and 88. What does =SUM⁡(B1:B3)=\operatorname{SUM}(B1:B3) return?

Hint: Add each value in the specified range.
Key step: 5+2+85+2+8
Solution: 5+2+8=155+2+8=15; the formula returns 1515.
Practice

When a formula is copied to another cell, which parts adjust in $A1\$A1, A$1A\$1 and $A$1\$A\$1?

Hint: A dollar sign marks the column or row that must remain fixed.
Key step: Check whether the column, row, or both are marked with a dollar sign.
Solution: $A1\$A1: column fixed, row adjusts; A$1A\$1: column adjusts, row fixed; $A$1\$A\$1: both fixed.

Quick recap

  • A spreadsheet uses rows, columns and cells; a workbook can contain multiple named sheets.
  • Cells may hold values, formulas, functions or nothing; formulas and functions conventionally begin with ==.
  • References may be relative, absolute or mixed; a dollar sign marks the part that stays fixed when copied.
  • A range such as A1:A6A1:A6 identifies a group of cells; SUM adds the values in that range.
  • Formatting changes how contents appear, not the underlying cell contents.
  • Page setup controls print orientation; save work regularly and specify a destination and file name.

Formula sheet

Value from Quantity and Price
V=Q×PV=Q\times P
General function form
=FunctionName⁡(argument)=\operatorname{FunctionName}(\text{argument})
SUM using a named range
=SUM⁡(Numbers)=\operatorname{SUM}(\text{Numbers})
SUMIF with a condition
=SUMIF⁡(Numbers,"<6")=\operatorname{SUMIF}(\text{Numbers},\text{"<6"})
IF syntax
IF(logical_test,value_if_true,value_if_false)\mathrm{IF}(logical\_test,value\_if\_true,value\_if\_false)
Nested IF example: test grades
IF(E2<96,IF(E2<91,IF(E2<55,"Fail","C Grade"),"B Grade"),"A Grade")\mathrm{IF}(E2<96,\mathrm{IF}(E2<91,\mathrm{IF}(E2<55,\text{"Fail"},\text{"C Grade"}),\text{"B Grade"}),\text{"A Grade"})
Extracting date parts and estimating age
Age=Dtoday−Dbirth365.25\text{Age} = \frac{D_{\text{today}}-D_{\text{birth}}}{365.25}
SUMIF syntax
SUMIF⁡(r,c,s)\operatorname{SUMIF}(r,c,s)
ROUND, ROUNDUP and ROUNDDOWN
ROUND⁡(n,d);ROUNDUP⁡(n,d);ROUNDDOWN⁡(n,d)\operatorname{ROUND}(n,d);\quad \operatorname{ROUNDUP}(n,d);\quad \operatorname{ROUNDDOWN}(n,d)
Syntax
=CONCATENATE(text1,text2,…)\texttt{=CONCATENATE(text1,text2,\ldots)}
Syntax
=AND(logical1,logical2,…,logical255)\texttt{=AND(logical1,logical2,\ldots,logical255)}
Syntax
=OR(logical1,logical2,…,logical255)\texttt{=OR(logical1,logical2,\ldots,logical255)}
Vector syntax
=LOOKUP(lookup_value,lookup_vector,result_vector)\texttt{=LOOKUP(lookup\_value,lookup\_vector,result\_vector)}
LOOKUP syntax
LOOKUP⁡(v,A)\operatorname{LOOKUP}(v,A)
VLOOKUP syntax
VLOOKUP⁡(v,T,c,r)\operatorname{VLOOKUP}(v,T,c,r)
Price calculation examples
21.45(1+0.35)=28.9575≈28.96,5.12(1+0.40)(1−0.20)=5.7344≈5.7321.45(1+0.35)=28.9575\approx 28.96,\qquad 5.12(1+0.40)(1-0.20)=5.7344\approx 5.73
HLOOKUP syntax
HLOOKUP⁡(lookup_value,table_array,row_index_num,range_lookup)\operatorname{HLOOKUP}(lookup\_value,table\_array,row\_index\_num,range\_lookup)
ACCRINT syntax
ACCRINT⁡(issue,first_interest,settlement,rate,par,frequency,basis,calc_method)\operatorname{ACCRINT}(issue,first\_interest,settlement,rate,par,frequency,basis,calc\_method)
Accrued-interest calculation
ACCRINT=par×ratefrequency×∑i=1NCAiNLi\mathrm{ACCRINT}=\mathrm{par}\times\frac{\mathrm{rate}}{\mathrm{frequency}}\times\sum_{i=1}^{\mathrm{NC}}\frac{A_i}{\mathrm{NL}_i}
CUMIPMT syntax
CUMIPMT⁡(rate,nper,pv,start_period,end_period,type)\operatorname{CUMIPMT}(rate,nper,pv,start\_period,end\_period,type)
PV function syntax
PV⁡(rate,nper,pmt,[fv],[type])\operatorname{PV}(rate,nper,pmt,[fv],[type])
PV relationship when the rate is zero
pmt⋅nper+PV+fv=0pmt\cdot nper+PV+fv=0
FV function syntax
FV⁡(rate,nper,pmt,[pv],[type])\operatorname{FV}(rate,nper,pmt,[pv],[type])
FV relationship when the rate is zero
FV=−(PV+pmt⋅nper)FV=-(PV+pmt\cdot nper)
PMT syntax
PMT⁡(rate,nper,pv,[fv],[type])\operatorname{PMT}(rate,nper,pv,[fv],[type])
RATE syntax
RATE⁡(nper,pmt,pv,[fv],[type],[guess])\operatorname{RATE}(nper,pmt,pv,[fv],[type],[guess])
NPV syntax and formula
NPV⁡(rate,value1,…,valuen)=∑i=1nvaluei(1+rate)i\operatorname{NPV}(rate,value_1,\ldots,value_n)=\sum_{i=1}^{n}\frac{value_i}{(1+rate)^i}
Linear Series
an=a1+(n−1)da_n=a_1+(n-1)d
Useful custom validation formulas
=COUNTIF(\B\2:\B\100,B2)=1=SUM(\C\2:\C\10)<=\F\1=B2=TRIM(B2)=AND(WEEKDAY(A2)<>1,WEEKDAY(A2)<>7)\begin{aligned}\texttt{=COUNTIF(\B\2:\B\100,B2)=1}\\\texttt{=SUM(\C\2:\C\10)<=\F\1}\\\texttt{=B2=TRIM(B2)}\\\texttt{=AND(WEEKDAY(A2)<>1,WEEKDAY(A2)<>7)}\end{aligned}
Budget Increase Formula
A2×C1A2 \times C1
Multiplication Table Formula
B1×C1B1 \times C1
Surplus calculation
S=A−QS=A-Q
Preventing division-by-zero display
IF⁡(B5=0,"",A5/B5)\operatorname{IF}(B5=0,\text{""},A5/B5)
Add the values in a range
=SUM⁡(A1:A6)=\operatorname{SUM}(A1:A6)

Accountancy — ALL CHAPTERS

1

Accounting for Share Capital

View

2

Issue and Redemption of Debentures

View

3

Financial Statements of a Company

View

4

Analysis of Financial Statements

View

5

Accounting Ratios

View

6

Cash Flow Statement

View

1

Overview Of Computerised Accounting System

View

2

Spreadsheet

View

3

Use Of Spreadsheet In Business Applications

View

4

Graphs And Charts For Business Data

View

1

Accounting for Partnership: Basic Concepts

View

2

Reconstitution of a Partnership Firm –Admission of a Partner

View

3

Reconstitution of a Partnership Firm –Retirement/Death of a Partner

View

4

Dissolution of Partnership Firm

View