Hi, I'm trying to make an array formula that would do the job. In Data sheet I have data for each weekday, on Dashboard sheet I want to create formula that would calculate standard deviation for the data points concerning the specific week number, i.e. I want the formula I have created on Dashboard sheet to "jump" so that the calculations are correct for each week.
Thanks in advance for pointers
Last edited by Exceltrouble; 03-14-2016 at 11:09 AM.
Thanks for editing your thread title!
Solution as promised,
=STDEV.P(OFFSET(data_1!$I$1,MATCH($A2,data_1!$I$2:$I$50,0),MATCH(B$1,data_1!$J$1:$P$1,0),COUNTIF(data_1!$I$2:$I$50,$A2),1))
It uses a volatile function, but will probably operate more efficiently than an array formula.
Last edited by jason.b75; 03-14-2016 at 07:59 AM. Reason: removal of title change request
Hi
Try this array formula (Ctrl+Shift+Enter) and copy down and right
Formula:
Please Login or Register to view this content.
I must do something incorrect, both attempts result in formula error.
What kind of error?
See if this one works
=STDEV.P(OFFSET(data_1!$I$1;MATCH($A2;data_1!$I$2:$I$50;0);MATCH(B$1;data_1!$J$1:$P$1;0);COUNTIF(data_1!$I$2:$I$50;$A2);1))
Fixed thanks a lot for you assistance!
I missed the difference in formatting - "," ";"
Thanks once more.
Do I have to mark the thread also, "Closed" "Answered" or similar? Or this is done by admins?
Thanks for the feedback, always happy to help!
If you go back to edit your thread title there will be a dropdown box where you can mark it as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks