Feb 17 2021 07:22 AM
Hello. I am having an Issue with my standard deviation not adjusting based off the date when looking up values. So the formula will not find the date in the table and update the averages based off the table. I have been struggling with this for a good few days and am beyond frustrated. Here is the book.
Any suggestions or answered would be GREATLY appreciated. I plan on using the data on sheet 3 to create a pivot table and would like to compare the current subject selected the the average of the other subjects. So I am using the T-Scores to compare the subjects to each other.
Feb 17 2021 07:56 AM
The formula in C8 should be
=STDEV.S(IF(C2=POWER[Date],POWER[Weight],))
i.e. with [Date] instead of [@Date]. And it should be confirmed with Ctrl+Shift+Enter to turn it into an array formula.
Feb 17 2021 08:06 AM
So I made it an array formula and now I am getting standard deviation values at 117 for the 2/16 when it should be 38. But I am also now getting values for 2/17 so that better.
I added that [@Date] to see if would change anything. It did not. @Hans Vogelaar
Feb 17 2021 08:20 AM
Feb 17 2021 08:42 AM
Thank you!! That Was a big time help.
If you are feeling really nice. I cant seem to get the subject name to look up by the date and name. I will end up having repeated subjects. Like two A's. So this weigh the book could look at the date and name and find the correct data for that day. SO for subject A on the 2/17 the correct data would come up. I have only come up with
=if(c2=POWER[DATE]&B4=POWER[NAME],POWER[WEIGHT].....
I can not figure what would be next to have the book look up the name, date, then the metric associated with that name and date( @Hans Vogelaar
Feb 17 2021 08:46 AM
Also,
Will this work for the rest of the metrics like Broad, vert, and things like that?@Hans Vogelaar
Feb 17 2021 08:54 AM
I'll get back to you later, no time now.
Feb 17 2021 11:30 AM
SolutionSee the attached version.
Feb 18 2021 01:59 AM
I still seem to be getting zero. Might just have to scrap this work book and go with a different route. Thank You for your help @Hans Vogelaar
Feb 18 2021 02:42 AM
If you select the 17th of February, the Standard Deviation for most subjects will be zero since the data for those subjects are all the same. The standard deviation of a series of identical numbers is 0.
In the screenshot, you'll see that the weights vary, but all Broad values are the same, as are all Vert values, etc.
Feb 18 2021 01:52 PM
oh man that is tough. thank you. that helped a lot. I should be able then to just paste function in for weight and date next to the subject as well correct? That should give me the subjects metrics on the correct date as well?@Hans Vogelaar
Feb 18 2021 02:35 PM
The workbook that I attached a few replies back shows how to do that.
Mar 04 2021 01:47 PM
Mar 05 2021 03:07 PM
Mar 05 2021 03:41 PM
Feb 17 2021 11:30 AM
Solution