Tag: Principles of Marketing

Bangalore University B.com Notes

Text Functions: LEN, TRIM, PROPER, UPPER, LOWER, CONCATENATE

indiafreenotesby indiafreenotes1
Function Description
ASC function Changes full-width (double-byte) English letters or katakana within a character string to half-width (single-byte) characters
BAHTTEXT function Converts a number to text, using the ß (baht) currency format
CHAR function Returns the character specified by the code number
CLEAN function Removes all nonprintable characters from text
CODE function Returns a numeric code for the first character in a text string
CONCAT function Combines the text from multiple ranges and/or strings, but it doesn’t provide the delimiter or IgnoreEmpty arguments.
CONCATENATE function Joins several text items into one text item
DBCS function Changes half-width (single-byte) English letters or katakana within a character string to full-width (double-byte) characters
DOLLAR function Converts a number to text, using the $ (dollar) currency format
EXACT function Checks to see if two text values are identical
FIND, FINDB functions Finds one text value within another (case-sensitive)
FIXED function Formats a number as text with a fixed number of decimals
LEFT, LEFTB functions Returns the leftmost characters from a text value
LEN, LENB functions Returns the number of characters in a text string
LOWER function Converts text to lowercase
MID, MIDB functions Returns a specific number of characters from a text string starting at the position you specify
NUMBERVALUE function Converts text to number in a locale-independent manner
PHONETIC function Extracts the phonetic (furigana) characters from a text string
PROPER function Capitalizes the first letter in each word of a text value
REPLACE, REPLACEB functions Replaces characters within text
REPT function Repeats text a given number of times
RIGHT, RIGHTB functions Returns the rightmost characters from a text value
SEARCH, SEARCHB functions Finds one text value within another (not case-sensitive)
SUBSTITUTE function Substitutes new text for old text in a text string
T function Converts its arguments to text
TEXT function Formats a number and converts it to text
TEXTJOIN function Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
TRIM function Removes spaces from text
UNICHAR function Returns the Unicode character that is references by the given numeric value
UNICODE function Returns the number (code point) that corresponds to the first character of the text
UPPER function Converts text to uppercase
VALUE function Converts a text argument to a number

Bangalore University B.com Notes

Perform calculations by using MIN and MAX function

indiafreenotesby indiafreenotes0

Select a cell below or to the right of the numbers for which you want to find the smallest number.

On the Home tab, in the Editing group, click the arrow next to AutoSum Button image, click Min (calculates the smallest) or Max (calculates the largest), and then press ENTER.

If the cells are not in a contiguous row or column

To do this task, use the MIN, MAX, SMALL, or LARGE functions.

Example

Copy the following data to a blank worksheet.

1 A
  Data
2 10
3 7
4 9
5 27
6 0
7 4
  Formula Description (Result)
  =MIN(A2:A7) Smallest number in the range (0)
  =MAX(A2:A7) Largest number in the range (27)
  =SMALL(A2:A7, 2) Second smallest number in the range (4)
  =LARGE(A2:A7,3) Third largest number in the range (9)

Bangalore University B.com Notes

Perform calculations by using the AVERAGE function

indiafreenotesby indiafreenotes0

Returns the average (arithmetic mean) of the arguments. For example, if the range A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers.

Syntax

AVERAGE(number1, [number2], …)

The AVERAGE function syntax has the following arguments:

Number1    Required. The first number, cell reference, or range for which you want the average.

Number2, …    Optional. Additional numbers, cell references or ranges for which you want the average, up to a maximum of 255.

  • Arguments can either be numbers or names, ranges, or cell references that contain numbers.
  • Logical values and text representations of numbers that you type directly into the list of arguments are not counted.
  • If a range or cell reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
  • Arguments that are error values or text that cannot be translated into numbers cause errors.
  • If you want to include logical values and text representations of numbers in a reference as part of the calculation, use the AVERAGEA function.
  • If you want to calculate the average of only the values that meet certain criteria, use the AVERAGEIF function or the AVERAGEIFS function.
  • Average, which is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5.
  • Median, which is the middle number of a group of numbers; that is, half the numbers have values that are greater than the median, and half the numbers have values that are less than the median. For example, the median of 2, 3, 3, 5, 7, and 10 is 4.
  • Mode, which is the most frequently occurring number in a group of numbers. For example, the mode of 2, 3, 3, 5, 7, and 10 is 3.

