Hi,
I am trying to calculate the variance for the following: sum([Metric X]) / [Prior 4Wk Avg - Metric X] - 1
I am using the following formula to calculate[4Wk Avg - Metric X]:
(
lookup(sum([metric]),-24*7)+
lookup(sum([metric]),-24*14)+
lookup(sum([metric),-24*21)+
lookup(sum([metric),-24*28)
) /4
The data is displayed on an hourly basis for one day. Currently I am able to calculate it by hour, but i would like to display the total variance for the day as of the last available hour (Highlighted in red in the attached document is the numbers i would like display on the workbook).
Your suggestions would be greatly appreciate.
Thank you so much!
Rody Zakovich any ideas?