Four Microsoft Excel functions

Sponsored Links

we will discuss four Microsoft Excel functions:

  • ABSOLUTE
  • TEXT
  • IF THEN
  • CONCATENATE

At the end we will then combine the four functions in one example to demonstrate the power of these functions.
Sponsored Links


The absolute function returns the absolute value of a number. The absolute value of a number is the number without its sign.

The syntax for using the absolute functions is:

      =ABS(number)

Where “number” is the real number of which you want the absolute value.
For example:

=ABS(3) equals 3

=ABS(-3) equals 3

If A1 contains -20, then:

=ABS(A1) equals 20

TEXT

With text functions, you can manipulate text strings in formulas. The text function will convert a value to text in a specific number or date format.
Sponsored Links

The syntax for using the “TEXT” function is:

=TEXT(value,format_text)

“Value” is a numeric value, a formula that evaluates to a numeric value, or a reference to a cell containing a numeric value.

“Format_text” is a number format in text form from in the Category box on the Number tab in the Format Cells dialog box. This will become clearer when you see an example. “Format_text” cannot contain an asterisk (*) and cannot be the General number format.

Using the TEXT function converts a value to formatted text, and the result is no longer calculated as a number.

For example:

=TEXT(2.715, “$0.00”) equals “$2.72”

=TEXT(“4/15/91”, “mmmm dd, yyyy”) equals “April 15, 1991”

There are numerous other text-type functions and we will discuss these in future newsletters.

IF THEN


This is an extremely powerful function and we will be using this throughout future newsletters.

The “IF THEN ” functions returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE. Use IF to conduct conditional tests on values and formulas.

The syntax for the “IF THEN” statement is:

      =IF(logical_test,value_if_true,value_if_false)

“Logical_test” is any value or expression that can be evaluated to TRUE or FALSE.

“Value_if_true” is the value that is returned if “logical_test” is TRUE. If “logical_test” is TRUE and “value_if_true” is omitted, TRUE is returned. “Value_if_true” can be another formula including another IF THEN function.

“Value_if_false” is the value that is returned if “logical_test” is FALSE. If “logical_test” is FALSE and “value_if_false” is omitted, FALSE is returned. “Value_if_false” can be another formula including another IF THEN function.

Up to seven IF THEN functions can be nested as “value_if_true” and “value_if_false” arguments to construct more elaborate tests.

For example:

      =IF(A1=200,SUM(B5:B15),””)

If the value in cell A1 is 200, then “logical_test” is TRUE, and the total value for the range B5:B15 is calculated. Otherwise, “logical_test” is FALSE, and empty text (“”) is returned that blanks the cell that contains the IF THEN function.

or

Suppose an expense worksheet contains in B2:B4 the following data for “Actual Expenses” for January, February, and March: 1500, 500, 500. C2:C4 contains the following data for “Predicted Expenses” for the same periods: 900, 900, 925.

You can write a formula to check whether you are over budget for a particular month, generating text for a message with the following formulas:

=IF(B2>C2,”Over Budget”,”OK”) equals “Over Budget”

=IF(B3>C3,”Over Budget”,”OK”) equals “OK”

CONCATENATE (“&”)


The concatenate function joins several text strings into one text string.

The syntax for using this function is:

“Text 1″&” Test2″

Text1, text2, … are 1 to 30 text items to be joined into a single text item. The text items can be text strings, numbers, or single-cell references.

For example:

“Total “&”Value” equals “Total Value”.

BRINGING IT ALL TOGETHER


Below is a link to a sample income statement for ABC Widget Company. To better understand this example you should download a copy of this spreadsheet by clicking here: Oct 1999 Sample

We have used the four functions above to generate dynamic notes to the income statement that will change with values. For example try changing the sales from $100,000 to $250,000. Notice how the notes about “Sales”, “Net operating revenue” and “Net income” all changed dynamically.

The formula to generate the note about the change in sales looks like this:

=”Sales for “&$E$6&IF(G10>0,” increased “,” decreased “)&TEXT(ABS(G10), “$0,000″)&” or “&TEXT(ABS(H10),”0%”)&” compared to “&$D$6

Sponsored Links

Where:

$E$6 is the cell containing the name of the current month

G10 is the cell containing the dollar change in sales

H10 is the cell containing the percent change in sales

$D$6 is the cell containing the name of the previous month

The formulas for the other notes are similar.

This of course is a simply example, if you are able to import the statistical/operating data that drives you income statement you can generate very sophisticated notes to the income statement automatically.

Two Microsoft Excel tools

Sponsored Links

we will discuss two Microsoft Excel tools: 

  • GOAL SEEK
  • SOLVER

At the end we will then demonstrate the power of these function.

Sponsored Links

GOAL SEEK

