SOLVED

# Standard Deviation and Formula Look Up

Occasional Contributor

# Standard Deviation and Formula Look Up

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.

17 Replies

# Re: Standard Deviation and Formula Look Up

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.

# Re: Standard Deviation and Formula Look Up

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

# Re: Standard Deviation and Formula Look Up

Change it to

=STDEV.S(IF(POWER[Date]=\$C2,POWER[Weight],""))

# Re: Standard Deviation and Formula Look Up

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

# Re: Standard Deviation and Formula Look Up

Also,

Will this work for the rest of the metrics like Broad, vert, and things like that?@Hans Vogelaar

# Re: Standard Deviation and Formula Look Up

I'll get back to you later, no time now.

best response confirmed by Dtripple40 (Occasional Contributor)
Solution

# Re: Standard Deviation and Formula Look Up

See the attached version.

# Re: Standard Deviation and Formula Look Up

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

# Re: Standard Deviation and Formula Look Up

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.

# Re: Standard Deviation and Formula Look Up

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

# Re: Standard Deviation and Formula Look Up

The workbook that I attached a few replies back shows how to do that.

# Re: Standard Deviation and Formula Look Up

this is a couple weeks late. But any chance I can still get some help with this work book.

# Re: Standard Deviation and Formula Look Up

I still can not seem to get the data to change with the date. So for athlete A on Date 2/16/21 everything shoes up. But when I change the Day it will not change to the data on day 2/17/2021. I can attach what I currently have.

Is this better?

# Re: Standard Deviation and Formula Look Up

Yes! Thank You! This is big time!