Hi,
I have an excel sheet

how to find out the maximum number of times a particular text has been
appeared continuously in a column ?

say i have column like this

A
A
A
B
B
B
A
B
B

I would like to find out whats the maximum number of times the B has
appeared continously in a column with out any other value in between ,
in this case 3 times B appeared continously.

how do i do it the same in excel

any help is appreciated

Regards
Jay

Hi Jay,

Try:

'=============
Public Function ConsecutiveCount(sStr As String, rng As Range) As Long
Dim rCell As Range
Dim iCtr As Long, jCtr As Long

For Each rCell In rng.Cells
With rCell
If .Value = sStr Then
iCtr = iCtr + 1
jCtr = Application.Max(jCtr, iCtr)
Else
iCtr = 0
End If
End With
Next rCell
ConsecutiveCount = jCtr
End Function
'<<=============


---
Regards,
Norman



"Jay" wrote in message
ups.com...
Hi,
I have an excel sheet

how to find out the maximum number of times a particular text has been
appeared continuously in a column ?

say i have column like this

A
A
A
B
B
B
A
B
B

I would like to find out whats the maximum number of times the B has
appeared continously in a column with out any other value in between ,
in this case 3 times B appeared continously.

how do i do it the same in excel

any help is appreciated

Regards
Jay

Hi Jay,

To optionally allow for case sensitivity, try the following version:

'=============
Public Function ConsecutiveCount(sStr As String, rng As Range, _
Optional blCaseSensitive As Boolean = False) As Long
Dim rCell As Range
Dim iCtr As Long, jCtr As Long

For Each rCell In rng.Cells
With rCell
If blCaseSensitive Then
If .Value = sStr Then
iCtr = iCtr + 1
jCtr = Application.Max(jCtr, iCtr)
Else
iCtr = 0
End If
Else
If UCase(.Value) = UCase(sStr) Then
iCtr = iCtr + 1
jCtr = Application.Max(jCtr, iCtr)
Else
iCtr = 0
End If
End If
End With
Next rCell
ConsecutiveCount = jCtr
End Function
'<<=============


---
Regards,
Norman

"Jay" skrev i en meddelelse
ups.com...
Hi,
I have an excel sheet

how to find out the maximum number of times a particular text has been
appeared continuously in a column ?

say i have column like this

A
A
A
B
B
B
A
B
B

I would like to find out whats the maximum number of times the B has
appeared continously in a column with out any other value in between ,
in this case 3 times B appeared continously.

how do i do it the same in excel

any help is appreciated

Regards
Jay


Hi Jay

Here's a formula solution, assuming the text to look for is in F1, and the
cells to investigate are A1:A25. If present, the text appears as the only
entry in a cell.

=MAX(FREQUENCY(IF(A1:A25=F1,COUNTIF(OFFSET(A1,,,RO W(INDIRECT(
"1:"&ROWS(A1:A25)))),"<"&F1)),ROW(INDIRECT("1:"&R OWS(A1:A25)))-1))

The formula must be entered as one line and finished with
<Shift<Ctrl<Enter, also if edited later.
If done correctly, Excel will display the formula in the formula bar
enclosed in braces { }. Don't enter these braces yourself, they're Excel's
way of showing, that the formula is an array formula.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

Leo thanx for your reply

but i m not able to execute the problem am getting error

table is

A
A
A
B
A
B
B
B
B
A

need to find max how many times the B has appeared continuously with
out any break

abvoe case B appeared 4 times in a continously .. this has to be
displyed on other cell.
where should i put that formula in ?

regards
Jay
Leo Heuser wrote
:
"Jay" skrev i en meddelelse
ups.com...
Hi,
I have an excel sheet

how to find out the maximum number of times a particular text has been
appeared continuously in a column ?

say i have column like this

A
A
A
B
B
B
A
B
B

I would like to find out whats the maximum number of times the B has
appeared continously in a column with out any other value in between ,
in this case 3 times B appeared continously.

how do i do it the same in excel

any help is appreciated

Regards
Jay


Hi Jay

Here's a formula solution, assuming the text to look for is in F1, and the
cells to investigate are A1:A25. If present, the text appears as the only
entry in a cell.

=MAX(FREQUENCY(IF(A1:A25=F1,COUNTIF(OFFSET(A1,,,RO W(INDIRECT(
"1:"&ROWS(A1:A25)))),"<"&F1)),ROW(INDIRECT("1:"&R OWS(A1:A25)))-1))

The formula must be entered as one line and finished with
<Shift<Ctrl<Enter, also if edited later.
If done correctly, Excel will display the formula in the formula bar
enclosed in braces { }. Don't enter these braces yourself, they're Excel's
way of showing, that the formula is an array formula.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

Hi Jay

enter "b" (or "B") without quotes in F1, and enter
the formula in any cell you want. Remember to finish
the formula with <Shift<Ctrl<Enter (all 3 keys
pressed at the same time i.e. press <Shift and <Ctrl
and while holding them press <Enter). Release all
3 keys.
If you copy the formula from the post, select
the cell you want the formula to reside in, click the formula
bar and press <Ctrlv to paste it to the bar.
Go to the end of the first line and press <Delete to connect
the 2 lines and finish with <Shift<Ctrl<Enter

Leo Heuser

Followup to newsgroup only please.
"Jay" skrev i en meddelelse
ups.com...
Leo thanx for your reply

but i m not able to execute the problem am getting error

table is

