logo
Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error

Options
Go to last post Go to first unread
mark91345  
#1 Posted : 9 years ago
mark91345

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).

MS Support  
#2 Posted : 9 years ago
MS Support

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.
thanks 1 user thanked MS Support for this useful post.
mark91345 on 11/8/2015(UTC)
Users browsing this topic
Guest (Hidden)
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.

information You have 1 unread message(s) in your Inbox.