Find 2 highest point to form resistance line

[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

 

[Bernard Liengme]

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]

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

 

[p45cal]

I've had an explore and this is going to be a minefield.
For example, do you realise by sayingAlthough 89.95 is higher than 87.96, line is forward plotting.

that the resistance line can never be sloping upwards?

If I'm right regarding resistance lines, they join peak prices, and b
definition a peak means there is a lower price either side of it (henc
If the highest in last row, can show "No line".

I may be wrong regarding this because you cite that you would want t
form a resistance line from 2 adjacent points in your example.

Anyway, I had a play and produced the attached, and I've left value
deliberatley to raise an eyebrow or two (it follows your rules wit
these values) to see if it's producing the kind of thing you want. Pla
with the values in the yellow highlighted area to see how it changes th
resistance line.

If I'm getting close, come back.

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

+-------------------------------------------------------------------+
|Filename: ResistanceLine.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=530|
+-------------------------------------------------------------------+

 

[Elton Law]

Hi Bernard,
Thanks for help.
Elton

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]

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

 

[Elton Law]

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

 

[p45cal]

Oh.. So what message board are you posting at?

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

Anyway, I had a play and produced the attached, and I've left values
deliberatley to raise an eyebrow or two (it follows your rules with
these values) to see if it's producing the kind of thing you want Play
with the values in the yellow highlighted area to see how it change the
resistance line.

If I'm getting close, come back.

+-------------------------------------------------------------------+

|Filename: ResistanceLine.xls |
|Download

http://www.thecodecage.com/forumz/a...--------------------------------------------+

------------------------------------------------------------------------

Code Cage Forums
(http://www.thecodecage.com/forumz/showthread.php?t=194692)Programming - Access Programming' (http://www.thecodecage.com/forumz)

]

 

[p45cal]

Oh, so which site are you posting at?

(ps. There's something wrong with this site, I thought I'd posted thi
earlier.)