Allow you to find a specific result for a cell by adjusting the value of one other cell

    1 – On the “Tools” menu, click “Goal Seek”

2 – In the “Set cell” box, enter the reference for the cell that

    contains the formula you want to resolve.

3 – In the “To value” box, type the result you want.

    4 – In the “By changing cell” box, enter the reference for the
          cell that contains the value you want to adjust.

SOLVER


Sponsored Links

Solver is similar to Goal Seek in that you can define a specific result, but unlike Goal Seek you can adjust up to 200 other cells.

1 – On the Tools menu, click Solver.
If the Solver command is not on the Tools menu,
you need to install the Solver add-in.

2 – In the Set Target Cell box, enter a cell reference or name

for the target cell. The target cell must contain a formula.
3 – To have the value of the target cell be as large as possible,
click Max.
To have the value of the target cell be as small as possible,
click Min.
To have the target cell be a certain value, click Value of,
and then type the value in the box.
4 – In the By Changing Cells box, enter a name or reference for
each adjustable cell, separating nonadjacent references with
commas. The adjustable cells must be related directly or
indirectly to the target cell. You can specify up to 200
adjustable cells.
To have Solver automatically propose the adjustable cells based
on the target cell, click Guess.
5 – In the Subject to the Constraints box, enter any constraints you
want to apply.
6 – Click Solve.
7 – To keep the solution values on the worksheet, click Keep Solver
Solution in the Solver Results dialog box.
To restore the original data, click Restore Original Values.

Sponsored Links

Tips:
You can interrupt the solution process by pressing ESC. Microsoft Excel recalculates the worksheet with the last values found for the adjustable cells.

BRINGING IT ALL TOGETHER


Below is a link to a simple marketing campaign model for ABC Widget Company. To better understand this example you should download a copy of this spreadsheet by clicking here: Nov 1999 Sample

In the marketing campaign model, rows 5 through 17 contain the assumptions and rows 26 through 33 contain the results.

The formulas to generate the results are relative simple and can be analyzed by click on the various cells.

Now suppose you want to know what is break-even response rate to the marketing campaign assuming the following:

      Number of Solicitations 500,000 (in cell D5)
      Solicitation Unit Cost $0.32 (in cell D7)
      Average Sale $49.99 (in cell D11)
      Cost of Goods Sold% 50% (in cell D13)
      Average shipping per sale $4.99 (in cell D15)
    Fixed Costs $10,000 (in cell D17)

From the tool bar, click “Tools”, then “Goal Seek”, click cell D33 (contribution margin), enter zero in the “To Value:” box, then enter D9 (response rate) in the “By Changing Cell” box. Finally click “OK”. The results show that a response rate of 1.7% to the marketing campaign will result in a break even!!!!

You can play around with different variation of this. Fixing other assumptions and varying other cells such as “Average Sales”, or “Solicitation Unit Cost”.

The same results can be achieved using “Solver”. For this example we will change the value in D7(solicitation unit cost) to $0.30.

From the tool bar, click “Tools”, then “Solver”, enter D33 (contribution margin) in the “Set Target Box”, check the box “Value of” and enter zero in the box, then enter D9 (response rate) in the “By Changing Cell” box. Finally click “Solve”. The results again show that a response rate of 1.6% to the marketing campaign will result in a break even!!!!

This of course is a simply example, if you have marketing models you can use solver and goal seek to set criteria and set goals.

Explore Microsoft Excel’s ability to import and summarize data

Sponsored Links

we will explore Microsoft Excel’s ability to import and summarize data using the following twelve different functions: 

  • IMPORT
  • LIST
  • SORT A LIST
  • SUBTOTALS
  • COUNT
  • COUNTA
  • COUNTIF
  • SUMIF
  • AVERAGE
  • MEDIAN
  • MAX
  • MIN

IMPORT


Sponsored Links

Microsoft Excel can import data from external data sources such as an AS400, the internet, or an external database. Examples of data that we have imported and manipulated include fixed assets, accounts receivable, inventory, sales by customer, etc.

Start by simply exporting the data from it source program in one of the following formats:

        – TXT (“Text”)

 

        – CSV (“Comma Separated Variables”)

 

      – DIF (“Data Interchange File”)

Then open the file like a normal Excel file, the “Text Import Wizard” will guide you through importing the data. Nearly all files are comma delimited. The result spreadsheet is called a “list”.

LIST


A LIST is a series of worksheet rows that contain related data, such as an invoice database or a set of client names and phone numbers. A list can be used as a database, in which rows are records and columns are fields. The first row of the list has labels for the columns.

SORT A LIST


You can rearrange the rows or columns of a list based on the values in the list by sorting. When you sort, Microsoft Excel rearranges rows, columns, or individual cells by using the sort order that you specify. You can sort lists in ascending (1 to 9, A to Z) or descending (9 to 1, Z to A) order, and sort based on the contents of one or more columns.

