leo thanx,

i m gettting this error on counting B

#NA erro

any clue ?

regards
Jay
Leo Heuser wrote:
"Jay" skrev i en meddelelse
oups.com...
thanx leo,


You're welcome, Jay.


i m getting #VALUE error in the maximum lost % , i deleted other
worksheet its giving this error now ? what to do now


There was an error in my function. It was not possible to
use data on a different sheet from the one, where the formula
resided. This has been rectified below. Sorry about that.
Please see, if this was, what caused the #VALUE error.


Function MaxBank(BankRange As Range) As Double
'Leo Heuser, Sep. 18, 2006, ver. 1.01
Dim BankRangeValue As Variant
Dim Counter As Long
Dim Counter1 As Long
Dim CountElement As Long
Dim GetMaxiValue As Double
Dim GetMiniValue As Double
Dim MaxiBankRow() As Long
Dim MiniBankValue() As Double
Dim Result() As Double
Dim SheetName As String

Set BankRange = BankRange.Columns(1)
SheetName = BankRange.Parent.Name

If IsEmpty(BankRange.Cells(1, 1). _
Offset(BankRange.Rows.Count)) Then
Set BankRange = Range(BankRange.Cells(1, 1), _
Sheets(SheetName).Cells(ActiveSheet.Rows.Count, _
BankRange.Column).End(xlUp))
End If

If BankRange.Rows.Count = 1 Then
MaxBank = 0
GoTo Finito
End If

BankRangeValue = BankRange.Value

GetMaxiValue = BankRangeValue(1, 1)

ReDim MaxiBankRow(1 To UBound(BankRangeValue, 1))

CountElement = 1

MaxiBankRow(CountElement) = CountElement

For Counter = 2 To UBound(BankRangeValue, 1) - 1
If BankRangeValue(Counter, 1) GetMaxiValue Then
If BankRangeValue(Counter, 1) _
BankRangeValue(Counter + 1, 1) Then
CountElement = CountElement + 1
GetMaxiValue = BankRangeValue(Counter, 1)
MaxiBankRow(CountElement) = Counter
End If
End If
Next Counter

MaxiBankRow(CountElement + 1) = Counter

ReDim Preserve MaxiBankRow(1 To CountElement + 1)
ReDim MiniBankValue(1 To UBound(MaxiBankRow))
ReDim Result(1 To UBound(MaxiBankRow))

For Counter = 1 To UBound(MiniBankValue) - 1
GetMiniValue = BankRangeValue(MaxiBankRow(Counter), 1)

For Counter1 = MaxiBankRow(Counter) To MaxiBankRow(Counter + 1)
If BankRangeValue(Counter1, 1) < GetMiniValue Then
GetMiniValue = BankRangeValue(Counter1, 1)
End If
Next Counter1

MiniBankValue(Counter) = GetMiniValue
Next Counter

MiniBankValue(Counter) = _
BankRangeValue(UBound(BankRangeValue, 1), 1)

For Counter = 1 To UBound(Result)
Result(Counter) = (BankRangeValue(MaxiBankRow(Counter), 1) - _
MiniBankValue(Counter)) / _
BankRangeValue(MaxiBankRow(Counter), 1)
Next Counter

MaxBank = Application.Max(Result)

Finito:

End Function




Last One : how do i count the number of times B occured in a particular
month ?

A
A
B
B
B



If dates in A2:A100 and Bs in C2:C100

For January:
=SUMPRODUCT((MONTH(A2:A100)=1)*(C2:C100="b"))

For March:
=SUMPRODUCT((MONTH(A2:A100)=3)*(C2:C100="b"))


Regards
Leo Heuser