Computer Application in Business Unit 3 Spreadsheet And It’s Business Application, College and University Answer Bank for B.com, M.Com B.sc, and Post Graduate Notes and Guide Available here, Computer Application in Business Unit 3 Spreadsheet And It’s Business Application to each Unit are provided in the list of UG-CBCS Central University & State University Syllabus so that you can easily browse through different College and University Guide and Notes here. Computer Application in Business Unit 3 Spreadsheet And It’s Business Application can be of great value to excel in the examination.
Computer Application in Business Unit 3 Spreadsheet And It’s Business Application
Computer Application in Business Unit 3 Spreadsheet And It’s Business Application Notes cover all the exercise questions in UGC Syllabus. Computer Application in Business Unit 3 Spreadsheet And It’s Business Application Solutions provided here ensures a smooth and easy understanding of all the concepts. Understand the concepts behind every Unit and score well in the board exams.
Spreadsheet And It’s Business Application
COMPUTER APPLICATION IN BUSINESS
| VERY SHORT TYPES QUESTION & ANSWERS |
1. What is a spreadsheet?
Ans: A spreadsheet is an electronic document just like an expense sheet. It provides much more flexibility, speed and accuracy, as compared to easy manual expense sheet. It has many built in functions which make it to use.
2. What are the tasks that a spreadsheet can do?
Ans: Spreadsheets are used for a variety of tasks such as:
(a) Maintaining records, Analyzing Data.
(b) Generating Graphs and reports.
(c) Doing financial calculations etc.
3. Name some popular spreadsheet softwares?
Ans: MS Excel, Open Office – Calc, Google documents.
4. What are the steps to start MS-Excel?
Ans: Steps to start MS-Excel are:
(a) Double Click on a shortcut key of the MS-Excel icon, if available, on the desktop.
OR
(b) Click Start → All Programs → Microsoft Office → MS Excel.
5. What are different components of a spreadsheet?
Ans: Some of the key components of a spreadsheet are:
(a) Worksheet: It is grid of horizontal rows and vertical columns.
(b) Workbook: A workbook contains one more worksheets.
(c) Row: A row is horizontal arrangement of cells. The rows are named by numbers (1,2,3,4,…..).
(d) Column: A column is a vertical arrangement of cells. The columns are named by alphabets (A, B, C, ….Y, Z, AA, AB, AC, …AZ, BA, BB,…).
(e) Cell: A cell is where the rows and columns intersect. Worksheet is also called an array of cells. A cell may contain text, numbers, date or a formula.
A cell address in a spreadsheet identifies location of the cell. It is a combination of column name and row number of the cell, such as A2 or B16 etc.
(f) Active cell: This is the cell on which the cursor is currently placed. It is outlined by a dark border. Data is always entered in the active cell.
(g) Formula Bar: This is located below the Ribbon. It displays the contents of the active cell. It can also be used to enter and edit data.
(h) Scroll Bar: These helps to scroll through the content and body of the worksheet. There are two scroll bars – horizontal and vertical.
6. Write steps to save a spreadsheet.
Ans: (a) Click File → Save.
(b) A Save As dialog box appears. Choose the directory(drive & folder) in which you want to save the workbook.
(c) Click Save.
7. Write steps to close the spreadsheet.
Ans: (a) Click File → Close.
OR
Click on the cross symbol “X”, that you see on the top right hand corner of the screen.
8. What are the different kinds of data that can be entered in the spreadsheet.
Ans: The different kinds of data that can be entered are:
(a) Text: a to z or A to Z characters.
(b) Numbers: 0 to 9 (all numeric and decimal point numbers).
(c) Date: date type data such as 12th December 2015, 12/12/2015, 4- 9-16 etc.
(d) Formula: you can enter formulas also such as =20+30, =sum/5 etc. All formulas started with “=” sign.
9. What is the default alignment of Text and Number data in a cell?
Ans: The Text data is aligned to left in a cell and the Number data/Date data is aligned to right.
10. What are the two ways to do calculation in a spreadsheet?
Ans: The two ways to do calculation are:
(a) By manual entry of the formulas.
(b) By using built-in functions.
11. Write steps to insert a row/ a column in a spreadsheet.
Ans: Steps to insert a row/ a column in a spreadsheet are:
(a) Select the row or the column (by clicking on the row header or column header) before which you want to insert a row or a column.
(b) Right click the mouse, and click on Insert row or Insert column.
12. What are the different options to format cell and its contents?
Ans: The different options to format the cell are as follows:
(a) Wrap Text: When the length of the text does not fit completely in the cell as the cell’s width is smaller than the size of the text. The alternate is to automatically make the text appear on multiple lines in a cell using the Wrap Text Option.
Steps: Click on the cell and Click on Home tab – Wrap Text option in the Alignment group.
(b) Change Font: Change font means to change the appearance of the text, like making the text Bold, Underline and Italics.
Steps: Click on the cell and choose the appropriate icon B, I, U under Font Group from the Home tab.
(c) Change Column Width: When the text does not fit into the width of the column, the width of the column can be increase to ensure that the entire text is visible.
Steps: Take the cursor to the column numbers on top. Position it on the line between two columns. The shape of the cursor changes to a + symbol. Click the left button and drag the + symbol to the right. Once you received the desired width release the left button of the mouse.
(d) Cell Content Alignment: Alignment means to arrange the position of the text in the cell.
Steps: Under the Home tab, in the Alignment group, click on the appropriate symbol for Left, Centre or Right Alignment of text.
(e) Create a Border: Border means to have a boundary around the text.
Steps: Select the cell, Under Home tab, in the Font group, click on down arrow next to the icon for borders. A list of borders appears. From this list select the appropriate option for Left Border, Right Border, Top Border, Bottom Border, All Borders etc.
13. What is Quick Access Toolbar? Where it is located?
Ans: Quick Access Toolbar contains buttons to quickly access the commands to complete a task. It is located on the right side of the Office Button.
14. By default, how many buttons are equipped in the Quick Access Toolbar? Can we add/remove buttons or options in the Quick Access Toolbar?
Ans: By default, the Quick Access toolbar is equipped with three buttons: Save, Undo and Redo. We can add or remove buttons or options that we want in the Quick Access Toolbar.
15. Write steps to add more buttons/options in the Quick Access Toolbar.
Ans: The steps to add more buttons are:
(a) Right-click the Quick Access Toolbar and Click Customize Quick Access Toolbar.
(b) The Excel Options dialog box will appear. On the left side of Add, click an option and click Add.
(c) After making the selections, click OK.
16. Write steps to remove a button from the Quick Access toolbar.
Ans: (a) Right click on the button that you want to remove from the Quick Access toolbar.
(b) Click on Remove From Quick Access toolbar.
17. In how many ways a currency symbol can be inserted. Explain?
Ans: Currency symbols can be inserted in two ways.
(i) Using the Accounting Number Format button.
Steps:
(a) Select the cell.
(b) Click on the down arrow next to the Accounting Number Format button which is available under the Home tab, in the Number group.
(c) A list appears showing some of the popular currency symbols. Select Rs. Option.
(ii) Using the Right Click Option.
Steps:
(a) Select the cell.
(b) Right click the mouse. A menu appears.
(c) Click on the option Format Cells. A dialogue box appears.
(d) From the Category list, choose Currency.
(e) Now click on the symbol box. Available currency symbol are displayed. Select Rs. Option.
18. In how many ways you can format the cell contents? Explain.
Ans: The cells can be formatted in two ways.
(i) Using the Icons on the Ribbon.
Steps:
(a) Click on the font box available under Font group in Home tab. A list appears.
(b) Choose desired font.
(ii) Use Short cut menu and the dialog box.
Steps:
(a) Select the cell.
(b) Right click the mouse button, A short menu appears.
(c) Click on Format Cells… A dialogue box appears with the Number tab selected by default.
(d) Click on the Font tab.
(e) Choose the desired font.
19. Write steps to delete a row/ a column in a spreadsheet.
Ans: Steps to insert a row/ a column in a spreadsheet are:
(a) Select the row or the column (by clicking on the row header or column header) that you want to delete.
(b) Right click the mouse, and click on delete row or delete column.
20. Write steps to check spelling mistakes in a spreadsheet.
Ans: Steps to check spelling mistakes are:
(a) Click on Spelling option under Proofing tab in the Review tab.
(b) Spreadsheet software will start checking the spellings. If it finds a spelling mistake, it will show the Spelling: English dialog box.
(c) You can accept the suggestion by clicking on the Change option.
(d) Or, you can type in the correct spelling in the box on top in the dialog box.
(e) Once you have corrected the mistake, it will continue checking spellings in the rest of the worksheet.
21. Write steps to apply borders to the cells.
Ans: The steps to apply borders to the cells are:
(a) Select the cells.
(b) Click on the down arrow on the left of borders under Font group in the Home tab.
(c) A list of options for borders appears. Select one of the options of borders.
(d) The selected options of border will be applied to the cells.
Or
(a) Select the cells on which you want to apply the Border.
(b) Right click, a list of options will be displayed. Click on Format Cells option.
(c) Choose Border tab and select the appropriate border.
(d) Click on OK button.
22. Write steps to color the cells.
Ans: The steps to color the cells are:
(a) Select the cells.
(b) Click on the down arrow on the right of Fill Color option under Font group in the Home Tab.
(c) A list of color options will appear. Select a color.
(d) The selected color will be applied to the cells.
23. Write steps to rename a worksheet.
Ans: The steps to rename a worksheet are:
(a) Right click a worksheet tab, a list of options will be displayed.
(b) Click on Rename option. Type the new name.
(c) Press Enter Key.
Or
(a) Double click the worksheet tab. Type the new name. Press Enter Key.
24. Write steps to add a worksheet.
Ans: The steps to add a worksheet are:
(a) Click the Insert Worksheet symbol, that is present at the end of worksheets. When you click it, a new sheet will be added.
Or
Press Shift and F11 keys together, a new sheet will be added.
25. Write steps to remove a worksheet.
Ans: The steps to remove a worksheet are:
(a) Right click on the worksheet that you want to delete. A short-cut menu pops up.
(b) Select the Delete option. Selected sheet will get deleted.
26. Write steps to print a worksheet with default settings.
Ans: (a) Select File → Print option.
(b) The worksheet will be printed.
27. Write steps to print a worksheet using Page Layout.
Ans: (i) Click on Page Layout Tab, adjust the following setting as desired by you.
(a) Margins: Change the margin settings for Top, Bottom, Left and Right.
(b) Orientation: Change the orientation Landscape or Portrait.
(c) Size: Change the size of Page to be used for printing, Eg. A4, Legal etc.
(d) Print Area: If you do not want to print the complete worksheet, select the range of cells that you want to print. Then Click on Print Area option and click on Set Print Area.
(e) Page Setup: Click on the bottom right hand corner of the Page Setup group in the Page Layout tab. A Page setup dialog box will appear. Select the appropriate options for setting.
(ii) Click on File → Print.
28. What is a spreadsheet?
Ans: A spreadsheet is a collection of data and information organized in the form of rows and columns.
29. What is a range of cells?
Ans: A group of adjacent cells is known as a range of cells.
30. Define an active cell.
Ans: A cell with a dark boundary is known as active cell.
Can you insert a new worksheet in a workbook? If yes, tell the keyboard shortcut to insert it.
Yes a new worksheet can be inserted in a workbook.
The shortcut key to insert it is Shift + F11 key.
31. What are the steps to change a decimal number into the scientific format?
Ans: Steps to change a decimal number into the scientific format are:
(a) Right click on the cell.
(b) Click on Format Cells option. The Format Cells dialog box appears.
(c) Select the Scientific option under Category.
32. What is a function? Give four examples.
Ans: A function is a pre-defined formula in Excel.
Four examples of functions are SUM, AVERAGE, MAX and MIN.
33. How do you enter the data into an active cell using the formula bar?
Ans: We enter data into an active cell using the formula bar by clicking in the formula bar and then typing the data.
34. What do you do if you want to edit an existing entry of cell?
Ans: To edit an existing entry of cell select the cell and press F2 key. Then move the cursor to the required position and correct it. Finally press the Enter key.
35. Tell the keyboard shortcut to open a cell in edit mode.
Ans: The keyboard shortcut to open a cell in edit mode is F2 key.
36. What happens when you close the Excel application without saving it?
Ans: When we close the Excel application without saving it will notify us that the current file is not saved and ask us if we want to save the file with options Yes, No and Cancel.
37. How many types of data can be entered in the cells?
Ans: Three types of data can be entered in the cells. They are Numbers or Date, Formula and Text.
38. How can we merge cells in MS Excel?
Ans: To merge cells together:
(a) We first select the cells to be merged.
(b) From the Alignment group under the Home tabs click merge and center which shows the drop down menu.
(c) The drop-down menu has four options-
(i) Merge and center merges the selected cells and center-aligns the contents.
(ii) Merge Across merges the selected cells across.
(iii) Merge Cells simply merges the cells keeping intact the previous alignment of the content.
(iv) Unmerge the merged cells in the original layout.
Cells can also be merged from the Format cells dialog box by following these steps:
(i) We first select the cells to be merged together.
(ii) Then we follow any one of the following steps to open the Format Cells dialog box.
(iii) From the Format Cells dialog box, we select the check box for merge cells under the Alignment tabs and press ok.
39. What are Formulas in Ms Excel? Illustrate their use using an example.
Ans: Formulas in excel are equations that can perform calculations. When creating formulas in MS Excel, we always start by typing the equal sign.
Writing formulas in a proper way gives us the correct result as the answer. When doing so it is best if we first enter all the data in the spreadsheet before us begging creating formulas.
Examples to add two numbers: Suppose we are to add two numbers located in two different cells A1 and A2. We choose the cell AS as the result cell where the formula has to be displayed. We follow these steps-
(a) We enter the two numbers to be added in cells Aland A2.
(b) Then select cell A5 and write any one of the following
= SUM (A1, A2)
Or
= SUM (A1+A2)
(c) Then press enter, the result of addition of the numbers in cells Aland A2 is displayed in cell AS.
40. What are charts in MS Excel? Mention any two charts types along with their use.
Ans: Charts: Charts are used in Excel to provide graphical representation of numerical data in a way that corresponding to the relationship among the numerical values.
(a) Column charts: Data that is arranged in columns or rows on a worksheet can be plotted in a column chart. Column Charts are useful for showing data changes over a period of time or for illustrating comparisons among items. In column charts, categories are typically organizes along the horizontal axis and values along the vertical axis.
(b) Line Charts: Data that is arranged in columns or rows on a worksheet can be plotted in a ling chart. Line Charts can display continuous data over time set against a common scale, and are therefore ideal for showing trends in data at equal intervals. In a line chart, category data is distributed evenly along the horizontal axis, and all value data is distributed evenly along the vertical axis.
41. What are Area charts in MS Excel? Give an Example.
Ans: Area Charts: An area chart emphasizes the magnitude of change over time. By displaying the sum of plotted values, an area chart also shows the relationship of pasts to a whole. An area chart is a line chart with the area below the lines filled with colors. Use a stacked area chart to display the contribution of each value to a total over time.
For example: To create area chart, execute the following steps-
(a) Select the range A1:D7.
(b) On the Insert tab, in the charts group, choose area and select Area.
42. Illustrate the use of line charts using some sample data.
Ans: A line chart shows trends in data at equal intervals line charts are useful for depicting the change.
For example sales Report of 5 sales person in two years
Sales Report
| Sales person | 2009 | 2010 |
| Akhilesh | 15433 | 16578 |
| Anil | 12465 | 14356 |
| Sahil | 11009 | 14453 |
| Monoj | 10034 | 14533 |
| Aman | 12332 | 14322 |
43. Define the Text type of data.
Ans: Text type of data included a combination of letters, numbers and special characters.
What are the steps to change a date format using Format Cells dialog box?
The steps to change a data format using Format Cells dialog box are as follows:
(a) Click on Date under Category box.
(b) Select the type of date format from the type box.
Describe the following charts:
(a) Pie chart.
(b) Line Chart.
(c) Column Chart.
(a) Pie chart shows the parts of the whole items that make up a data series to the sum of the items.
(b) Line Chart shows trends in data at equal intervals. It is helpful for depicting the change in value over a period of time.
(c) Column Chart shows data changes over a period of time or defines comparisons among individual items.
44. What is the difference between category axis and value axis?
Ans: The X-axis or horizontal axis is called Category axis while Y-axis or vertical axis is called Value axis.
45. What is the chart area and the plot area in a chart?
Ans: The total region of the chart in which the chart is defined is called Chart Area while the area of the chart in which the data is plotted is called Plot Area.
46. How to Creating Functions in MS excel?
Ans: Spreadsheets come with many built formulas, called functions that perform specialized calculations automatically. We can include these functions in our own formulas. Some functions are quite simple, such as the COUNT function (to count the number of values in a range of cells). Many functions, however are very complex.
To insert a function you have to follow the following steps.
(a) Click on the cell in which you want to insert the function.
(b) Select the option Function from insert menu.
(c) Above dialog box will appear.
(d) Select the desired function and click on OK button.
Using Various Functions in MS-Excel: The leading spreadsheets come with hundreds of these functions. Here we are discussing some of the most commonly used ones with the help of examples:
(a) Date and Time Functions.
(i) DATE: Represents the date number that represents a particular date.
Syntax: DATE (year, month, day)
Argument Type: Number, Number, Number.
Return Type: Number.
Example: = date(2005, 04, 17)
MS-EXCEL returns 4/17/05
(ii) DATEVALUE: Returns the serial number of the date represented by date_text.
Syntax: DATEVALUE(date_text)
Argument Type: Text like date.
Return Type: Number.
Example: = DATEVALUE(“2005/04/17”)
MS-EXCEL returns 38459
(iii) DAY: Returns the day of a date, which is represented by a serial number. The day is given as an integer ranging from 1 to 31.
Syntax: DAY(serial_number).
Argument Type: Number(Date number).
Return Type: Number.
Example: =DAY(38459).
MS-EXCEL returns 17.
(iv) MONTH: Returns the month of a date, which is represented by a serial number. The month is given as an integer ranging from 1 to 12.
Syntax: MONTH(serial_number).
Argument Type: Number(Date number).
Return Type: Integer.
Example: = MONTH(38459).
MS-EXCEL returns 4
(v) NOW: Returns the serial number of the current date and time i.e. this function returns sum of current date number and current time number.
Syntax: NOW()
Argument Type: None.
Return Type: Number.
Example: = NOW()
MS-EXCEL returns 4/17/2005 21:58
(vi) TIME: Returns the decimal number for a particular time.
Syntax: TIME(hour, minute, second)
Argument Type: Number, Number, Number.
Return Type: Number (the time number)
Example:= TIME(21, 58, 12)
MS-EXCEL returns 9:58 PM
(vii) TODAY: Returns the serial number of current date i.e. the date number of current date.
Syntax: TODAY()
Argument Type: None.
Return Type: Number.
Example: = TODAY()
MS-EXCEL returns 4/17/2005
(b) Financial Functions.
(i) FV: Returns the future value of an investment based on periodic, constant payments and a constant interest rate.
Syntax: FV (rate, nper, pmt, pv, type)
Argument Type: Number, Number, Number, Number,
Return Type: Number.
Example:=FV(0.005, 18, -500, -2000, 0)
MS-EXCEL returns $11,580.75
(ii) PMT: Calculates the payment for a loan based on constant payments and a constant interest rate.
Syntax: PMT(rate, nper, pv, fv, type)
Argument Type: Number, Number, Number, Number, Number
Return Type: Number.
Example: = PMT(8%/12,10,10000,0,1)
MS-EXCEL returns ($1,030.16)
(iii) PV: Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now.
Syntax: PV(rate, nper, pmt, fv, type)
Argument Type: Number all.
Return Type: Number.
Example: PV(0.08/12,240,500,,0)
MS-EXCEL returns ($59,777.15)
(c) Logical Functions:
Examples based on the following table:
R. No. Name Marks Grade.
2001 Anil 96 A+
2002 Sanjay 43 F
2003 Jagdeep 66 A
2004 Rajneesh 55 B
2005 Renu 80 A+
(i) AND: The logical functions are used to see whether a condition is true or false or to check for multiple conditions.
Syntax: AND(Logical1, Logical2, ….)
Argument Type: Logical all.
Return Type: Logical.
Example: =AND(c2:c6)
MS-EXCEL returns TRUE
(ii) OR: Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
Syntax: OR(Logical1, Logical2, ….)
Argument Type: Logical all.
Return Type: Logical.
Example: = OR(c2:c6)
MS-EXCEL returns TRUE
Type: = OR(b2:b6)
MS-EXCEL returns #VALUE
(iii) NOT: Reverses the value of its expression that can be evaluated to TRUE or FALSE.
Syntax: NOT(logical)
Argument Type: Logical.
Return Type: Logical.
Example: = NOT(FALSE)
MS-EXCEL returns TRUE.
Type: = NOT(1+4=5)
MS-EXCEL returns FALSE.
(d) Math Functions.
(i) ABS: Returns the absolute value of a number.
Syntax: ABS(Number)
Argument Type: Number.
Return Type: Number.
Example: = ABS(5)
MS-EXCEL returns 5
Type: = ABS(-5)
MS-EXCEL returns 5
(ii) EXP: Returns e raised to the power of a number e.g. e^x. The constant e = 2.71828182845904, the base of the natural logarithm.
Syntax: EXP(number)
Argument Type: Number.
Return Type: Number.
Example: = EXP(1)
MS-EXCEL returns 2.718281828
Example: = EXP(2)
MS-EXCEL returns 7.389056099
(iii) INT: Rounds a number down to a nearest integer.
Syntax: INT(number)
Argument Type: Number.
Return Type: Number.
Example: = INT(8.9)
MS-EXCEL returns 8
Example: = INT(-8.9)
MS-EXCEL returns -9
(iv) LOG: Returns the logarithm of a number to the base we specify.
Syntax: LOG(Number, base)
Argument Type: Number, Number.
Return Type: Number.
Example: = LOG(10)
MS-EXCEL returns 1
Example: = LOG(8,2)
MS-EXCEL returns 3
(v) MOD: Returns the remainder after number is divided by divisor. The result has the same sign as the divisor.
Syntax: MOD(Number, divisor)
Argument Type: Number, Number.
Return Type: Number.
Example: = MOD(3,2)
MS-EXCEL returns 1
Example: = MOD(3,-2)
MS-EXCEL returns -1
(vi) ROUND: Returns a number to a specified number of digits.
Syntax: ROUND(number, num_digits)
Argument Type: Number.
Return Type: Number.
Example: = ROUND(2.15,1)
MS-EXCEL returns 2.2
Example: = ROUND(2.149,1)
MS-EXCEL returns 2.1
(vii) SQRT: Returns a positive square root.
Syntax: SQRT(number)
Argument Type: Number.
Return Type: Number.
Example: SQRT(16)
MS-EXCEL returns 4
Example: = SQRT(-16)
MS-EXCEL returns #NUM
(viii) SUM: Adds all the numbers in a range of cells.
Syntax: SUM(number1, number2, ….)
Argument Type: Number.
Return Type: Number.
Example: =SUM(3,2)
MS-EXCEL returns 5
Example: =SUM(a2, b2, 2)
MS-EXCEL returns 2
(ix) TRUNC: Truncates a number to an integer by removing the fractional part of the number.
Syntax: TRUNC(number, num_digits)
Argument Type: All number.
Return Type: Number.
Example:= TRUN(8.9)
MS-EXCEL returns 8
Example: = TRUN(-8.9)
MS-EXCEL returns -8
(d) Statistical Functions.
Examples based on the following table:
R. No. Name Marks Grade
2001 Anil 96 A+
2002 Sanjay 43 F
2003 Jagdeep 66 A
2004 Rajneesh 55 B
2005 Renu 80 A+
(i) AVERAGE: Returns the average of the arguments.
Syntax: AVERAGE(number 1, numner2, number3,….)
Argument Type: All numbers.
Return Type: Number.
Example: = AVERAGE(c2:c6)
MS-EXCEL returns 68
(ii) COUNT: Counts the number of cells that contain numbers and numbers within the list of arguments.
Syntax: COUNT(value 1, value2,…)
Argument Type: Any type.
Return Type: Number.
Example: = COUNT(c2:c6)
MS-EXCEL returns 5
(iii) MAX: Returns the largest value in a set of values.
Syntax: MAX(number1, number2,…)
Argument Type: All numbers.
Return Type: Number.
Example: = MAX(c2:c6)
MS-EXCEL returns 96
(iv) MIN: Returns the smallest value in a set of values.
Syntax: MIN(number1, number2,…)
Argument Type: All numbers.
Return Type: Number.
Example: = MIN(c2:c6)
MS-EXCEL returns 43
(f) Text Functions.
(i) CHAR: Returns the character specified by a number. The number is assumed to be the ASCII value and its equivalent is returned.
Syntax: CHAR (number)
Argument Type: Number.
Return Type: Character.
Example: = CHAR(65)
MS-EXCEL returns A.
(ii) CONCATENATE: Joins several text strings into one string.
Syntax: CONCATENATE(text1, text2,…)
Argument Type: All Text.
Return Type: Text.
Example: CONCATENATE(“Ram”, “Avtar”)
MS-EXCEL returns RamAvtar.
(iii) LEFT: Returns the first character or the characters in a text string, based on the number of characters we specify.
Syntax: LEFT(text, num_chars).
Argument Type: Text, Number.
Return Type: Text.
Example: = LEFT(“SALES PRICE”, 4)
MS-EXCEL returns SALE.
(iv) LEN: Returns the number of characters in a text string i.e. Length of a string.
Syntax: LEN(text).
Argument Type: Text.
Return Type: Number.
Example: = LEN(“SALES PRICE”)
MS-EXCEL returns 11
(v) LOWER: Converts all uppercase letters in a text string to lowercase.
Syntax: LOWER(text)
Argument Type: Text.
Return Type: Text.
Example: = LOWER(“SALES PRICE”)
MS-EXCEL returns sales price.
(vi) RIGHT: Returns the last character or the characters in a text string, based on the number of characters we specify.
Syntax: RIGHT(text, num_chars)
Argument Type: Text, Number.
Return Type: Text.
Example: = RIGHT(“SALES PRICE”, 5)
MS-EXCEL returns PRICE.
(vii) TRIM: Removes all spaces from text except for single spaces between the words.
Syntax: TRIM(text)
Argument Type: Text.
Return Type: Text.
Example:= TRIM(“SALES PRICE “)
MS-EXCEL returns SALES PRICE.
(viii) UPPER: Converts all lowercase letters in a text string to uppercase.
Syntax: UPPER(text)
Argument Type: Text.
Return Type: Text.
Example: UPPER(“sales price”)
MS-EXCEL returns SALES PRICE.
47. What is the LOOKUP Function?
Ans: The LOOKUP Function is categorized under Excel Lookup and Reference functions. The function performs a rough match lookup either in a one – row or one – column range and returns the corresponding value from another one – row or one-column range.
While doing financial analysis, if we wish to compare two rows or columns, we can use the LOOKUP function. It is designed to handle the simplest cases of vertical and horizontal lookup.
The more advanced versions of the LOOKUP function are HLOOKUP and VLOOKUP.
Formula (Vector):
There are two forms of Lookup: Vector and Array.
The vector form of the LOOKUP function will search one row or one column of data for a specified value and then get the data from the same position in another row or column.
The formula for the function is as follows:
= LOOKUP(lookup_value, lookup_vector, [result_vector])
It uses the following arguments:
(a) Lookup_value (required function): This is the value that we will be searching. It can be a logical value of TRUE or FALSE, reference to a cell, number, or text.
(b) Lookup_vector(required function): This is the one – dimensional data that we wish to search. Remember, we need to sort it in ascending order.
(c) Result_vector: An optional one-dimensional list of data from which we want to return a value. If supplied, the [result_vector] must be the same length as the lookup_vector. If the [result_vector] is omitted, the result is returned from the lookup_vector.
The array form of LOOKUP looks in the first row or column of an array for the specified value and returns a value from the same position in the last row or column of the array. We need to use this form of LOOKUP when the values that we want to match are in the first row or column of the array.
Formula (Array) LOOKUP Function:
= LOOKUP(lookup_value, array)
The arguments are as follows:
(d) Lookup_value (required argument): This is a value that we are searching for.
(e) Array (required argument): A range of cells that contains text, numbers, or logical values that we want to compare with the lookup_value.
How to use the LOOKUP Function in Excel?
As a worksheet function, the LOOKUP Function can be entered as part of a formula in a cell of a worksheet.
To understand the uses of this function, let us consider a few examples:
Example 1: Assume we are given a list of products, color, order_id, and quantity. We want a dashboard where we put the product and then we instantly get the quantity.
The formula to use will be:
The result we get is:
Example 2: Suppose we are in the business of giving loans and we offer different interest rates based on the amount borrowed. We are given the data below:
The formula to use will be:
We will get the following result:
Things to remember about the LOOKUP Function:
(i) #N/A error – Occurs when the Lookup function fails to find the closest match to the supplied lookup_value. This occur when: the smallest value in the lookup_vector arrives.
48. How to use the VLOOKUP Function in Excel?
Ans:
49. How to use Data Management in excel?
Ans: Data menu of the Excel provides various commands, which you can apply to your data.
Sorting of Data:
To sort the data you have to perform the following steps.
(a) Select the columns which you want to sort.
(b) Choose Sort option from Data menu. You will find out the following dialog box on the screen.
(c) Choose the Column on which you want to do the sorting. You can select more than one column.
(d) Select the Ascending or descending option.
(e) Select the Header row if there is any header row in the table.
(f) Click on the OK button.
