|
Maximum Number of times
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 |
Maximum Number of times
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 |
Maximum Number of times
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 |
Maximum Number of times
"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. |
Maximum Number of times
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. |
Maximum Number of times
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. |
Maximum Number of times
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. |
Maximum Number of times
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. |
Maximum Number of times
"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 |
Maximum Number of times
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 |
Maximum Number of times
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 |
Maximum Number of times
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 |
Maximum Number of times
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 |
Maximum Number of times
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 |
Maximum Number of times
"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 |
Maximum Number of times
"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 |
Maximum Number of times
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 |
Maximum Number of times
"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 |
Maximum Number of times
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 |
Maximum Number of times
"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 |
Maximum Number of times
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 |
Maximum Number of times
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 |
Maximum Number of times
"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 |
Maximum Number of times
"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 |
Maximum Number of times
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 |
Maximum Number of times
"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 |
Maximum Number of times
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 |
Maximum Number of times
"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 |
Maximum Number of times
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 |
Maximum Number of times
"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 |
Maximum Number of times
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 |
Maximum Number of times
"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 |
Maximum Number of times
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 |
Maximum Number of times
"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 |
Maximum Number of times
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 |
Maximum Number of times
"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 |
Maximum Number of times
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 |
| All times are GMT +1. The time now is 09:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com