EXCEL COUNTING FUNCTIONS 17TH JAN 2014

Posted on January 17, 2015 by Sanjay Bakshi

• COUNT
  • Returns the number of cells in a range that contain a numeric value
• COUNTA
  • Returns the number of nonblank cells in a range
• COUNTBLANK
  • Returns the number of blank cells in a range
• COUNTIF
  • Returns the number of cells in a range that meet a single specified criterion
• COUNTIFS
  • Returns the number of cells in a range that meet one or more specified criterion
• DCOUNT
  • Counts the number of records in a worksheet database that meet specified criteria
• DCOUNTA
  • Counts the number of nonblank records in a worksheet database that meet specified criteria
• DEVSQ
  • Returns the sum of squares of deviations of data points from the sample mean; used  primarily in statistical formulas
• DSUM
  • Returns the sum of a column of values in a worksheet database that meet specified criteria
• FREQUENCY
  • Calculates how often values occur within a range of values and returns a vertical array of
    numbers; used only in a multicell array formula
• SUBTOTAL
  • When used with a first argument of 2 or 3, returns a count of cells that comprise a subtotal;
    when used with a first argument of 9, returns the sum of cells that comprise a subtotal
• SUM
  • Returns the sum of its arguments
• SUMIF
  • Returns the sum of cells in a range that meet a specified criterion
• SUMIFS
  • Returns the sum of the cells in a range that meet one or more specified criterion
• SUMPRODUCT
  • Multiplies corresponding cells in two or more ranges and returns the sum of those products
• SUMSQ
  • Returns the sum of the squares of its arguments; used primarily in statistical formulas
• SUMX2PY2
  • Returns the sum of the sum of squares of corresponding values in two ranges; used primarily
    in statistical formulas
• SUMXMY2
  • Returns the sum of squares of the differences of corresponding values in two ranges; used primarily in statistical formulas
• SUMX2MY2
  • Returns the sum of the differences of squares of corresponding values in two ranges; used primarily in statistical formulas

Keep Reading and Do Share

Sanjay Bakshi

mesanjaybakshi@gmail.com

Excel Formula Fact 15th Jan 2015

Every left parenthesis, of course, must have a matching right parenthesis. If you have
many levels of nested parentheses, you may find it difficult to keep them straight.
Fortunately, Excel lends a hand in helping you match parentheses. When editing a formula,
matching parentheses are colored the same, although the colors can be difficult
to distinguish if you have a lot of parentheses. Also, when the cursor moves over a
parenthesis, Excel momentarily displays the parenthesis and its matching parenthesis in
bold. This lasts for less than a second, so watch carefully.

 

 

Keep Learning and do Share

Sanjay Bakshi (mesanjaybakshi@gmail.com)

Entering the Formula 07th Jan 2015

When you type an equal sign into an empty cell, Excel assumes that you are entering a formula because a formula always begins with an equal sign. Excel’s accommodating nature also permits you to begin your formula with a minus sign or a plus sign. However, Excel always inserts the leading equal sign after you enter the formula.
As a concession to former Lotus 1-2-3 users, Excel also allows you to use an “at” symbol (@) to begin a formula that starts with a function. For example, Excel accepts either of the following formulas:

=sum(A1:A100)  @sum(A1:A100)
However, after you enter the second formula, Excel replaces the @ symbol with an equal sign. You can enter a formula into a cell in one of two ways: Enter it manually, or enter it by pointing to cells that are used in the formula.

A formula can consist of up to about 8000 chars. In the unlikely event that you need to create a formula that exceeds this limit, we must break the formula up into multiple formulas. Other than this you can opt for VBA.


Keep Learning and do share
Sanjay Bakshi
mesanjaybakshi@gmail.com
“Knowledge Increases by Sharing but not by Saving” ..Kamari aka Lyrikal

Work Sheet Note 05th Jan 2015

• To enter data without pressing the arrow keys, enable the After Pressing Enter, Move
  Selection option on the Advanced tab of the Excel Options dialog box (which you access from the Office➜Excel Options command). You can also choose the direction that you want to go.
