Microsoft Office Tutorials and References
In Depth Information
Some Useful Worksheet Functions
InRange, “<=” & num1)
End If
End With
End Function
Note that this function uses Excel’s COUNTIFS function. In fact, the CountBetween function is
essentially a wrapper that can simplify your formulas.
COUNTIFS was introduced in Excel 2007. Therefore, this function won’t work with
previous versions of Excel.
Following is an example formula that uses the CountBetween function. The formula returns the
number of cells in A1:A100 that are greater than or equal to 10 and less than or equal to 20.
=CountBetween(A1:A100,10,20)
The function accepts the two numeric argument in either order. So, this formula is equivalent to
the previous formula:
=CountBetween(A1:A100,20,10)
Using this VBA function is simpler than entering the following (somewhat confusing) formula:
=COUNTIFS(A1:A100,”>=10”,A1:A100,”<=20”)
Determining the last non-empty cell in a column or row
In this section, I present two useful functions: LastInColumn returns the contents of the last
non-empty cell in a column; LastInRow returns the contents of the last non-empty cell in a row.
Each function accepts a range as its single argument. The range argument can be a complete
column (for LastInColumn ) or a complete row (for LastInRow ). If the supplied argument isn’t a
complete column or row, the function uses the column or row of the upper-left cell in the range.
For example, the following formula returns the last value in column B:
=LastInColumn(B5)
The following formula returns the last value in row 7:
=LastInRow(C7:D9)

Search JabSto ::

Custom Search