A
A
A
B
A
B
B
B
B
A

need to find max how many times the B has appeared continuously with
out any break

abvoe case B appeared 4 times in a continously .. this has to be
displyed on other cell.
where should i put that formula in ?

regards
Jay
Leo Heuser wrote
:
"Jay" skrev i en meddelelse
ups.com...
Hi,
I have an excel sheet

how to find out the maximum number of times a particular text has been
appeared continuously in a column ?

say i have column like this

A
A
A
B
B
B
A
B
B

I would like to find out whats the maximum number of times the B has
appeared continously in a column with out any other value in between ,
in this case 3 times B appeared continously.

how do i do it the same in excel

any help is appreciated

Regards
Jay


Hi Jay

Here's a formula solution, assuming the text to look for is in F1, and
the
cells to investigate are A1:A25. If present, the text appears as the only
entry in a cell.

=MAX(FREQUENCY(IF(A1:A25=F1,COUNTIF(OFFSET(A1,,,RO W(INDIRECT(
"1:"&ROWS(A1:A25)))),"<"&F1)),ROW(INDIRECT("1:"&R OWS(A1:A25)))-1))

The formula must be entered as one line and finished with
<Shift<Ctrl<Enter, also if edited later.
If done correctly, Excel will display the formula in the formula bar
enclosed in braces { }. Don't enter these braces yourself, they're
Excel's
way of showing, that the formula is an array formula.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

Leo

still its giving error...

I entered the way you mentioned here changing the F1 and Cell Range
from A1:A25 to L9:L64999

do i need to change anything in the quote

looking forward for ur reply
regards
Jay
Leo Heuser wrote:
Hi Jay

enter "b" (or "B") without quotes in F1, and enter
the formula in any cell you want. Remember to finish
the formula with <Shift<Ctrl<Enter (all 3 keys
pressed at the same time i.e. press <Shift and <Ctrl
and while holding them press <Enter). Release all
3 keys.
If you copy the formula from the post, select
the cell you want the formula to reside in, click the formula
bar and press <Ctrlv to paste it to the bar.
Go to the end of the first line and press <Delete to connect
the 2 lines and finish with <Shift<Ctrl<Enter

Leo Heuser

Followup to newsgroup only please.
"Jay" skrev i en meddelelse
ups.com...
Leo thanx for your reply

but i m not able to execute the problem am getting error

table is

A
A
A
B
A
B
B
B
B
A

need to find max how many times the B has appeared continuously with
out any break

abvoe case B appeared 4 times in a continously .. this has to be
displyed on other cell.
where should i put that formula in ?

regards
Jay
Leo Heuser wrote
:
"Jay" skrev i en meddelelse
ups.com...
Hi,
I have an excel sheet

how to find out the maximum number of times a particular text has been
appeared continuously in a column ?

say i have column like this

A
A
A
B
B
B
A
B
B

I would like to find out whats the maximum number of times the B has
appeared continously in a column with out any other value in between ,
in this case 3 times B appeared continously.

how do i do it the same in excel

any help is appreciated

Regards
Jay


Hi Jay

Here's a formula solution, assuming the text to look for is in F1, and
the
cells to investigate are A1:A25. If present, the text appears as the only
entry in a cell.

=MAX(FREQUENCY(IF(A1:A25=F1,COUNTIF(OFFSET(A1,,,RO W(INDIRECT(
"1:"&ROWS(A1:A25)))),"<"&F1)),ROW(INDIRECT("1:"&R OWS(A1:A25)))-1))

The formula must be entered as one line and finished with
<Shift<Ctrl<Enter, also if edited later.
If done correctly, Excel will display the formula in the formula bar
enclosed in braces { }. Don't enter these braces yourself, they're
Excel's
way of showing, that the formula is an array formula.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

here how i entered the forumula