Microsoft Excel sorts lists alphabetically by default. If you need to sort months and weekdays according to their calendar order instead of their alphabetic order, use a custom sort order. You can also rearrange lists in a specific order by creating custom sort orders. For example, if you have a list that contains the entry “Low,” “Medium,” or “High” in a column, you can create a sort order that arranges rows that contain “Low” first, rows that contain “Medium” next, and rows with “High” last.

To perform a sort highlight the entire data range you would like to sort. Then from the toolbar select “Data” and “Sort”. Select the rows to sort and the order desired ascending or descending. If the list does not have a header row select, check “No Header Row”

Sponsored Links

SUBTOTALS


Insert subtotal values into a list

In a long list of data, you can use subtotals to automatically outline the information on a worksheet. You can “nest,” or insert, subtotals for smaller groups within subtotal groups, which is useful when you need to calculate subtotals within groups of values. You can remove subtotals from a list without affecting your original data.

      1 – Sort the list by the column for which you want to calculate subtotals. For example, to summarize the units sold by each salesperson in a list of salespeople, sales amounts, and the number of units sold, sort the list by the salesperson column.
        2 – Click a cell in the list.

 

        3 – On the Data menu, click Subtotals.

 

        4 – In the At each change in box, click the column that contains the groups for which you want subtotals. This should be the same column by which you sorted your list in step 1.

 

        5 – In the Use function box, click the function you want to use to calculate the subtotals.

 

        6 – In the Add subtotal to box, select the check boxes for the columns that contain the values for which you want subtotals.

 

COUNT


Counts the number of cells that contain numbers and numbers within the list of arguments. Use COUNT to get the number of entries in a number field in a range or array of numbers.

Syntax

COUNT(value1,value2, …)

    “Value1”, “value2”, … are 1 to 30 arguments that can contain or refer to a variety of different types of data, but only numbers are counted.

Arguments that are numbers, dates, or text representations of numbers are counted; arguments that are error values or text that cannot be translated into numbers are ignored.

If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored. If you need to count logical values, text, or error values, use the COUNTA function.

Examples

In the following example, suppose A1:A7 contain “Sales”, “12/8/90”, “”, “19”, “22.24”, “TRUE”, “#DIV/0!” respectively:

COUNT(A1:A7) equals 3
COUNT(A4:A7) equals 2
COUNT(A1:A7, 2) equals 4

COUNTA


Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array.

Syntax

COUNTA(value1,value2, …)

    “Value1”, “value2”, … are 1 to 30 arguments representing the values you want to count. In this case, a value is any type of information, including empty text (“”) but not including empty cells. If an argument is an array or reference, empty cells within the array or reference are ignored. If you do not need to count logical values, text, or error values, use the COUNT function.

Examples

In the following example, suppose A1:A7 contain “Sales”, “12/8/90”, “”, “19”, “22.24”, “TRUE”, “#DIV/0!” respectively:

COUNTA(A1:A7) equals 6
COUNTA(A4:A7) equals 4
COUNTA(A1:A7, 2) equals 7
COUNTA(A1:A7, “Two”) equals 7

COUNTIF


Counts the number of cells within a range that meet the given criteria.

Syntax

COUNTIF(range,criteria)

“Range” is the range of cells from which you want to count cells.

“Criteria” is the criteria in the form of a number, expression, or text that defines which cells will be counted. For example, criteria can be expressed as 32, “32”, “>32”, “apples”.

Examples

Suppose A3:A6 contain “apples”, “oranges”, “peaches”, “apples”, respectively:
COUNTIF(A3:A6,”apples”) equals 2
Suppose B3:B6 contain 32, 54, 75, 86, respectively:
COUNTIF(B3:B6,”>55″) equals 2

SUMIF


Adds the cells specified by a given criteria.

Syntax

SUMIF(range,criteria,sum_range)

“Range” is the range of cells you want evaluated.

“Criteria” is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, “32”, “>32”, “apples”.

“Sum_range” are the actual cells to sum. The cells in sum_range are summed only if their corresponding cells in range match the criteria. If sum_range is omitted, the cells in range are summed.

Example

Suppose A1:A4 contain the following property values for four homes: $100,000, $200,000, $300,000, $400,000, respectively. B1:B4 contain the following sales commissions on each of the corresponding property values: $7,000, $14,000, $21,000, $28,000.

SUMIF(A1:A4,”>160000″,B1:B4) equals $63,000

AVERAGE


Returns the average (arithmetic mean) of the arguments.

Syntax

AVERAGE(number1,number2, …)

    “Number1”, “number2”, … are 1 to 30 numeric arguments for which you want the average.

Remarks

      The arguments must be either numbers or names, arrays, or references that contain numbers.

If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.

