Forum Discussion

Dtripple40's avatar
Dtripple40
Copper Contributor
Feb 17, 2021
Solved

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 th...
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
Feb 17, 2021

Dtripple40 

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.

Dtripple40's avatar
Dtripple40
Copper Contributor
Feb 17, 2021

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. HansVogelaar 

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Feb 17, 2021

    Dtripple40 

    Change it to

     

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

    • Dtripple40's avatar
      Dtripple40
      Copper Contributor
      Feb 17, 2021

      Also,

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

    • Dtripple40's avatar
      Dtripple40
      Copper Contributor
      Feb 17, 2021

      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( HansVogelaar 

Resources