Needs to calcuate a Dymamic value relative to 2 cells

[tommytrader]

Hello, I have not been able to solve this excel forlmua. It is probably simple but I just don't see it. Any help would be greatly appeciated. Happy New Year. Tommy



Price Problem.jpg

I made a mistake in the 2nd scenerio. Should be 25%

[shg]

Welcome to the board.

	A	B	C	D	E
1	High	Dynamic	Low	Result
2	$ 300.00	$ 200.00	$ 100.00	50%	D2: =(B2-A2) / (C2-A2)
3	$ 300.00	$ 150.00	$ 100.00	75%
4	$ 300.00	$ 75.00	$ 100.00	113%

I don't understand your last result.

[tommytrader]

Im sorry, this formulas does not give the desired result?

A2 is correct

B3 (not what I am trying to accomplish) It (H) is 300 and (L) is 100, then D3 must return a value of less than 50% because B3 is closer to C3 than A3 as the median is 200. Make sense?

Also, D4 should return a negative value as it is lower than C4 at 100

I hope I am explaining it OK

[MrShorty]

I usually find that I must first figure out the math/algebra expression that gives the desired result. Do you know what this expression should be? The first two examples look like (x-low)/(high-low), but those that are below the low value are calculated as (x-low)/low. Is that right?

[tommytrader]

I have been experimenting with all sorts of formulas with no success.

=(J23-K30)/(J30-K30) with (D) at 74.00 and (L) at 73.75 and (H) at 75.00 this returns 20%

=(J23-K30)/K30 Returns .34% but not expressed as a negative number

How can you combine thes results into 1 formula result?

[tommytrader]

Sorry I could not edit the previous post.

By reversing to =(K30-J23)/K30 now returns the negative number...

[shg]

Maybe if you would explain the purpose of the calculation we could understand what you're trying to do.

[tommytrader]

Hi shg, This formula is used to track resistance and support for options trading. Knowing where the stock price is in relationship to its support and resistance allow intellegent entry and exit points in option trading. The good new is that I was able to solve the problen and the formula is now working as desired. Thank you very much for your assistance and Happy New Year to you!

[shg]

Cheers .