Excel Digest

  Excel help for the rest of us

Archive for September, 2008

28 Sep

How to view multiple worksheets at the same time

To view multiple worksheets at the same time follow these steps:
1. Open your workbook.
2. In the Window menu click New Window.
3. In the Window menu click Arrange and select an option (Tiled, horizontal .. etc). Now you will have two windows showing the same worksheet.
4. Click on any one of the two windows and select [...]

23 Sep

Function to return the address of a lookup value

If you look at many of Excel lookup functions you will find out that they either return a value or a position of an item. There is no function that returns the address where a lookup value is found.
Below is a code for a user defined function that takes two arguments: a lookup value and [...]

18 Sep

How to search for a text in a formula or a comment

It may be interesting to know that the Find option in Excel Edit menu is not limited to finding cell values, but it can also be used to search for text in a formula or a comment.
To search for a text in a formula follow these steps.
1. In the Edit menu click Find.
2. The Find [...]

13 Sep

Summary of Excel count functions

COUNT
Counts the number of cells that contain numbers within a given range. It can also accept a list of  arguments other than cell ranges and counts numbers within that list of arguments. Use COUNT to find out how many cells or arguments that have numeric values.
Syntax
COUNT(value1,value2,…)
Read more …

COUNTA
Counts the number of cells that are not [...]

08 Sep

Count numbers greater than or less than a number

To count numbers greater than or less than a given number, you use the COUNTIF function.
As asn example  if you have a list of numbers in a range of cells B1:B20 and you want to know how many of those cells have a value greater than 100, then you use COUNTIF as follows:
COUNTIF(B1:B20,”>100″).
On the other [...]

05 Sep

How to use the COUNTIF function

The COUNTIF function counts the number of cells within a given range that meet a given criteria.
Syntax
COUNTIF(range,criteria)
Range: The range of cells from which you want to count cells.
Criteria: The criteria upon which you want to base your count. It defines which cells will be counted.
The criteria can b a number, expression, cell reference, [...]

02 Sep

How to count cells that contain data

To count cells, within a given range or list of arguments, that contains data, you use the COUNTA function.
Data in this context includes error values, spaces and empty text (””).
Syntax
COUNTA(value1,value2,…)
Examples:
COUNTA(A1:A20) :counts any nonblank cells in a range of cells.
COUNTA(A1:A20, 7,”ABC”) :counts nonblank in a range of cells plus the second argument (7), and the third [...]

© 2008 Excel Digest | Entries (RSS) and Comments (RSS)

GPS Reviews and news from GPS Gazettewordpress logo