To locate the Show a zero in cells that have a zero-value check box:

On the File tab, click Options, and then, in the Advanced category, look under Display options for this worksheet.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Data
10 15 32
7
9
27
2
Formula Description Result
=AVERAGE(A2:A6) Average of the numbers in cells A2 through A6. 11
=AVERAGE(A2:A6, 5) Average of the numbers in cells A2 through A6 and the number 5. 10
=AVERAGE(A2:C2) Average of the numbers in cells A2 through C2. 19

Bangalore University B.com Notes

Perform calculations by using the COUNT function

indiafreenotesby indiafreenotes1

Use COUNTIF, one of the statistical functions, to count the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list.

In its simplest form, COUNTIF says:

=COUNTIF(Where do you want to look?, What do you want to look for?)

For example:

=COUNTIF(A2:A5,”London”)

=COUNTIF(A2:A5,A4)

Examples

To use these examples in Excel, copy the data in the table below, and paste it in cell A1 of a new worksheet.

Data Data
apples 32
oranges 54
peaches 75
apples 86
Formula Description
=COUNTIF(A2:A5,”apples”) Counts the number of cells with apples in cells A2 through A5. The result is 2.
=COUNTIF(A2:A5,A4) Counts the number of cells with peaches (the value in A4) in cells A2 through A5. The result is 1.
=COUNTIF(A2:A5,A2)+COUNTIF(A2:A5,A3) Counts the number of apples (the value in A2), and oranges (the value in A3) in cells A2 through A5. The result is 3. This formula uses COUNTIF twice to specify multiple criteria, one criteria per expression. You could also use the COUNTIFS function.
=COUNTIF(B2:B5,”>55″) Counts the number of cells with a value greater than 55 in cells B2 through B5. The result is 2.

Management Notes

Perform calculations by using the SUM function

indiafreenotesby indiafreenotes0

If you need to sum a column or row of numbers, let Excel do the math for you. Select a cell next to the numbers you want to sum, click AutoSum on the Home tab, press Enter, and you’re done.

When you click AutoSum, Excel automatically enters a formula (that uses the SUM function) to sum the numbers.

Here’s an example. To add the January numbers in this Entertainment budget, select cell B7, the cell immediately below the column of numbers. Then click AutoSum. A formula appears in cell B7, and Excel highlights the cells you’re totalling.

Press Enter to display the result (95.94) in cell B7. You can also see the formula in the formula bar at the top of the Excel window.

Management Notes

Perform logical operations by using the AVERAGEIF function

indiafreenotesby indiafreenotes0

AVERAGEIF(range, criteria, [average_range])

The AVERAGEIF function syntax has the following arguments:

  • Range Required. One or more cells to average, including numbers or names, arrays, or references that contain numbers.
  • Criteria Required. The criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged. For example, criteria can be expressed as 32, “32”, “>32”, “apples”, or B4.
  • Average_range Optional. The actual set of cells to average. If omitted, range is used.

Remarks

  • Cells in range that contain TRUE or FALSE are ignored.
  • If a cell in average_range is an empty cell, AVERAGEIF ignores it.
  • If range is a blank or text value, AVERAGEIF returns the #DIV0! error value.
  • If a cell in criteria is empty, AVERAGEIF treats it as a 0 value.
  • If no cells in the range meet the criteria, AVERAGEIF returns the #DIV/0! error value.
  • You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
  • Average_range does not have to be the same size and shape as range. The actual cells that are averaged are determined by using the top, left cell in average_range as the beginning cell, and then including cells that correspond in size and shape to range. For example:
If range is And average_range is Then the actual cells evaluated are
A1:A5 B1:B5 B1:B5
A1:A5 B1:B3 B1:B5
A1:B4 C1:D4 C1:D4
A1:B4 C1:C2 C1:D4

Average: Which is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5.

Median: Which is the middle number of a group of numbers; that is, half the numbers have values that are greater than the median, and half the numbers have values that are less than the median. For example, the median of 2, 3, 3, 5, 7, and 10 is 4.