Tip: When averaging cells, keep in mind the difference between empty cells and those containing the value zero, especially if you have cleared the Zero values check box on the View tab (Options command, Tools menu). Empty cells are not counted, but zero values are.

Example

If A1:A5 is named Scores and contains the numbers 10, 7, 9, 27, and 2, then:

AVERAGE(A1:A5) equals 11
AVERAGE(Scores) equals 11
AVERAGE(A1:A5, 5) equals 10
AVERAGE(A1:A5) equals SUM(A1:A5)/COUNT(A1:A5) equals 11

If C1:C3 is named OtherScores and contains the numbers 4, 18, and 7, then:

AVERAGE(Scores, OtherScores) equals 10.5

MEDIAN


Sponsored Links

Returns the median of the given numbers. The median is the number in the middle of a set of numbers; that is, half the numbers have values that are greater than the median, and half have values that are less.

Syntax

MEDIAN(number1,number2, …)

      “Number1”, “number2”, … are 1 to 30 numeric arguments for which you want the median.

The arguments should be either numbers or names, arrays, or references that contain numbers. Microsoft Excel examines all the numbers in each reference or array argument.

If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.

Remarks

    If there is an even number of numbers in the set, then MEDIAN calculates the average of the two numbers in the middle. See the second example following.

Example

MEDIAN(1, 2, 3, 4, 5) equals 3

MEDIAN(1, 2, 3, 4, 5, 6) equals 3.5, the average of 3 and 4

MAX


Returns the largest value in a set of values.

Syntax

MAX(number1,number2,…)

“Number1”, “number2”,… are 1 to 30 numbers for which you want to find the maximum value.

      You can specify arguments that are numbers, empty cells, logical values, or text representations of numbers. Arguments that are error values or text that cannot be translated into numbers cause errors.

If an argument is an array or reference, only numbers in that array or reference are used. Empty cells, logical values, or text in the array or reference are ignored. If logical values and text must not be ignored, use MAXA instead.

If the arguments contain no numbers, MAX returns 0 (zero).

Example

If A1:A5 contains the numbers 10, 7, 9, 27, and 2, then:

MAX(A1:A5) equals 27
MAX(A1:A5,30) equals 30

MIN


Returns the smallest number in a set of values.

Syntax

MIN(number1,number2,…)

“Number1”, “number2”,… are 1 to 30 numbers for which you want to find the minimum value.

      You can specify arguments that are numbers, empty cells, logical values, or text representations of numbers. Arguments that are error values or text that cannot be translated into numbers cause errors.

If an argument is an array or reference, only numbers in that array or reference are used. Empty cells, logical values, or text in the array or reference are ignored.

If the arguments contain no numbers, MIN returns 0 (zero).

Example

If A1:A5 contains the numbers 10, 7, 9, 27, and 2, then:

MIN(A1:A5) equals 2
MIN(A1:A5,0) equals 0

Common Print Problems Experienced When Using Microsoft Excel

we will present solutions to the following common print problems experienced when using Microsoft Excel:

        1 – WHY DOES MICROSOFT EXCEL PRINT ONLY A PORTION OF MY WORKSHEET?

2 – I WANT MY SHEET TO PRINT ONE PAGE WIDE (OR TALL)3 – MY PRINTED HEADER OR FOOTER DOESN’T LOOK THE WAY I WANT4 – MICROSOFT EXCEL IGNORES THE PAGE BREAKS I SET5 – WHY DO SOME COLUMNS AND ROWS PRINT ON THE WRONG PAGE?6 – WHY DOESN’T EXCEL PRINT MULTIPLE PRINT AREAS ON THE SAME PAGE?7 – I CAN’T SCALE A CHART TO PRINT THE SIZE I WANT OR PRINT MULTIPLE CHARTS ON A SINGLE PAGE?

Sponsored Links

WHY DOES MICROSOFT EXCEL PRINT ONLY A PORTION OF MY WORKSHEET?


CHECK WHETHER A PRINT AREA IS DEFINED. If the worksheet contains a print area, Microsoft Excel prints only the print area. To determine whether your worksheet contains a print area, click “Page Break Preview” on the “View” menu. The area of the worksheet that appears with a white background is the area that is printed.

To print the whole sheet, clear the print area. You can clear the print area by using the “Clear Print Area” command (“Print Area” on the “File” menu).

CHANGE THE PRINT AREA. You can change the size of the print area so that it includes or excludes rows and columns. On the “View” menu, click “Page Break Preview”, and then drag the borders that surround the print area.

To add nonadjacent cells to the print area, select the cells, right-click any cell in the selection, and then click “Add to Print Area” on the shortcut menu.

Sponsored Links

I WANT MY SHEET TO PRINT ONE PAGE WIDE (OR TALL)