MAX(FREQUENCY(IF(L9:L64999=B,COUNTIF(OFFSET(L9,,,R OW(INDIRECT(1:"&ROWS(L9:L64999)))),"<"&B)),ROW(IN DIRECT("1:"&ROWS(L9:L64999)))-1))

regrds

Jay


Jay wrote:
Leo

still its giving error...

I entered the way you mentioned here changing the F1 and Cell Range
from A1:A25 to L9:L64999

do i need to change anything in the quote

looking forward for ur reply
regards
Jay
Leo Heuser wrote:
Hi Jay

enter "b" (or "B") without quotes in F1, and enter
the formula in any cell you want. Remember to finish
the formula with <Shift<Ctrl<Enter (all 3 keys
pressed at the same time i.e. press <Shift and <Ctrl
and while holding them press <Enter). Release all
3 keys.
If you copy the formula from the post, select
the cell you want the formula to reside in, click the formula
bar and press <Ctrlv to paste it to the bar.
Go to the end of the first line and press <Delete to connect
the 2 lines and finish with <Shift<Ctrl<Enter

Leo Heuser

Followup to newsgroup only please.
"Jay" skrev i en meddelelse
ups.com...
Leo thanx for your reply

but i m not able to execute the problem am getting error

table is

A
A
A
B
A
B
B
B
B
A

need to find max how many times the B has appeared continuously with
out any break

abvoe case B appeared 4 times in a continously .. this has to be
displyed on other cell.
where should i put that formula in ?

regards
Jay
Leo Heuser wrote
:
"Jay" skrev i en meddelelse
ups.com...
Hi,
I have an excel sheet

how to find out the maximum number of times a particular text has been
appeared continuously in a column ?

say i have column like this

A
A
A
B
B
B
A
B
B

I would like to find out whats the maximum number of times the B has
appeared continously in a column with out any other value in between ,
in this case 3 times B appeared continously.

how do i do it the same in excel

any help is appreciated

Regards
Jay


Hi Jay

Here's a formula solution, assuming the text to look for is in F1, and
the
cells to investigate are A1:A25. If present, the text appears as the only
entry in a cell.

=MAX(FREQUENCY(IF(A1:A25=F1,COUNTIF(OFFSET(A1,,,RO W(INDIRECT(
"1:"&ROWS(A1:A25)))),"<"&F1)),ROW(INDIRECT("1:"&R OWS(A1:A25)))-1))

The formula must be entered as one line and finished with
<Shift<Ctrl<Enter, also if edited later.
If done correctly, Excel will display the formula in the formula bar
enclosed in braces { }. Don't enter these braces yourself, they're
Excel's
way of showing, that the formula is an array formula.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

"Jay" skrev i en meddelelse
oups.com...
Leo

still its giving error...

I entered the way you mentioned here changing the F1 and Cell Range
from A1:A25 to L9:L64999

do i need to change anything in the quote

looking forward for ur reply
regards
Jay


Hi Jay

You are welcome to attach a copy of your workbook to
a personal mail, and I'll take a look at it. Which version
of Excel are you using?

leo.heuser at adslhome.dk

Regards
Leo Heuser

If you enter B *directly* into the formula, you have to tell
Excel, that it's a string ("B"), so the formula becomes:

=MAX(FREQUENCY(IF(L9:L64999="B",COUNTIF(OFFSET(L9, ,,ROW(INDIRECT(1:"&ROWS(L9:L64999)))),"<"&"B")),R OW(INDIRECT("1:"&ROWS(L9:L64999)))-1))

If you get errors of some kind, please disclose
what the error says.

Leo Heuser


Followup to newsgroup only please.
"Jay" skrev i en meddelelse
ups.com...
here how i entered the forumula

MAX(FREQUENCY(IF(L9:L64999=B,COUNTIF(OFFSET(L9,,,R OW(INDIRECT(1:"&ROWS(L9:L64999)))),"<"&B)),ROW(IN DIRECT("1:"&ROWS(L9:L64999)))-1))

regrds

Jay

error is :

Formula You typed Contains an error

For information about fixing common formula problems Click Help
To Get Assistance in Entering a function click OK then click Function
on the Insert Menu
If you are not trying to enter a formula , avoid using an equal sign or
minus sign or preded it with a singl quotation mark ( ' )

regards
Jay
Leo Heuser wrote:
If you enter B *directly* into the formula, you have to tell
Excel, that it's a string ("B"), so the formula becomes:

=MAX(FREQUENCY(IF(L9:L64999="B",COUNTIF(OFFSET(L9, ,,ROW(INDIRECT(1:"&ROWS(L9:L64999)))),"<"&"B")),R OW(INDIRECT("1:"&ROWS(L9:L64999)))-1))

If you get errors of some kind, please disclose
what the error says.

Leo Heuser


Followup to newsgroup only please.
"Jay" skrev i en meddelelse
ups.com...
here how i entered the forumula

MAX(FREQUENCY(IF(L9:L64999=B,COUNTIF(OFFSET(L9,,,R OW(INDIRECT(1:"&ROWS(L9:L64999)))),"<"&B)),ROW(IN DIRECT("1:"&ROWS(L9:L64999)))-1))

regrds

Jay

Leo ,

Its working perfectly fine now after tweaking

Thankyou very much for yur help

Jay
Jay wrote:
error is :

Formula You typed Contains an error

For information about fixing common formula problems Click Help
To Get Assistance in Entering a function click OK then click Function
on the Insert Menu
If you are not trying to enter a formula , avoid using an equal sign or
minus sign or preded it with a singl quotation mark ( ' )

regards
Jay
Leo Heuser wrote:
If you enter B *directly* into the formula, you have to tell
Excel, that it's a string ("B"), so the formula becomes:

=MAX(FREQUENCY(IF(L9:L64999="B",COUNTIF(OFFSET(L9, ,,ROW(INDIRECT(1:"&ROWS(L9:L64999)))),"<"&"B")),R OW(INDIRECT("1:"&ROWS(L9:L64999)))-1))

If you get errors of some kind, please disclose
what the error says.

Leo Heuser


Followup to newsgroup only please.
"Jay" skrev i en meddelelse
ups.com...
here how i entered the forumula

MAX(FREQUENCY(IF(L9:L64999=B,COUNTIF(OFFSET(L9,,,R OW(INDIRECT(1:"&ROWS(L9:L64999)))),"<"&B)),ROW(IN DIRECT("1:"&ROWS(L9:L64999)))-1))

regrds

Jay

leo,
i have one more request dont mind !

how do i put last cells value in another cell ?

as i keep on adding the rows so last cells value changes i want the
same value to be displayed on another cell how do i do this ?

regards
Jay
Jay wrote:
error is :

Formula You typed Contains an error

For information about fixing common formula problems Click Help
To Get Assistance in Entering a function click OK then click Function
on the Insert Menu
If you are not trying to enter a formula , avoid using an equal sign or
minus sign or preded it with a singl quotation mark ( ' )

regards
Jay
Leo Heuser wrote:
If you enter B *directly* into the formula, you have to tell
Excel, that it's a string ("B"), so the formula becomes:

=MAX(FREQUENCY(IF(L9:L64999="B",COUNTIF(OFFSET(L9, ,,ROW(INDIRECT(1:"&ROWS(L9:L64999)))),"<"&"B")),R OW(INDIRECT("1:"&ROWS(L9:L64999)))-1))

If you get errors of some kind, please disclose
what the error says.

Leo Heuser


Followup to newsgroup only please.
"Jay" skrev i en meddelelse
ups.com...
here how i entered the forumula

MAX(FREQUENCY(IF(L9:L64999=B,COUNTIF(OFFSET(L9,,,R OW(INDIRECT(1:"&ROWS(L9:L64999)))),"<"&B)),ROW(IN DIRECT("1:"&ROWS(L9:L64999)))-1))

regrds

Jay

Leo,

i have stock market investment spread sheet where in

say : Initial Investment : $1000.00

After Day 1 Trading : $1020.00
After Day 2 Trading : $1068.00
After Day 3 Trading : $1120.00
After Day 4 Trading : $1080.00
After Day 5 Trading : $980.00
After Day 6 Trading : $1020.00
After Day 7 Trading : $1060.00

I would like to calculate and display this in another cell how much max
% we have lost in an sequence against our maximum profit max :
$1120.00

Forumula i have is $1120-$980/$1120 * 100 = 12.5% i want this to be
displayed in another cell. only maximum % lost in any trading sequence
just like previous problem

thanx in advance
regards
Jay
Jay wrote:
leo,
i have one more request dont mind !

how do i put last cells value in another cell ?

as i keep on adding the rows so last cells value changes i want the
same value to be displayed on another cell how do i do this ?

regards
Jay
Jay wrote:
error is :

Formula You typed Contains an error

For information about fixing common formula problems Click Help
To Get Assistance in Entering a function click OK then click Function
on the Insert Menu
If you are not trying to enter a formula , avoid using an equal sign or
minus sign or preded it with a singl quotation mark ( ' )

regards
Jay
Leo Heuser wrote:
If you enter B *directly* into the formula, you have to tell
Excel, that it's a string ("B"), so the formula becomes:

=MAX(FREQUENCY(IF(L9:L64999="B",COUNTIF(OFFSET(L9, ,,ROW(INDIRECT(1:"&ROWS(L9:L64999)))),"<"&"B")),R OW(INDIRECT("1:"&ROWS(L9:L64999)))-1))

If you get errors of some kind, please disclose
what the error says.

Leo Heuser


Followup to newsgroup only please.
"Jay" skrev i en meddelelse
ups.com...
here how i entered the forumula

MAX(FREQUENCY(IF(L9:L64999=B,COUNTIF(OFFSET(L9,,,R OW(INDIRECT(1:"&ROWS(L9:L64999)))),"<"&B)),ROW(IN DIRECT("1:"&ROWS(L9:L64999)))-1))

regrds

Jay

"Jay" skrev i en meddelelse
oups.com...
leo,
i have one more request dont mind !

how do i put last cells value in another cell ?

as i keep on adding the rows so last cells value changes i want the
same value to be displayed on another cell how do i do this ?

regards
Jay

Jay

Here's one way:

=INDEX(L9:L64999,MAX(IF(ISBLANK(L9:L64999),0,ROW(L 9:L64999)-ROW(L9)+1)))

Also an array formula to be entered with <Shift<Ctrl<Enter

Regards
Leo Heuser

"Jay" skrev i en meddelelse
oups.com...
Leo,

i have stock market investment spread sheet where in

say : Initial Investment : $1000.00

After Day 1 Trading : $1020.00
After Day 2 Trading : $1068.00
After Day 3 Trading : $1120.00
After Day 4 Trading : $1080.00
After Day 5 Trading : $980.00
After Day 6 Trading : $1020.00
After Day 7 Trading : $1060.00

I would like to calculate and display this in another cell how much max
% we have lost in an sequence against our maximum profit max :
$1120.00

Forumula i have is $1120-$980/$1120 * 100 = 12.5% i want this to be
displayed in another cell. only maximum % lost in any trading sequence
just like previous problem

thanx in advance
regards
Jay

Jay

One way assuming maximum profit in K1 and tradings in J3:J65000

=(K1-MIN(J3:J65000))/K1

Format the cell as %

Regards
Leo Heuser

leo
thanks a lot for the solutions to myproblems.
last cell value got soloved

but Max % lost in trading is not solved.

as u know trading flactuates every day so here in this case i need to
find out max % lost in any consecutive days say

we had $1200 Max and minimum $980 as days progress we may surpass
$1200 ...it shouldnt take next maximum value my intention is to find
out one maximum lost % continuous losing from

example :

here is my initial bank $1000

1. $1100
2. $1150
3. $1200 Max Bank
4. $1090
5. $980 Min Bank at this point we have lost max % to the maximum
bank.
6. $1100
7. $1250

we may lose max % to maximum in the future ...what max % lost has to be
the value displayed in another cell.

hope u can help me

sorry to disturd u

thanx a lot for what ever u did for me till now

regards
Jay

Leo Heuser wrote:
"Jay" skrev i en meddelelse
oups.com...
leo,
i have one more request dont mind !

how do i put last cells value in another cell ?

as i keep on adding the rows so last cells value changes i want the
same value to be displayed on another cell how do i do this ?

regards
Jay

Jay

Here's one way:

=INDEX(L9:L64999,MAX(IF(ISBLANK(L9:L64999),0,ROW(L 9:L64999)-ROW(L9)+1)))

Also an array formula to be entered with <Shift<Ctrl<Enter

Regards
Leo Heuser

"Jay" skrev i en meddelelse
oups.com...
leo
thanks a lot for the solutions to myproblems.
last cell value got soloved

but Max % lost in trading is not solved.

as u know trading flactuates every day so here in this case i need to
find out max % lost in any consecutive days say

we had $1200 Max and minimum $980 as days progress we may surpass
$1200 ...it shouldnt take next maximum value my intention is to find
out one maximum lost % continuous losing from

example :

here is my initial bank $1000

1. $1100
2. $1150
3. $1200 Max Bank
4. $1090
5. $980 Min Bank at this point we have lost max % to the maximum
bank.
6. $1100
7. $1250

we may lose max % to maximum in the future ...what max % lost has to be
the value displayed in another cell.

hope u can help me

sorry to disturd u

thanx a lot for what ever u did for me till now

regards
Jay


Jay

I'm not sure, that I fully understand, what you're after, but
here's my guess. With data in A2:A200 and initial amount in A2.

=IF(INDEX(A2:A200,MAX(IF(ISBLANK(A2:A200),0,ROW(A2 :A200)-
ROW(A2)+1)))=MAX(A2:A200),0,(MAX(A2:A200)-INDEX(A2:A200,
MAX(IF((A2:A200<MAX(A2:A200))*(A2:A200<""),ROW(A2 :A200)-
ROW(A2)+1))))/MAX(A2:A200))

Again in one line, entered with <Shift<Ctrl<Enter and the cell
formatted as %.

If I'm wrong, please make a larger example, which shows all
the principles with calculated results.

Regards
Leo Heuser

Wht i m looking for is

I would like to find out in a tenure of trading Maximum % Lost say

$1000
$1200
$1350
$1450 - Max 1
$1200
$1450
$1200
$1100
$1000
$900 - Min 1
$1000
$1200
$1350
$1450
$1500 - Max 2
$1200
$1100
$1000
$800 - Min 2

1st Max and Min - $1450-900/$1450 = 37.94% Lost this will be
displayed 1st
2nd Max and Min - $1500-800/$1500= 46% lost this will displayed noth
the first one as we have lost more than first Max Min .

its about checking each trading day Max bank minimum bank - minimum
bank should be taken after the maximum bank attained not the previous
minimum

Max-Min/Max * 100 = Max % Lost after attaining Maximum bank.

Minimum should be taken after the maximum bank attained as explained
above

what ever lost has to be Maximum % lost in all the trading ....

i think its clear now

regards
jay



% lost to maximum bank attained here we attained $1450
Leo Heuser wrote:
"Jay" skrev i en meddelelse
oups.com...
leo
thanks a lot for the solutions to myproblems.
last cell value got soloved

but Max % lost in trading is not solved.

as u know trading flactuates every day so here in this case i need to
find out max % lost in any consecutive days say

we had $1200 Max and minimum $980 as days progress we may surpass
$1200 ...it shouldnt take next maximum value my intention is to find
out one maximum lost % continuous losing from

example :

here is my initial bank $1000

1. $1100
2. $1150
3. $1200 Max Bank
4. $1090
5. $980 Min Bank at this point we have lost max % to the maximum
bank.
6. $1100
7. $1250

we may lose max % to maximum in the future ...what max % lost has to be
the value displayed in another cell.

hope u can help me

sorry to disturd u

thanx a lot for what ever u did for me till now

regards
Jay


Jay

I'm not sure, that I fully understand, what you're after, but
here's my guess. With data in A2:A200 and initial amount in A2.

=IF(INDEX(A2:A200,MAX(IF(ISBLANK(A2:A200),0,ROW(A2 :A200)-
ROW(A2)+1)))=MAX(A2:A200),0,(MAX(A2:A200)-INDEX(A2:A200,
MAX(IF((A2:A200<MAX(A2:A200))*(A2:A200<""),ROW(A2 :A200)-
ROW(A2)+1))))/MAX(A2:A200))

Again in one line, entered with <Shift<Ctrl<Enter and the cell
formatted as %.

If I'm wrong, please make a larger example, which shows all
the principles with calculated results.

Regards
Leo Heuser

"Jay" skrev i en meddelelse
oups.com...
Wht i m looking for is

I would like to find out in a tenure of trading Maximum % Lost say

$1000
$1200
$1350
$1450 - Max 1
$1200
$1450
$1200
$1100
$1000
$900 - Min 1
$1000
$1200
$1350
$1450
$1500 - Max 2
$1200
$1100
$1000
$800 - Min 2

1st Max and Min - $1450-900/$1450 = 37.94% Lost this will be
displayed 1st
2nd Max and Min - $1500-800/$1500= 46% lost this will displayed noth
the first one as we have lost more than first Max Min .

its about checking each trading day Max bank minimum bank - minimum
bank should be taken after the maximum bank attained not the previous
minimum

Max-Min/Max * 100 = Max % Lost after attaining Maximum bank.

Minimum should be taken after the maximum bank attained as explained
above

what ever lost has to be Maximum % lost in all the trading ....

i think its clear now

regards
jay



Jay

I don't think it's possible to make a formula solution without helper cells
and/or named formulae, so I have dropped it and instead made a user
defined function (UDF). It has the added advantage, that you are able
to find the max % lost for an arbitrary (sequentially) period of time.

Here's how to implement it:

1. Copy the code below (see note!)
2. Enter the VBA-editor with <Alt<F11
3. Doubleclick the project in the project window
at the left side of the screen. (if it isn't visible, use <Ctrlr)
4. Choose the menu Insert Module
5. Doubleclick the new module in the project window
6. Paste the code to the right hand window.
7. Return to the sheet with <Alt<F11
8. Save the workbook.

From the workbook:
Assuming data in A2:A4000 (more than 10 years, so you probably
haven't filled all cells in the range :-)

In any cell outside column A (e.g. G1) enter:

=maxbank(a2:a4000)

G1 displays the result for all entered values (e.g. a2:a167)

=maxbank(a23:a129)

G1 displays the result for the period spanning cells a23 through a129.

As you enter new data in a168 and down, G1 will display the
largest % loss for the various groups (a group being data from
one max value to the next, or from the last max value to the
last entry)


Regards
Leo Heuser


Note:
Because of the spaces in front of the lines, the code may not work
when copied and pasted. If you experience that open the toolbar
"Edit", select all code in the module window and press the button
"Outdent" repeatedly until *all* lines match the left border of the
window.
The code may loose in readability, but it works :-)


Function MaxBank(BankRange As Range) As Double
'Leo Heuser, Sep. 16, 2006
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

Set BankRange = BankRange.Columns(1)

If IsEmpty(BankRange.Cells(1, 1). _
Offset(BankRange.Rows.Count)) Then
Set BankRange = Range(BankRange.Cells(1, 1), _
ActiveSheet.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

Leo,

Its working great !

Thanx a lot for your help

Regards
Jay
Leo Heuser wrote:
"Jay" skrev i en meddelelse
oups.com...
Wht i m looking for is

I would like to find out in a tenure of trading Maximum % Lost say

$1000
$1200
$1350
$1450 - Max 1
$1200
$1450
$1200
$1100
$1000
$900 - Min 1
$1000
$1200
$1350
$1450
$1500 - Max 2
$1200
$1100
$1000
$800 - Min 2

1st Max and Min - $1450-900/$1450 = 37.94% Lost this will be
displayed 1st
2nd Max and Min - $1500-800/$1500= 46% lost this will displayed noth
the first one as we have lost more than first Max Min .

its about checking each trading day Max bank minimum bank - minimum
bank should be taken after the maximum bank attained not the previous
minimum

Max-Min/Max * 100 = Max % Lost after attaining Maximum bank.

Minimum should be taken after the maximum bank attained as explained
above

what ever lost has to be Maximum % lost in all the trading ....

i think its clear now

regards
jay



Jay

I don't think it's possible to make a formula solution without helper cells
and/or named formulae, so I have dropped it and instead made a user
defined function (UDF). It has the added advantage, that you are able
to find the max % lost for an arbitrary (sequentially) period of time.

Here's how to implement it:

1. Copy the code below (see note!)
2. Enter the VBA-editor with <Alt<F11
3. Doubleclick the project in the project window
at the left side of the screen. (if it isn't visible, use <Ctrlr)
4. Choose the menu Insert Module
5. Doubleclick the new module in the project window
6. Paste the code to the right hand window.
7. Return to the sheet with <Alt<F11
8. Save the workbook.

From the workbook:
Assuming data in A2:A4000 (more than 10 years, so you probably
haven't filled all cells in the range :-)

In any cell outside column A (e.g. G1) enter:

=maxbank(a2:a4000)

G1 displays the result for all entered values (e.g. a2:a167)

=maxbank(a23:a129)

G1 displays the result for the period spanning cells a23 through a129.

As you enter new data in a168 and down, G1 will display the
largest % loss for the various groups (a group being data from
one max value to the next, or from the last max value to the
last entry)


Regards
Leo Heuser


Note:
Because of the spaces in front of the lines, the code may not work
when copied and pasted. If you experience that open the toolbar
"Edit", select all code in the module window and press the button
"Outdent" repeatedly until *all* lines match the left border of the
window.
The code may loose in readability, but it works :-)


Function MaxBank(BankRange As Range) As Double
'Leo Heuser, Sep. 16, 2006
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

Set BankRange = BankRange.Columns(1)

If IsEmpty(BankRange.Cells(1, 1). _
Offset(BankRange.Rows.Count)) Then
Set BankRange = Range(BankRange.Cells(1, 1), _
ActiveSheet.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

leo can this be done ! sorry if i m disturbing u cos i m novice in
excel :(

i appreciate it if you could solve this problem !
i have work sheet with table given below , i have another worksheet
with Monthly sales on that i would like to sum daily sales into
particular month in another sheet 2 whats the formula or Module for
this ?


Date Sales
01/01/2006 $1000
02/01/2006 $1200
03/01/2006 $1300
...
..
..
..
31/01/2006 $1200
01/02/2006 $1000
02/02/2006 $1200
..
..
..
28/02/2006 $1500.
..
..
31/12/2006 $1800


Sheet 2:

January ?
February
March
..
..
..
December


thanx in advance
regards
Jay





Jay wrote:
Leo,

Its working great !

Thanx a lot for your help

Regards
Jay
Leo Heuser wrote:
"Jay" skrev i en meddelelse
oups.com...
Wht i m looking for is

I would like to find out in a tenure of trading Maximum % Lost say

$1000
$1200
$1350
$1450 - Max 1
$1200
$1450
$1200
$1100
$1000
$900 - Min 1
$1000
$1200
$1350
$1450
$1500 - Max 2
$1200
$1100
$1000
$800 - Min 2

1st Max and Min - $1450-900/$1450 = 37.94% Lost this will be
displayed 1st
2nd Max and Min - $1500-800/$1500= 46% lost this will displayed noth
the first one as we have lost more than first Max Min .

its about checking each trading day Max bank minimum bank - minimum
bank should be taken after the maximum bank attained not the previous
minimum

Max-Min/Max * 100 = Max % Lost after attaining Maximum bank.

Minimum should be taken after the maximum bank attained as explained
above

what ever lost has to be Maximum % lost in all the trading ....

i think its clear now

regards
jay



Jay

I don't think it's possible to make a formula solution without helper cells
and/or named formulae, so I have dropped it and instead made a user
defined function (UDF). It has the added advantage, that you are able
to find the max % lost for an arbitrary (sequentially) period of time.

Here's how to implement it:

1. Copy the code below (see note!)
2. Enter the VBA-editor with <Alt<F11
3. Doubleclick the project in the project window
at the left side of the screen. (if it isn't visible, use <Ctrlr)
4. Choose the menu Insert Module
5. Doubleclick the new module in the project window
6. Paste the code to the right hand window.
7. Return to the sheet with <Alt<F11
8. Save the workbook.

From the workbook:
Assuming data in A2:A4000 (more than 10 years, so you probably
haven't filled all cells in the range :-)

In any cell outside column A (e.g. G1) enter:

=maxbank(a2:a4000)

G1 displays the result for all entered values (e.g. a2:a167)

=maxbank(a23:a129)

G1 displays the result for the period spanning cells a23 through a129.

As you enter new data in a168 and down, G1 will display the
largest % loss for the various groups (a group being data from
one max value to the next, or from the last max value to the
last entry)


Regards
Leo Heuser


Note:
Because of the spaces in front of the lines, the code may not work
when copied and pasted. If you experience that open the toolbar
"Edit", select all code in the module window and press the button
"Outdent" repeatedly until *all* lines match the left border of the
window.
The code may loose in readability, but it works :-)


Function MaxBank(BankRange As Range) As Double
'Leo Heuser, Sep. 16, 2006
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

Set BankRange = BankRange.Columns(1)

If IsEmpty(BankRange.Cells(1, 1). _
Offset(BankRange.Rows.Count)) Then
Set BankRange = Range(BankRange.Cells(1, 1), _
ActiveSheet.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

"Jay" skrev i en meddelelse
ups.com...
Leo,

Its working great !

Thanx a lot for your help

Regards
Jay


You're welcome.
Thanks for your feedback :-)


Regards
Leo Heuser

"Jay" skrev i en meddelelse
oups.com...
leo can this be done ! sorry if i m disturbing u cos i m novice in
excel :(

i appreciate it if you could solve this problem !
i have work sheet with table given below , i have another worksheet
with Monthly sales on that i would like to sum daily sales into
particular month in another sheet 2 whats the formula or Module for
this ?


Date Sales
01/01/2006 $1000
02/01/2006 $1200
03/01/2006 $1300
..
.
.
.
31/01/2006 $1200
01/02/2006 $1000
02/02/2006 $1200
.
.
.
28/02/2006 $1500.
.
.
31/12/2006 $1800


Sheet 2:

January ?
February
March
.
.
.
December


thanx in advance
regards
Jay





Last gasp!

For January:
=SUMPRODUCT((MONTH(Sheet1!$A$2:$A$100)=1)*(Sheet1! $B$2:$B$100))

For February:
=SUMPRODUCT((MONTH(Sheet1!$A$2:$A$100)=2)*(Sheet1! $B$2:$B$100))

etc.

Assuming all data from same year.

Regards
Leo Heuser

thanx leo,

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

thanx for last problems solution

regards
Jay

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

A
A
B
B
B



Leo Heuser wrote:
"Jay" skrev i en meddelelse
oups.com...
leo can this be done ! sorry if i m disturbing u cos i m novice in
excel :(

i appreciate it if you could solve this problem !
i have work sheet with table given below , i have another worksheet
with Monthly sales on that i would like to sum daily sales into
particular month in another sheet 2 whats the formula or Module for
this ?


Date Sales
01/01/2006 $1000
02/01/2006 $1200
03/01/2006 $1300
..
.
.
.
31/01/2006 $1200
01/02/2006 $1000
02/02/2006 $1200
.
.
.
28/02/2006 $1500.
.
.
31/12/2006 $1800


Sheet 2:

January ?
February
March
.
.
.
December


thanx in advance
regards
Jay





Last gasp!

For January:
=SUMPRODUCT((MONTH(Sheet1!$A$2:$A$100)=1)*(Sheet1! $B$2:$B$100))

For February:
=SUMPRODUCT((MONTH(Sheet1!$A$2:$A$100)=2)*(Sheet1! $B$2:$B$100))

etc.

Assuming all data from same year.

Regards
Leo Heuser

"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

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

"Jay" skrev i en meddelelse
oups.com...
leo thanx,

Does that mean, that the new version of "MaxBank" took care of
the error?



i m gettting this error on counting B

#NA erro

any clue ?

Since you haven't told me how things are organized,
I haven't got a clue. Where are the dates and where
are the Bs? Is the formula in the same sheet as the lists?
How does your formula look?

Regards
Leo Heuser

leo
Thanx for all your help,

but i encountered a problem with the max % lost when i put a value on
withdrawel column as bank will reduce and this gave way to max % lost
to more than 99%

how do i rectify this problem

I have a withdrawel and deposit column when ever i have put in or out
money Bank column changes according to if deposit it will increase ,
withdrawel then bank will reduce i dont want this deposit and
withdrawel should effect the max % lost ?

how do i rectify this

regarrds
Jay
Jay wrote:
thanx leo,

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

thanx for last problems solution

regards
Jay

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

A
A
B
B
B



Leo Heuser wrote:
"Jay" skrev i en meddelelse
oups.com...
leo can this be done ! sorry if i m disturbing u cos i m novice in
excel :(

i appreciate it if you could solve this problem !
i have work sheet with table given below , i have another worksheet
with Monthly sales on that i would like to sum daily sales into
particular month in another sheet 2 whats the formula or Module for
this ?


Date Sales
01/01/2006 $1000
02/01/2006 $1200
03/01/2006 $1300
..
.
.
.
31/01/2006 $1200
01/02/2006 $1000
02/02/2006 $1200
.
.
.
28/02/2006 $1500.
.
.
31/12/2006 $1800


Sheet 2:

January ?
February
March
.
.
.
December


thanx in advance
regards
Jay





Last gasp!

For January:
=SUMPRODUCT((MONTH(Sheet1!$A$2:$A$100)=1)*(Sheet1! $B$2:$B$100))

For February:
=SUMPRODUCT((MONTH(Sheet1!$A$2:$A$100)=2)*(Sheet1! $B$2:$B$100))

etc.

Assuming all data from same year.

Regards
Leo Heuser

"Jay" skrev i en meddelelse
oups.com...
leo
Thanx for all your help,

but i encountered a problem with the max % lost when i put a value on
withdrawel column as bank will reduce and this gave way to max % lost
to more than 99%

how do i rectify this problem

I have a withdrawel and deposit column when ever i have put in or out
money Bank column changes according to if deposit it will increase ,
withdrawel then bank will reduce i dont want this deposit and
withdrawel should effect the max % lost ?

how do i rectify this

regarrds
Jay



Jay

You can attach a copy of your workbook to a personal mail,
and I'll take a look at it. Which version of Excel are you using?

Regards
Leo Heuser

hi

which address should i send the excel ?

Regards
Jay

Leo Heuser wrote:
"Jay" skrev i en meddelelse
oups.com...
leo
Thanx for all your help,

but i encountered a problem with the max % lost when i put a value on
withdrawel column as bank will reduce and this gave way to max % lost
to more than 99%

how do i rectify this problem

I have a withdrawel and deposit column when ever i have put in or out
money Bank column changes according to if deposit it will increase ,
withdrawel then bank will reduce i dont want this deposit and
withdrawel should effect the max % lost ?

how do i rectify this

regarrds
Jay



Jay

You can attach a copy of your workbook to a personal mail,
and I'll take a look at it. Which version of Excel are you using?

Regards
Leo Heuser

"Jay" skrev i en meddelelse
oups.com...
hi

which address should i send the excel ?

Regards
Jay


Hi

I mentioned that in my posting from 13 Sep.

leo.heuser at adslhome.dk

Regards
Leo Heuser

leo,

did u get my email ?

rgrds
jay
Leo Heuser wrote:
"Jay" skrev i en meddelelse
oups.com...
hi

which address should i send the excel ?

Regards
Jay


Hi

I mentioned that in my posting from 13 Sep.

leo.heuser at adslhome.dk

Regards
Leo Heuser

"Jay" skrev i en meddelelse
ups.com...
leo,

did u get my email ?

rgrds
jay

Jay

Yes I got it. I'll have a look at it as soon as possible.

Regards
Leo Heuser

Leo,

i m looking forward for your reply

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

did u get my email ?

rgrds
jay

Jay

Yes I got it. I'll have a look at it as soon as possible.

Regards
Leo Heuser

"Jay" skrev i en meddelelse
oups.com...
Leo,

i m looking forward for your reply

regards
Jay

Jay

I have used a lot of time to help you, but when you
add new information on the fly, and it turns out, that
it's not just a snap to include it in the code, I am not
inclined to continue using time on the function.

The next time you ask for help in the groups, please
disclose *all* information from the start.

Nobody gets paid for helping in the groups. We do it,
because we like helping and because it's fun, but
it's not fun having spent an hour or two to help you, only
to be told, that I wasted my time because you didn't
supply all the necessary pieces of information from the
beginning.

Regards
Leo Heuser

Leo

Thank you for all your help

Regards
Jay
Leo Heuser wrote:
"Jay" skrev i en meddelelse
oups.com...
Leo,

i m looking forward for your reply

regards
Jay

Jay

I have used a lot of time to help you, but when you
add new information on the fly, and it turns out, that
it's not just a snap to include it in the code, I am not
inclined to continue using time on the function.

The next time you ask for help in the groups, please
disclose *all* information from the start.

Nobody gets paid for helping in the groups. We do it,
because we like helping and because it's fun, but
it's not fun having spent an hour or two to help you, only
to be told, that I wasted my time because you didn't
supply all the necessary pieces of information from the
beginning.

Regards
Leo Heuser