Here's the crux of the problem, the error in my formulation, I think:
((2/(pers+1))*(abs((sum(fml("Vid1"),pers2)-sum(fml("Vid2"),pers2))/
(sum(fml("Vid1"),pers2)+sum(fml("Vid2"),pers2))) )*c)+((1-( (2/(pers+1))*(abs((sum(fml("Vid1"),pers2)-sum(fml("Vid2"),pers2))/
(sum(fml("Vid1"),pers2)+sum(fml("Vid2"),pers2))) )))*ref(c,-1))
That ref(c,-1) at the very end is not right. It should be *Vidya the previous day. In the Excel formulation, it's K9, which I read in the bands as ref(c,-1), too. So that's where it references itself. K10 is the rest of the formula, and it's multiplied by K9, Vidya the day before.
What about copying Vidya and make an identical formula called Vidya2, then multiply everything by:
ref(fml("Vidya2"),-1))
So the formula becomes:
((2/(pers+1))*(abs((sum(fml("Vid1"),pers2)-sum(fml("Vid2"),pers2))/
(sum(fml("Vid1"),pers2)+sum(fml("Vid2"),pers2))) )*c)+((1-( (2/(pers+1))*(abs((sum(fml("Vid1"),pers2)-sum(fml("Vid2"),pers2))/
(sum(fml("Vid1"),pers2)+sum(fml("Vid2"),pers2))) )))**ref(fml("Vidya2"),-1))
Still not convinced? I thought not. Oh well.
But you could do the bands like this in WOW, both at once:
1. ref(fml("Vidya"),-1)+var*ref(fml("Vidya"),-1)
2. ref(fml("Vidya"),-1)-var*ref(fml("Vidya"),-1)
Where var = Percent vertical shift for bands and .01 is the default (.03 better for small caps)
Yes, I'm insane, I know, but think about that way to get around the self-referencing. Just copy Vidya, create Vidya2 and multiply Vidya times the previous day's value of Vidya2.
Brooke |