You can prevent columns or rows from spilling over the edge of a page by shrinking the sheet so that it is one page wide or tall. On the “File” menu, click “Page Setup”, and then click the “Page” tab. Click “Fit to”, type “1” in either the “page(s) wide by” box or the “tall” box, and then delete the number (if any) from the other box.

MY PRINTED HEADER OR FOOTER DOESN’T LOOK THE WAY I WANT!


You can choose from several built-in headers and footers or create your own. On the “View” menu, click “Header and Footer”.

Use black and white only. The text in headers and footers always prints in black. You cannot apply color to the text in headers or footers, even if you have a color printer.

Use print titles to repeat graphics and linked information. If you want to repeat graphics or linked information at the top of every page, use print titles instead of a header. For example, to print a company logo and address at the top of every page, place the information in the first few rows of the worksheet. On the “File” menu, click “Page Setup”, click the “Sheet” tab, and then enter the rows that contain the logo and address in the “Rows to repeat at top” box. If you’re printing a wide worksheet on multiple pages, be sure to copy the repeating information to additional rows or columns as needed.

Headers and footers cannot contain graphics or links to specific cells.

Use the existing left and right margins. The left and right header and footer margins are three-quarters (.75) of an inch, regardless of the left and right margins on the page. You can simulate wider margins on a single line header or footer by typing spaces before the text in the “Left section” edit box and after the text in the “Right section” edit box. (To locate these edit boxes, click “Custom Header” or “Custom Footer” in the “Page Setup” dialog box.) The spaces you type in the “Right section” edit box won’t appear in the edit box, but will appear in the printed worksheet.

Adjust the space for the header and footer. You can adjust the distance between the top or bottom of the page and the header or footer. You can also change the distance between the data printed on a page and the header or footer. Click “Print Preview”, click the “Margins” button, and drag the margin handles to adjust the spacing.

Use two ampersand (&) characters to print an ampersand Microsoft Excel uses the ampersand to initiate codes for special formatting in the headers and footers. Therefore, to enter an ampersand in the header or footer, you must enter “&&”. For example, to include “Subcontractors & Services” in a header, enter “Subcontractors && Services”.

Sponsored Links

MICROSOFT EXCEL IGNORES THE PAGE BREAKS I SET


If your workbook is set up so that Excel fits your printed work on a specific number of pages, Excel ignores the manual page breaks you’ve set and reduces the size of the printed worksheet. (To see whether this is the case, click “Page Setup” on the “File” menu, click the “Page” tab, and check the “Fit to” option under “Scaling”.)

Use Adjust To instead of Fit To To fit the worksheet to the specified number of pages and use the manual page breaks you inserted, click “Page Setup” on the “File” menu, click the “Page” tab, and then click “Adjust to”.

Set each page as a separate print range. If “Adjust to” doesn’t produce the results you want, you can set a print area with each page selected as a separate range. Excel prints nonadjacent ranges in a print area on separate pages.

Insert a blank row or column in each location where you want a manual page break.

Select the range you want to include as the entire first page, omitting the blank row or column.

Hold down CTRL and select the range for the second page, omitting the blank rows or columns. Continue until you’ve selected all pages that you want to print.

On the “File” menu, point to “Print Area”, and then click “Set Print Area”. On the “File” menu, click “Page Setup”, click the “Page” tab, click “Fit to”, and enter the number of pages you want.

WHY DO SOME COLUMNS AND ROWS PRINT ON THE WRONG PAGE?


Column widths, row heights, page margins, and page breaks determine the number of columns and rows that print on a page.

Make margins smaller. If some columns or rows print on the next page, try decreasing the size of the “Top”, “Bottom”, “Left”, and “Right” margins. On the “File” menu, click “Page Setup”, and then click the “Margins” tab.

Adjust page breaks. You can move page breaks before you print your document. On the “View” menu, click “Page Break Preview”. Microsoft Excel displays manual page breaks as thick blue lines; automatic page breaks appear as dashed lines. You can move page breaks by dragging them left, right, up, or down.

If a column prints on the wrong page – for example, on the second page instead of the first page – drag the page break that is to the left of the column one column to the right. If a row prints on the page that follows the page where you want it to print, drag the page break that is above the row to a position below the row. Excel automatically scales the worksheet to fit the columns and rows to the page.

Make the sheet print one page wide. If you want the worksheet to be only one page wide, regardless of the number of pages, you can restrict it to the width of a single page. On the “File” menu, click “Page Setup”, and then click the “Page” tab. Under “Scaling”, click “Fit to”, type “1” in either the “page(s) wide by” box or the “tall” box, and then delete the number (if any) from the other box.

Change the orientation. If you need a few more columns to print on the same page, change the orientation to landscape. On the “File” menu, click “Page Setup”, and then click the “Page” tab. Under “Orientation”, click “Landscape”.

