Find 2 highest point to form resistance line

  • Thread starter Thread starter Elton Law
  • Start date Start date
E

Elton Law

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
 
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
 
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
 
Hi Bernard,
Thanks for help.
Elton

Bernard Liengme said:
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


.
Click to expand...
 
Hi P45cal,
You are right.
I wanna download your attachment and see... but cannot make it.
I will try to register and then go to the post. thanks for your reply.
Elton
 
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
 
Back
Top