SOLVED

Standard Deviation and Formula Look Up

%3CLINGO-SUB%20id%3D%22lingo-sub-2143200%22%20slang%3D%22en-US%22%3EStandard%20Deviation%20and%20Formula%20Look%20Up%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2143200%22%20slang%3D%22en-US%22%3E%3CP%3EHello.%20I%20am%20having%20an%20Issue%20with%20my%20standard%20deviation%20not%20adjusting%20based%20off%20the%20date%20when%20looking%20up%20values.%20So%20the%20formula%20will%20not%20find%20the%20date%20in%20the%20table%20and%20update%20the%20averages%20based%20off%20the%20table.%20I%20have%20been%20struggling%20with%20this%20for%20a%20good%20few%20days%20and%20am%20beyond%20frustrated.%20Here%20is%20the%20book.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%20or%20answered%20would%20be%20GREATLY%20appreciated.%20I%20plan%20on%20using%20the%20data%20on%20sheet%203%20to%20create%20a%20pivot%20table%20and%20would%20like%20to%20compare%20the%20current%20subject%20selected%20the%20the%20average%20of%20the%20other%20subjects.%20So%20I%20am%20using%20the%20T-Scores%20to%20compare%20the%20subjects%20to%20each%20other.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2143200%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2143276%22%20slang%3D%22en-US%22%3ERe%3A%20Standard%20Deviation%20and%20Formula%20Look%20Up%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2143276%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F969628%22%20target%3D%22_blank%22%3E%40Dtripple40%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20formula%20in%20C8%20should%20be%3C%2FP%3E%0A%3CP%3E%3DSTDEV.S(IF(C2%3DPOWER%5BDate%5D%2CPOWER%5BWeight%5D%2C))%3C%2FP%3E%0A%3CP%3Ei.e.%20with%20%5BDate%5D%20instead%20of%20%5B%40Date%5D.%20And%20it%20should%20be%20confirmed%20with%20Ctrl%2BShift%2BEnter%20to%20turn%20it%20into%20an%20array%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2143317%22%20slang%3D%22en-US%22%3ERe%3A%20Standard%20Deviation%20and%20Formula%20Look%20Up%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2143317%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20I%20made%20it%20an%20array%20formula%20and%20now%20I%20am%20getting%20standard%20deviation%20values%20at%20117%20for%20the%202%2F16%20when%20it%20should%20be%2038.%20But%20I%20am%20also%20now%20getting%20values%20for%202%2F17%20so%20that%20better.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20added%20that%20%5B%40Date%5D%20to%20see%20if%20would%20change%20anything.%20It%20did%20not.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2143372%22%20slang%3D%22en-US%22%3ERe%3A%20Standard%20Deviation%20and%20Formula%20Look%20Up%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2143372%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F969628%22%20target%3D%22_blank%22%3E%40Dtripple40%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EChange%20it%20to%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSTDEV.S(IF(POWER%5BDate%5D%3D%24C2%2CPOWER%5BWeight%5D%2C%22%22))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2143526%22%20slang%3D%22en-US%22%3ERe%3A%20Standard%20Deviation%20and%20Formula%20Look%20Up%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2143526%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you!!%20That%20Was%20a%20big%20time%20help.%3C%2FP%3E%3CP%3EIf%20you%20are%20feeling%20really%20nice.%20I%20cant%20seem%20to%20get%20the%20subject%20name%20to%20look%20up%20by%20the%20date%20and%20name.%20I%20will%20end%20up%20having%20repeated%20subjects.%20Like%20two%20A's.%20So%20this%20weigh%20the%20book%20could%20look%20at%20the%20date%20and%20name%20and%20find%20the%20correct%20data%20for%20that%20day.%20SO%20for%20subject%20A%20on%20the%202%2F17%20the%20correct%20data%20would%20come%20up.%20I%20have%20only%20come%20up%20with%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3Dif(c2%3DPOWER%5BDATE%5D%26amp%3BB4%3DPOWER%5BNAME%5D%2CPOWER%5BWEIGHT%5D.....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20not%20figure%20what%20would%20be%20next%20to%20have%20the%20book%20look%20up%20the%20name%2C%20date%2C%20then%20the%20metric%20associated%20with%20that%20name%20and%20date(%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2143545%22%20slang%3D%22en-US%22%3ERe%3A%20Standard%20Deviation%20and%20Formula%20Look%20Up%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2143545%22%20slang%3D%22en-US%22%3E%3CP%3EAlso%2C%3C%2FP%3E%3CP%3EWill%20this%20work%20for%20the%20rest%20of%20the%20metrics%20like%20Broad%2C%20vert%2C%20and%20things%20like%20that%3F%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2143568%22%20slang%3D%22en-US%22%3ERe%3A%20Standard%20Deviation%20and%20Formula%20Look%20Up%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2143568%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F969628%22%20target%3D%22_blank%22%3E%40Dtripple40%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'll%20get%20back%20to%20you%20later%2C%20no%20time%20now.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

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

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 

@Dtripple40 

Change it to

 

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

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 

Also,

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

@Dtripple40 

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

best response confirmed by Dtripple40 (Occasional Contributor)
Solution

@Dtripple40 

See the attached version.

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 

@Dtripple40 

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.

S0129.png

In the screenshot, you'll see that the weights vary, but all Broad values are the same, as are all Vert values, etc.

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 

@Dtripple40 

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

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

@Dtripple40 

What is your question?

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.

@Dtripple40 

Is this better?

 

 

Yes! Thank You! This is big time!