• To enter the same data in all cells within a range, select the range, enter the information into the active cell, and then press Ctrl+Enter.
• To fill a range with increments of a single value, press Ctrl while you drag the fill handle at the lower-right corner of the cell.
• To create a custom Auto Fill list, select the Edit Custom Lists button on the Popular tab of the Excel Options dialog box.
• To copy a cell without incrementing, drag the fill handle at the lower-right corner of the selection; or, press Ctrl+D to copy down.
• To automatically format a cell with the currency format, type your currency symbol before the value.
• To enter a value in percent format, type a percent sign after the value.
• To insert the current date, press Ctrl+; (semicolon). To enter the current time into a cell,press Ctrl+Shift+;.

Keep Learning and do Share
Sanjay Bakshi
mesanjaybakshi@gmail.com

Work Sheet Note 04th Jan 2015

By default, every new workbook starts out with three worksheets. You can easily add a new sheet when necessary, so you really don’t need to start with three sheets. You may want to change this default to a single sheet. To change this option, choose the File➜Options command, click the General tab, and change the setting for the option labeled Include This Many Sheets.

Keep Learning and do share

Sanjay Bakshi

mesanjaybakshi@gmail.com

Spreadsheet History

VisiCalc - Dream become true

In today’s environment spreadsheet comprise a huge business and it is one of the best BI Tool in current market. In the pre spreadsheet days we remember we used to spend many hours to do large calculation which takes now seconds.

Do you know that Dan Bricklin and Bob Frankston was involved in the creation of first ever electronic spreadsheet called VisiCalc. They have written this program for Apple II computer.

VisiCalc has got the attention immediately in the market and many companies purchased the Apple II for the sole purpose of developing their budget with the help of VisiCalc.

Microsoft Entry (Excel2)

In 1982, Microsoft released its first spreadsheet – Multiplan. This product was ported to several other platforms, including Apple II, Apple III, XENIX and MS-Dos. Excel 2 was running on Macintosh Paltform

The original version of Excel “Excel 2” appeared in late 1987.

(Photo Courtesy Microsoft Corporation)

 

Microsoft Entry (Excel3)

Excel 3 came into picture in the end of 1990 and it is released for Windows. This version offered significant improvement in appearance and features. It includes toolbars, drawing, worksheet outline, add in support, 3 d charts, workgroup editing and many more.

 

Microsoft Entry (Excel4)

Excel 4 came into picture in 1992. This version made quite an impact on the marketplace as Windows increased in popularity. It boasted lots of new features and usability enhancements that made it easier for beginners to get up to speed quickly.

 

Microsoft Entry (Excel5)

In early 1994, Excel 5 appeared on the scene. This version introduced tons of new features, including multi sheet workbooks and the new Visual Basic for Applications (VBA) macro language.

 

Microsoft Entry (Excel95)

Excel 95 came in summer of 1995. It was proved to be significant because it presented the first version to use more advanced 32 bit code.

Microsoft Entry (Excel97)

Experts believe that Excel 97 offered the most significant upgrade ever. The toolbars and menu took a new look and for Macro Developer VBA moved up on the scale.

Microsoft Entry (Excel2000)

Excel 2000 came into existence in June 1999. Excel 2000 offered several minor enhancements, but the most significant advancement was the ability to use HTML as an alternative format.

 

 

Microsoft Entry (Excel2002)

Do you know Excel 2002 also known as Excel XP and it was released in June 2001 and it was a part of Microsoft Office XP. The significant features were the capability of to save your work when Excel Crashes and also recover corrupt workbook files that you may have abandoned long ago. Excel 2002 also added background formula for error checking and new formula debugging tool.

Microsoft Entry (Excel2003)

Excel 2003 was released in fall of 2003. This version had very new features. The most significant features were the ability to import and export XML and map the data to specific cells. It also introduced the concepts of the list, a specially designated range of cells.

 

Microsoft Entry (Excel2007)

Excel 2007 was released in early 2007. This series has represented the most significance changes after Excl 97. Microsoft has changed the default file format in this version. The default format was based on XML concept.

 

Microsoft Entry (Excel2010)

Excel 2007 was released in early 2007. This series has represented the most significance changes after Excl 97. Microsoft has changed the default file format in this version. The default format was based on XML concept.

 Keep Reading & Do Share

mesanjaybakshi@gmail.com