Hello, I need to ‘freeze’ a higher low LLV1 (see screen shot attached) for example 40 periods ago and then compare it to a lower low LLV3 today. What happens is that LLV1 moves down to LLV3 and they are equal at point (3) so I cannot calculate the difference. Any idea how to ‘freeze’ LLV1?

Edited by user Friday, October 27, 2023 12:28:59 PM(UTC)  | Reason: Not specified

The formula is pretty simple VVL(...,40), and '...' could be anything e.g. CLOSE. I have uploaded a chart for better illustration. The chart is attached but when I'll post it, it disappears. Where can I email that chart to?

With 'Ref' one data point is possible only. Right? With 'ValueWhen' one data point in the past is possible only. Right? >>> What I need though is a range from e.g. -40 periods in the past with VVL and keeps going until today, even if there is a lower low.

Thanks for your answer.

Thanks a lot for your answer. :-)   It is a nice workaround, and it does the job, by moving the original function into the future.

'ValueWhen' is not just for a specific day and rigid, but can also be used on a rolling basis, if used in this way: ValueWhen(20 , Month() , LLV(L,40));

20 in this example shifts the function 19 periods into the future.

Edited by user Tuesday, October 31, 2023 6:31:34 PM(UTC)  | Reason: Not specified

Originally Posted by: MS Support

Originally Posted by: flavio_meta

The formula is pretty simple VVL(...,40), and '...' could be anything e.g. CLOSE. I have uploaded a chart for better illustration. The chart is attached but when I'll post it, it disappears. Where can I email that chart to?

With 'Ref' one data point is possible only. Right? With 'ValueWhen' one data point in the past is possible only. Right? >>> What I need though is a range from e.g. -40 periods in the past with VVL and keeps going until today, even if there is a lower low.

Thanks for your answer.

Thank you, I have a better idea of what you are asking. Unfortunately the point to 'freeze' is somewhat arbitrary without some other qualifying criteria, i.e. the lowest low since a specific date. So the relevant question would be, "How do I decide (via a formula) which lowest low to keep and which to ignore?"

You can use ValueWhen to look for a Lowest Low from a specific date using the DayOfMonth, Month, and Year functions but that obviously makes that part of the formula very specific and it would not work for other dates. For example:

Code:

ValueWhen(1,Month()=6 AND DayOfMonth()=15,LLV(L,40))

Sir,

How can i get the

highest high of  monday, tuesday & wednesday (of all weeek)

lowest low of monday, tuesday & wednesday (of all week)

Please help

Thank you

Krisna