Mode: Which is the most frequently occurring number in a group of numbers. For example, the mode of 2, 3, 3, 5, 7, and 10 is 3.

Example

Region Profits (Thousands)
East 45678
West 23789
North -4789
South (New Office) 0
MidWest 9678
Formula Description Result
=AVERAGEIF(A2:A6,”=*West”,B2:B6) Average of all profits for the West and MidWest regions. 16733.5
=AVERAGEIF(A2:A6,”<>*(New Office)”,B2:B6) Average of all profits for all regions excluding new offices. 18589

Management Notes

Perform logical operations by using the IF function

indiafreenotesby indiafreenotes1

The IF function is one of the most popular functions in Excel, and it allows you to make logical comparisons between a value and what you expect.

So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.

For example, =IF(C2=” Yes”,1,2) says IF(C2 = Yes, then return a 1, otherwise return a 2).

Common problems

Problem What went wrong
0 (zero) in cell There was no argument for either value_if_true or value_if_False arguments. To see the right value returned, add argument text to the two arguments, or add TRUE or FALSE to the argument.
#NAME? in cell This usually means that the formula is misspelled.

Management Notes

Perform logical operations by using the SUMIF function

indiafreenotesby indiafreenotes1

SUMIF(range, criteria, [sum_range])

The SUMIF function syntax has the following arguments:

range   Required. The range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored. The selected range may contain dates in standard Excel format (examples below).

Criteria Required. The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added. Wildcard characters can be included – a question mark (?) to match any single character, an asterisk (*) to match any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.

For example, criteria can be expressed as 32, “>32”, B5, “3?”, “apple*”, “*~?”, or TODAY().

sum_range   Optional. The actual cells to add, if you want to add cells other than those specified in the range argument. If the sum range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).

Sum_range should be the same size and shape as range. If it isn’t, performance may suffer, and the formula will sum a range of cells that starts with the first cell in sum_range but has the same dimensions as range. For example:

range sum_range Actual summed cells
A1:A5 B1:B5 B1:B5
A1:A5 B1:K5 B1:B5

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Category Food Sales
Vegetables Tomatoes $2,300
Vegetables Celery $5,500
Fruits Oranges $800
Butter $400
Vegetables Carrots $4,200
Fruits Apples $1,200
Formula Description Result
=SUMIF(A2:A7,”Fruits”,C2:C7) Sum of the sales of all foods in the “Fruits” category. $2,000
=SUMIF(A2:A7,”Vegetables”,C2:C7) Sum of the sales of all foods in the “Vegetables” category. $12,000
=SUMIF(B2:B7,”*es”,C2:C7) Sum of the sales of all foods that end in “es” (Tomatoes, Oranges, and Apples). $4,300
=SUMIF(A2:A7,””,C2:C7) Sum of the sales of all foods that do not have a category specified. $400

Management Notes

Perform statistical operations by using the COUNTIF function

indiafreenotesby indiafreenotes0

Use COUNTIF, one of the statistical functions, to count the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list.

In its simplest form, COUNTIF says:

=COUNTIF(Where do you want to look?, What do you want to look for?)

For example:

  • =COUNTIF(A2:A5,”London”)
  • =COUNTIF(A2:A5,A4)

To use these examples in Excel, copy the data in the table below, and paste it in cell A1 of a new worksheet.