WHY DOESN’T EXCEL PRINT MULTIPLE PRINT AREAS ON THE SAME PAGE?


If a print area contains nonadjacent areas of a worksheet, Microsoft Excel prints each area on a separate page by default. However, you can specify that separate parts of a worksheet print on the same page by using one of the following methods.

Bring together the columns you want. To print nonadjacent columns side by side, first select the columns you don’t want to print, point to “Column” on the “Format” menu, and then click “Hide”. Set one print area that contains all of the columns, including the hidden columns.

Bring together the rows you want. To print nonadjacent rows above and below each other, first select the rows you don’t want to print, point to “Row” on the “Format” menu, and then click “Hide”. Set one print area that contains all of the rows, including the hidden rows.

Sponsored Links

I CAN’T SCALE A CHART TO PRINT THE SIZE I WANT OR PRINT MULTIPLE CHARTS ON A SINGLE PAGE?


For chart sheets, set the print size. If the chart is on a chart sheet, you can scale it when you print it. Activate the chart sheet, click “Page Setup” on the “File” menu, and then click the “Chart” tab. Under “Printed chart size”, click the scaling option you want.

If you click “Custom”, the chart will be printed at the size you make it when you drag its selection handles.

For embedded charts, drag to resize. If the chart is an embedded chart on a worksheet, resize the chart by clicking it and then dragging its selection handles.

To print charts together on a page, embed them. You cannot print multiple chart sheets on a single page. To print two or more charts on a page, create embedded charts, and then resize them to fit on a single page. You can use the “Page Break Preview” command on the “View” menu to more closely see the location of the charts in the print area.

CREATING “USER DEFINED FUNCTIONS”

        1 – CREATING “USER DEFINED FUNCTIONS”

(Valid credit card number check – MOD-10)

2 – TELEPHONE BILL AUDIT SERVICE 

CREATING “USER DEFINED FUNCTIONS”


Sponsored Links

Using visual basic macros(functions) we can develop custom functions not built into Excel. These functions can then be used in formulas like any other function currently available in Excel. To demonstrate this we will create the “CardOK” function to test the validity of a credit card number. The “CardOk” function will perform a mathematical check to determine if the number in a specific cell could be a valid credit card number (know as the MOD10 check). It makes no determination of credit worthiness, available credit or whether this is an actual credit card number.

On the “Tools” menu, click “Macro”, and then click “Macros”. Type the name of the Macro, in this example type “CardOk” then click “create”.

This should bring up the Microsoft Visual Basic Editor.

Delete all the text on that page by pressing [Ctrl + A], and then press “delete”.

Then cut and paste the following text onto the page:

Function CardOk(CardNum$) As Boolean

‘Logic statement by Richard Harris – obtained from Deja News

‘Implemented by Chui Tey – obtained from Deja News
‘Concatenation and minor correction by Phil Bornemeier
‘Modified for Excel by www.finance-analyst.com
Dim tmpCardNum$, OddSum, EvenSum, i, OneLetter$, Digit, CheckSum
‘Initialize

      tmpCardNum$ = CardNum$

CardNum$ = “”

 

    OddSum = 0

 

    EvenSum = 0

 

      ‘Reverse order of cardnum drop non-number characters

 

    For i = Len(tmpCardNum$) To 1 Step -1

 

    OneLetter$ = Mid$(tmpCardNum$, i, 1)

 

    If OneLetter$ <= “9” And OneLetter$ >= “0” Then

CardNum$ = CardNum$ + OneLetter$

End If

 

    Next i

 

    ‘Add numbers in odd positions

 

    For i = 1 To Len(CardNum$) Step 2

 

    OddSum = OddSum + Val(Mid$(CardNum$, i, 1))

 

    Next i

 

    ‘Double numbers in even positions, add up all digits, accumulate

 

    For i = 2 To Len(CardNum$) Step 2

 

    Digit = Val(Mid$(CardNum$, i, 1))

EvenSum = EvenSum + Digit * 2 + (Digit * 2 >= 10) * 9

Next i

 

    ‘Add OddSum and EvenSum

 

        CheckSum = OddSum + EvenSum

 

        ‘Check if CheckSum divisible by 10

 

        If CheckSum Mod 10 = 0 Then

 

        CardOk = True

 

        Else

 

        CardOk = False

 

    End If

End Function

Sponsored Links

Now press [Atl + Q] to quit Microsoft Visual Basic Editor and return to Excel.

The function is now ready to be used. To test it, format cell A1 for text by highlighting cell A1 then pressing [Ctrl+1], click “number”, then click “text” and finally “ok”. Then enter in cell A1 enter the following valid credit card number:

4200123456789015

In cell A2 type:

=CardOk(A1)

The value in cell A2 should be “TRUE”

