Excel Garage

Count function Summary  The Excel COUNT function returns the count of values that are numbers, generally cells that contain numbers. Values can be supplied as constants, cell references, or ranges. Purpose Count numbers Return value  Count of numeric values Syntax   =COUNT (value1, [value2], ...) Arguments   value1 - An item, cell reference, or range. value2 - [optional] An item, cell reference, or range. Usage notes   The COUNT function returns the count of numeric values in the list of supplied arguments. Arguments can be individual items, cell references, or ranges up to a total of 255 arguments. All numbers including negative values, percentages, dates, fractions, and time are counted. Empty cells and text values are ignored. Example #1 - range In the example shown, COUNT is set up to count numbers in the range B5:B11: =COUNT(B5:B11) // returns 4 COUNT returns 4, since there are 4 numeric values in the range B5:B11. Text values and blank cells are ignored. E...

METHOD - 1  VLOOKUP Formula =VLOOKUP(A1,CHOOSE({3,2,1},col1,col2,col3),3,0)    Explanation  To reverse a VLOOKUP – i.e. to find the original lookup value using a VLOOKUP formula result – you can use a tricky formula based on the CHOOSE function, or more straightforward formulas based on INDEX and MATCH or XLOOKUP as explained below. In the example shown, the formula in H10 is: =VLOOKUP(G10,CHOOSE({3,2,1},B5:B8,C5:C8,D5:D8),3,0) With this setup, VLOOKUP finds the option associated with a cost of 3000, and returns "C". Note: this is a more advanced topic. If you are just getting started with VLOOKUP, start here. Introduction A key limitation of VLOOKUP is it can only lookup values to the right. In other words, the column with lookup values must be to the left of the values you want to retrieve with VLOOKUP.  As a result, with standard configuration, there is no way to use VLOOKUP to "look left" and reverse the original lookup. From the standpoint of VLOOKUP, we ca...

If you have a formula in a worksheet, and the cell referenced by the formula is blank, then the formula still returns a zero value. For instance, if you have the formula =A3, then the formula returns the contents of cell A3, unless cell A3 is blank. In that case, the formula returns a value of zero. This seems to be related to the idea that it is impossible for a formula to return a blank value, when "blank" is used synonymously with "empty." You can, however, expand your formula a bit so that it returns an empty string. Instead of using =A3 as your formula, you would use the following: =IF(ISBLANK(A3),"",A3) This formula uses ISBLANK, which returns either True or False, depending on whether the referenced cell (A3) is blank or not. The IF function then returns an empty string ("") if A3 is blank, or it uses the value in A3 if A3 is not blank. Regardless of what the formula returns, you can still use its result in other formulas, and it will work...

Function Returns a subtotal in a list or database. It is generally easier to create a list with subtotals by using the Subtotal command in the Outline group on the Data tab in the Excel desktop application. Once the subtotal list is created, you can modify it by editing the SUBTOTAL function." Syntax =SUBTOTAL(function_num,ref1,[ref2],...) The SUBTOTAL function syntax has the following arguments:" Example Using subtotal sum function 

Syntax =IF(ISODD(Cell_number),"ODD","EVEN") Example Excel formula used is   =IF(ISODD(F2),"ODD","EVEN")

SUM, SUMIF, SUMIFS SUM FUNCTION The Excel SUM function returns the sum of values supplied. These values can be numbers, cell references, ranges, arrays, and constants, in any combination. SUM can handle up to 255 individual arguments. Purpose - Add numbers together Syntax =SUM (number1, [number2], [number3], ...) Example To sum yellow highlighted D2 to D 13 =SUM(D2:D13) Result: 7601 ************************************** SUMIF FUNCTION  The Excel SUMIF function returns the sum of cells that supplied criteria. Criteria can be applied to dates, numbers, and text using logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. Purpose - Sum numbers in a range that meet supplied criteria Syntax =SUMIF (range, criteria, [sum_range]) Example 1.Total count of Area named "Badami" =SUMIF(C2:C13,"Badami",D2:D13 ) Result: 2182

Basic Excel Formulas Guide Mastering the basic Excel formulas is critical for beginners to become highly proficient in financial analysis. Microsoft Excel is considered the industry standard piece of software in data analysis. Microsoft’s spreadsheet program also happens to be one of the most preferred software by investment bankers and financial analysts in data processing, financial modeling, and presentation. This guide will provide an overview and list of basic Excel functions. Basic Terms in Excel There are two basic ways to perform calculations in Excel: Formulas and Functions. 1. Formulas In Excel, a formula is an expression that operates on values in a range of cells or a cell. For example, =A1+A2+A3, which finds the sum of the range of values from cell A1 to cell A3. 2. Functions Functions are predefined formulas in Excel. They eliminate laborious manual entry of formulas while giving them human-friendly names. For example: =SUM(A1:A3). The function sums all the valu...