Data Data
apples 32
oranges 54
peaches 75
apples 86
Formula Description
=COUNTIF(A2:A5,”apples”) Counts the number of cells with apples in cells A2 through A5. The result is 2.
=COUNTIF(A2:A5,A4) Counts the number of cells with peaches (the value in A4) in cells A2 through A5. The result is 1.
=COUNTIF(A2:A5,A2)+COUNTIF(A2:A5,A3) Counts the number of apples (the value in A2), and oranges (the value in A3) in cells A2 through A5. The result is 3. This formula uses COUNTIF twice to specify multiple criteria, one criteria per expression. You could also use the COUNTIFS function.
=COUNTIF(B2:B5,”>55″) Counts the number of cells with a value greater than 55 in cells B2 through B5. The result is 2.
=COUNTIF(B2:B5,”<>”&B4) Counts the number of cells with a value not equal to 75 in cells B2 through B5. The ampersand (&) merges the comparison operator for not equal to (<>) and the value in B4 to read =COUNTIF(B2:B5,”<>75″). The result is 3.
=COUNTIF(B2:B5,”>=32″)-COUNTIF(B2:B5,”>85″) Counts the number of cells with a value greater than (>) or equal to (=) 32 and less than (<) or equal to (=) 85 in cells B2 through B5. The result is 3.
=COUNTIF(A2:A5,”*”) Counts the number of cells containing any text in cells A2 through A5. The asterisk (*) is used as the wildcard character to match any character. The result is 4.
=COUNTIF(A2:A5,”?????es”) Counts the number of cells that have exactly 7 characters, and end with the letters “es” in cells A2 through A5. The question mark (?) is used as the wildcard character to match individual characters. The result is 2.

Management Notes

Auto completion of Series, Sort and filter, Charts

indiafreenotesby indiafreenotes0

Auto completion of series

Enter the first number in the top cell. Hover until you see the Fill Handle and with the RIGHT mouse button, drag the Fill Handle until you have selected the cells to autofill.

Autofilling Via the Excel Ribbon

The Series window can be reached via the ribbon in Excel version 2007 and newer.

  • Enter the first number of the series. Then select that cell and the cells that you want to auto fill.
  • Click the Fill button located on the Editing section of the ribbon’s Home tab as shown in the first image.
  • A drop-down menu appears as shown in the second image. Click Series and the Series window appears as shown below.
  • Enter your step value and any other values on the Series window and click OK.

Through Drag

  • Select one or more cells you want to use as a basis for filling additional cells.
  • For a series like 1, 2, 3, 4, 5…, type 1 and 2 in the first two cells. For the series 2, 4, 6, 8…, type 2 and 4.
  • For the series 2, 2, 2, 2…, type 2 in first cell only.
  • Drag the fill handle Fill handle.
  • If needed, click Auto Fill Options Button image and choose the option you want.

Sort and filter

Sorting data is an integral part of data analysis. You might want to arrange a list of names in alphabetical order, compile a list of product inventory levels from highest to lowest, or order rows by colors or icons. Sorting data helps you quickly visualize and understand your data better, organize and find the data that you want, and ultimately make more effective decisions.

You can sort data by text (A to Z or Z to A), numbers (smallest to largest or largest to smallest), and dates and times (oldest to newest and newest to oldest) in one or more columns. You can also sort by a custom list you create (such as Large, Medium, and Small) or by format, including cell color, font color, or icon set.

Sort text

  1. Select a cell in the column you want to sort.
  2. On the Data tab, in the Sort & Filter group, do one of the following:
  • To quick sort in ascending order, click A to Z command in Excel that sorts A to Z or smallest number to largest (Sort A to Z).
  • To quick sort in descending order, click Z to A command in Excel that sorts Z to A or largest number to smallest (Sort Z to A).

Filtering Data

The filter feature applies a drop-down menu to each column heading, allowing you to select specific choices to narrow a table. Using the above example, let’s say you wanted to filter your table by Company and Salesperson. Specifically, you want to find the number of sales Dylan Rogers made to Eastern Company.

To do this using the filter you would:

  • Go to the Data tab on Excel ribbon
  • Select the Filter tool
  • Select Eastern Company from the dropdown menu
  • Select Dylan Rogers from the Salesperson dropdown menu

Charts

Create a chart

  • Select the data for which you want to create a chart.
  • Click INSERT > Recommended Charts.
  • On the Recommended Charts tab, scroll through the list of charts that Excel recommends for your data, and click any chart to see how your data will look.
  • If you don’t see a chart you like, click All Charts to see all the available chart types.
  • When you find the chart you like, click it > OK.
  • Use the Chart Elements, Chart Styles, and Chart Filters buttons, next to the upper-right corner of the chart to add chart elements like axis titles or data labels, customize the look of your chart, or change the data that is shown in the chart.
  • To access additional design and formatting features, click anywhere in the chart to add the CHART TOOLS to the ribbon, and then click the options you want on the DESIGN and FORMAT tabs.
error: Content is protected !!