You can also access this function on the “Insert” menu, click “Function”, in the “Function Category” box select “User Defined”, “CardOk” should appear in “Function Name” box. Select “CardOk” and press “ok”. In the box that pops up enter “A1” (or the cell containing the credit card number), finally press “OK”.

One final note, this function (MOD10 check) is being used by many of the smaller e-commerce sites that can’t afford to have online credit card authorization!!

Enjoy!!!.

Sponsored Links

TELEPHONE BILL AUDIT SERVICE


Marketing Partners are now welcomed!

Do you know who is reviewing your Company’s telephone bill on a monthly basis?

If your Company is like most, the answer is a scary: NO ONE!

In fact, most US corporations pay their telephone bills without scrutinizing or challenging them. Department managers caught up in the operational demands of their departments, can’t possible do a detail analysis of their bill to discover errors, correct the problems, and obtain refunds. Usually a quick month-over-month comparison of the dollar amount on the bill is all that is done, before it is approved and sent off to accounts payable for payment.

Did you know that 60% of all US Corporations are being overcharged by as much as 30% on their telephone bills? While the majority of these overcharges are a direct result of the application of incorrect rates, other overcharges include incorrect tax rates, double billings, accounting errors, etc. Even though you may have negotiated excellent rates with the phone company, it doesn’t mean your bill reflects those rates. If you haven’t performed a complete detailed analysis of your telephone bill in the last two years, chances are your company is overpaying, maybe as much as 30%.

We, Finance-Analyst.com, can identify commercial telecom billing errors and overcharges for most businesses, and then negotiate for refunds, credits and lower costs. As your consultants, we will be constantly watching your telephone bill, striving to lower it and put more money back into your income statements’ bottom line.

This service is performed at no cost to you. You pay only a percentage of what we recover for you, and if we can’t reduce your costs for you, YOU PAY NOTHING.

Sponsored Links

To get started we need the following information from you:

-A copy of your telephone contract, including the rates
AND
-The actual paper bill for the last three months, or
-Electronic bills (CDR on ftp or CD-ROM) for same period, or
-CDR’s from your telephone company.

Once we have this information, we will perform a detailed analysis to verify the accuracy of your bills. The result of our analysis will be a report on errors and the amount of your money you can expect to recoup.

Remember, telephone billing-errors will continue indefinitely until you take the necessary steps to correct them! So reply to send us an email now and a senior consultant will call you to start the process.

You owe it to yourself and your bottom line to talk with us.

Microsoft Excel

Sponsored Links

Introduction:

Microsoft Excel is a spreadsheet application hence developed by the Giants “Microsoft” for Microsoft Windows and Mac OS. It mainly contains the following features as follows:

  • Calculation
  • Pivot tables
  • Graphing tools
  • Macro programming language called “Visual Basic for Applications”

It has been very widely used and applied for all these platforms, since in the version 5 in 1993, and it has replaced the other software’s such as “Lotus 1-2-3” . Excel forms part of Microsoft Office.

The top features and benefits of Microsoft Excel are as follows:

Sponsored Links

Build great charts

Excel allows mainly the business users to unlock all their potential data, by using different formulas across a grid of cells. Required Data is inserted into individual cells when ever required in either rows or else columns.

Allowing them to perform sort, filter, and then display them in a visual presentation.

Using pie charts, graphs and clustered columns which adds meaning to data. These visualizations can add some extra emphasis to all the business reports and even marketing material. Excel recommends charts thus explained are most suitable for the type of data being presented on either the X axis and Y axis.

Use conditional formatting

Excel users can format all their spreadsheets either using different

Bolds and italics, to differentiate, highlight the most important data.
colour shades,

This function could be more useful when presenting an accounting information, “pre-tax profit” as well the “balance that which being carried or forwarded by the company.”

Users can select their appropriate coloring scheme either by using the “Quick Analysis button” and then utilizing the Formatting tab.

Help identify trends:

When we are presenting the data in the form of graphs, it is helpful to include an average lines, which explicitly shows the key trends that which are emerging from the information.

This may also help to demonstrate the required key points to the other users in a straightforward manner.

For Example, let us consider an executive from a different department during their board meeting. Hence Excel allows you to trend the lines that which have to be extended beyond their graph.

Sponsored Links

Bring data together

Excel sheets are used to bring all the necessary information from various documents together, so that they can be maintained in a single location.

As well as the raw data that which is present contains all the information even from other spreadsheets too, hence it is possible to import the text and images where ever necessary. Other remaining objects can be added obviously either by using the Insert tab, or by the additional spreadsheets.

Online Access

Excel is available even in online as a part of the “Microsoft’s Office 365 productivity suite”. This means that business leaders and employees can have access to their programs from a range of devices, i.e. even from any location.

Providing that they have web-enabled PC, Smartphone, Laptop or Tablet through which it should be possible to access an Excel, making remote and mobile working as expected.

