Discussions
»
Product and Service Development
»
Formula Assistance
»
Monday's high, compared with the lowest point from Tues-Fri
Rank: Advanced Member
Groups: Registered, Registered Users, Unverified Users
Joined: 1/6/2009(UTC)
Posts: 36
Location: Los Angeles
Thanks: 4 times
|
How can I create a formula that find the percent change between:
For each week, find Monday's high, then, finding the "lowest" low among Tuesday through Friday?
I know that the basic formula is (Low - High) / High, but I'm not sure how to make it week-by-week (and pull only the days I want to see).
|
|
|
|
|
|
Rank: Advanced Member
Groups: Moderators, Registered, Registered Users, Subscribers
Joined: 10/8/2010(UTC)
Posts: 1,960
Thanks: 92 times
Was thanked: 155 time(s) in 150 post(s)
|
Hi Mark,
Haven't had a chance to fully validate this but you might want to try the following formula to see if it helps:
{Monday High}
Hi:=ValueWhen(1,DayOfWeek()=1,H);
{Tue through Fri Low}
NewWeek:=ROC(DayOfWeek(),1,$);
Lo:=ValueWhen(1,NewWeek,Ref(LowestSince(1,DayOfWeek()=2,L),-1));
((Lo-Hi) / Hi) * 100
The formula above would not account for any holidays, so in a case where Monday of a given week was a holiday, it would end up going back to get the previous week's Monday High value.
|
 1 user thanked MS Support for this useful post.
|
|
|
|
| Users browsing this topic |
|
Guest (Hidden)
|
Discussions
»
Product and Service Development
»
Formula Assistance
»
Monday's high, compared with the lowest point from Tues-Fri
Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.
