If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Find 2 highest point to form resistance line
Dear expert,
Have left a post for finding 2 lowest point for form a support line. Now, would like to find 2 highest point to form a resistance line. Data is from an array for formula. In this case, should choose 91.27 and 87.96 to form resistance line. Although 89.95 is higher than 87.96, line is forward plotting. Is it possible? If the highest in last row, can show "No line". Thanks 52.35 26-Jan-10 59.87 18-Feb-10 89.95 26-Feb-10 85.41 05-Mar-10 91.27 10-Mar-10 87.96 23-Mar-10 36.69 29-Mar-10 |
#2
|
|||
|
|||
Find 2 highest point to form resistance line
I put your data in A1:B5
In E1 I find the maximum with =MAX(A1:A7) In F1 I find the next maximum that is later in the column than the first using =MAX(INDIRECT("A"&MATCH(E1,A1:A7,0)+1&":A7")) If the Max is the last in the column than E1 and F1 will have the same vlaue To get "No line" use =IF(MATCH(E1,A1:A7,0)=7,"No line",MAX(INDIRECT("A"&MATCH(E1,A1:A7,0)+1&":A7")) ) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Elton Law" wrote in message ... Dear expert, Have left a post for finding 2 lowest point for form a support line. Now, would like to find 2 highest point to form a resistance line. Data is from an array for formula. In this case, should choose 91.27 and 87.96 to form resistance line. Although 89.95 is higher than 87.96, line is forward plotting. Is it possible? If the highest in last row, can show "No line". Thanks 52.35 26-Jan-10 59.87 18-Feb-10 89.95 26-Feb-10 85.41 05-Mar-10 91.27 10-Mar-10 87.96 23-Mar-10 36.69 29-Mar-10 |
#3
|
|||
|
|||
Find 2 highest point to form resistance line
With your data in A1:B7
In E1 the max is found with =MAX(A1:A7) The next largest number that is later in the column is found with =MAX(INDIRECT("A"&MATCH(E1,A1:A7,0)+1&":A7")) If the max is the last item then E1 and F1 will hve the same value To get "No Line" =IF(MATCH(E1,A1:A7,0)=7,"no line",MAX(INDIRECT("A"&MATCH(E1,A1:A7,0)+1&":A7")) ) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Elton Law" wrote in message ... Dear expert, Have left a post for finding 2 lowest point for form a support line. Now, would like to find 2 highest point to form a resistance line. Data is from an array for formula. In this case, should choose 91.27 and 87.96 to form resistance line. Although 89.95 is higher than 87.96, line is forward plotting. Is it possible? If the highest in last row, can show "No line". Thanks 52.35 26-Jan-10 59.87 18-Feb-10 89.95 26-Feb-10 85.41 05-Mar-10 91.27 10-Mar-10 87.96 23-Mar-10 36.69 29-Mar-10 |
#4
|
|||
|
|||
Find 2 highest point to form resistance line
Hi Bernard,
Thanks for help. Elton "Bernard Liengme" wrote: With your data in A1:B7 In E1 the max is found with =MAX(A1:A7) The next largest number that is later in the column is found with =MAX(INDIRECT("A"&MATCH(E1,A1:A7,0)+1&":A7")) If the max is the last item then E1 and F1 will hve the same value To get "No Line" =IF(MATCH(E1,A1:A7,0)=7,"no line",MAX(INDIRECT("A"&MATCH(E1,A1:A7,0)+1&":A7")) ) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Elton Law" wrote in message ... Dear expert, Have left a post for finding 2 lowest point for form a support line. Now, would like to find 2 highest point to form a resistance line. Data is from an array for formula. In this case, should choose 91.27 and 87.96 to form resistance line. Although 89.95 is higher than 87.96, line is forward plotting. Is it possible? If the highest in last row, can show "No line". Thanks 52.35 26-Jan-10 59.87 18-Feb-10 89.95 26-Feb-10 85.41 05-Mar-10 91.27 10-Mar-10 87.96 23-Mar-10 36.69 29-Mar-10 . |
#5
|
|||
|
|||
Find 2 highest point to form resistance line
Hi Bernard,
Can you advise why it does not work when I add a row in 1, although I have changed from =MAX(INDIRECT("A"&MATCH(E1,A1:A7,0)+1&":A7")) to =MAX(INDIRECT("A"&MATCH(E2,A2:A8,0)+1&":A8")) ? Thanks Elton "Bernard Liengme" wrote: With your data in A1:B7 In E1 the max is found with =MAX(A1:A7) The next largest number that is later in the column is found with =MAX(INDIRECT("A"&MATCH(E1,A1:A7,0)+1&":A7")) If the max is the last item then E1 and F1 will hve the same value To get "No Line" =IF(MATCH(E1,A1:A7,0)=7,"no line",MAX(INDIRECT("A"&MATCH(E1,A1:A7,0)+1&":A7")) ) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Elton Law" wrote in message ... Dear expert, Have left a post for finding 2 lowest point for form a support line. Now, would like to find 2 highest point to form a resistance line. Data is from an array for formula. In this case, should choose 91.27 and 87.96 to form resistance line. Although 89.95 is higher than 87.96, line is forward plotting. Is it possible? If the highest in last row, can show "No line". Thanks 52.35 26-Jan-10 59.87 18-Feb-10 89.95 26-Feb-10 85.41 05-Mar-10 91.27 10-Mar-10 87.96 23-Mar-10 36.69 29-Mar-10 . |
Thread Tools | |
Display Modes | |
|
|