Sponsored Links

Disadvantages:

  • A disadvantage is that we can observe that the software requires frequent updating which is most time consuming task.
  • The risk factor is that it is more likely when the software starts updating.
  • Another important thing to be remembered is that not all computer users will have the programs that which are compatible to have a view/glance the documents which have been made in the program.

Conclusion: 

Excel is a Microsoft program that which has certain formulas to ease the calculations. But the disadvantage of using it is that it minimizes or else limits the knowledge of solving different applications/ problems manually.

Excel Formulas

This Excel formula helps us how to create and make use of this formulas, with including a step by step examples. It is mainly intended for those for the users with little or no experience at all in working with spreadsheet programs such as Excel.

Sponsored Links

1. SUM

Formula: =SUM (6,6) or

=SUM (A1, B1) or

=SUM(A1:B5)

The SUM formula helps us to add 2 or more numbers together.

You can have different numbers there by separated by commas, it will add them together, you can also have cell references and you have to add  numbers in these cells and they  will add them together , or you can also have a range of cells separated by  a colon in between the cells, and it will add the numbers.

2. COUNT

Formula:

=COUNT (A1:A10)

The count formula counts the number of cells in a range that have numbers in them.

3. COUNTA

Formula:

=COUNTA (A1:A10)

It   even helps to count all the numbers of  non-empty cells in a particular  range. It will count that if cells have numbers or any other characters involved in them.

The COUNTA Formula works with all data types.

images

 

4. LEN:

Formula: =LEN (A1)

The LEN formula helps us to count all the number of characters in a give range of cells. This sometimes  also includes spaces.

Sponsored Links

doc-reverse-text-words10

Note:

10 characters, that without spaces, in between the words, where as 12 characters with spaces between the words.

5. TRIM

Formula: =TRIM (A1)

We will find  this formula to be more useful  when we run into situations, where you pull data from a database. When we  try comparing it either  using “IF statement” or else  “VLOOKUP’s.”

TRIM remove extra spaces

6. RIGHT, LEFT, MID

Formulas: = RIGHT(text, number of characters), =LEFT(text, number of characters), =MID(text, start number, number of characters).

The formula which we used here  returns  the specified number of characters as mentioned  from a given  text string.

Note:

  • RIGHT gives us the number of characters that are from the right of the text string.
  • LEFT gives us the number of characters that are from the left.
  • And MID gives us the required number of characters from the middle

7. VLOOKUP

Formula: =VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)

“ It First Looks up for a value which is in the leftmost column  in table, and then returns the value which is in the same row,from that of a  column we  specify…”.

Sponsored Links

Note: If at all possible we  use any  number for the lookup value. it makes it more and more easier to make sure that  the data  that which you are getting back would be a correct match.

8. IF Statements

Formula: =IF(logical_statement, return this if logical statement is true, return this if logical statement is false)

Example:

=IF(C3>D3, “Met Quota”, “Did Not Meet Quota”)

9. SUMIF, COUNTIF, AVERAGEIF

Formulas: =SUMIF (range, criteria, sum range), =COUNTIF(range, criteria), =AVERAGEIF(range, criteria, average range)\

10. CONCATENATE

It is the procedure of   combining the 2 different cells into one cell.

Sponsored Links

concatenate-function

Finding all the  required right Excel Formulas For The Job. There are around 316 predefined or built in functions in Excel

 

 

 

 

Excel Keyboard Shortcut Keys

Sponsored Links

   1. To format any selected object, press ctrl+1

2. To insert current date, press ctrl+;

3. To insert current time, press ctrl+shift+;

4. To repeat last action, press F4

5. To edit a cell comment, press shift + F2

6. To autosum selected cells, press alt + =

7. To see the suggest drop-down in a cell, press alt + down arrow

8. To enter multiple lines in a cell, press alt+enter

9. To insert a new sheet, press shift + F11

10. To edit active cell, press F2 (places cursor in the end)

11. To hide current row, press ctrl+9

12. To hide current column, press ctrl+0

13. To unhide rows in selected range, press ctrl+shift+9

14. To unhide columns in selected range, press ctrl+shift+0

15. To recalculate formulas, press F9

16. To select data in current region, press ctrl+shift+8

17. To see formulas in the worksheet, press ctrl+shift+` (ctrl+~)

18. While editing formulas to change the reference type from absolute to relative vice versa, press F4

19. To format a number as currency, press ctrl+shift+4 (ctrl+$)

20. To apply outline border around selected cells, press ctrl+shift+7

21. To open the macros dialog box, press alt+F8

22. To copy value from above cell, press ctrl+

23. To format current cell with comma formats, press ctrl+shift+1

24. To go to the next worksheet, press ctrl+shift+pg down

25. To go to the previous worksheet, press ctrl+shift+pg up