google-sheetsfiltergoogle-sheets-formulavlookupstdev

combining STDEV & VLOOKUP

I am trying to find the STDEV of a specific teams score based on the seasons completed games.

I want to accomplish this by using vlookup. I came up with this formula but it shoots a DIV/0 error.

=STDEV(vlookup(B19,'2021 Game Log'!A2:B,2,false))

basically now I have the STDEV of ALL AWAY GAMES.... I'd like to refine that and get the STDEV of ONLY the AWAY team that's listed in that games "B cell". The scores are listed in 2021 Game Log. (that make sense?)

https://docs.google.com/spreadsheets/d/1F2t8RDp1qDDoGEMCdYOOxGDlDQHyZNaDQ8nRr7qP3v4/edit?usp=sharing

enter image description here

Here is a screenshot that might help.

https://i.sstatic.net/AK1l0.png

Solution

  • the output of your:

    =VLOOKUP(B19, '2021 Game Log'!A2:B, 2, 0)

    is:

    3

    eg. it's a single value so it's same as if you would run:

    =STDEV(3)

    and that translates to error because there is only one value you trying to STDEV

    ... I believe this is what you need:

    =STDEV(FILTER('2021 Game Log'!B2:B, '2021 Game Log'!A2:A=B19))

    enter image description here