0

I'm attempting to run a STDEV.P on a specific population that has 3 criteria. I did this with AVERAGEIFS and it worked perfectly.

Here's the equation I have:

{=STDEV.P(IF((Adjusted_Player_Data!A:A=Sim_FG!A3,Adjusted_Player_Data!AK:AK=SImulated_Outcome!H3),Adjusted_Player_Data!K:K,""))}

Adjusted_Player_Data!A:A is a player name list
Sim_FG!A3 is the player I'm looking up
Player_Data!AK:AK is the scale names (Low, Med, High)
Simluated_Outcome!H3 is the scale I am trying to match. 
Adjusted!K:K is the range I am running the STDEV.P against.

Basically I need to match player and scale from big list. My averageifs function works perfectly and this is what it is: =IF(SImulated_Outcome!M2="Y",AVERAGEIFS(Adjusted_Player_Data!K:K,Adjusted_Player_Data!A:A,Sim_FG!A3,Adjusted_Player_Data!AK:AK,SImulated_Outcome!H3),"")

I've found answer below on nesting but now I want to include another criteria. If M2=Y then run a similar STDEV.

=IF(SImulated_Outcome!M2="Y",STDEV.P(IF(Adjusted_Player_Data!A:A=Sim_FG!A3,IF(Adjusted_Player_Data!AK:AK=SImulated_Outcome!H3,IF(Adjusted_Player_Data!AP:AP=1,Adjusted_Player_Data!K:K),STDEV.P(IF(Adjusted_Player_Data!A:A=Sim_FG!A3,IF(Adjusted_Player_Data!AK:AK=SImulated_Outcome!H3,IF(Adjusted_Player_Data!AP:AP=0,Adjusted_Player_Data!K:K))))))))

Ben
  • 75
  • 7

2 Answers2

1

You need to use nested IFs in STDEV.P:

=STDEV.P(IF(Adjusted_Player_Data!A:A=Sim_FG!A3,IF(Adjusted_Player_Data!AK:AK=SImulated_Outcome!H3,Adjusted_Player_Data!K:K,""),""))
xidgel
  • 3,085
  • 2
  • 13
  • 22
  • One more question. What if I want to add another criteria IF M2 = Y then run it. This my equation and I'm getting #NUM! I tried nesting it and still having issues. `=IF(SImulated_Outcome!M2="Y",STDEV.P(IF(Adjusted_Player_Data!A:A=Sim_FG!A3,IF(Adjusted_Player_Data!AK:AK=SImulated_Outcome!H3,IF(Adjusted_Player_Data!AP:AP=1,Adjusted_Player_Data!K:K),STDEV.P(IF(Adjusted_Player_Data!A:A=Sim_FG!A3,IF(Adjusted_Player_Data!AK:AK=SImulated_Outcome!H3,IF(Adjusted_Player_Data!AP:AP=0,Adjusted_Player_Data!K:K))))))))` – Ben Jan 14 '21 at 18:14
  • Also tried this and getting a #DIV/0! `=IF(SImulated_Outcome!M2="Y",STDEV.P(IF(Adjusted_Player_Data!A:A=Sim_FG!A3,IF(Adjusted_Player_Data!AK:AK=SImulated_Outcome!H3,IF(SImulated_Outcome!M2=1,Adjusted_Player_Data!K:K,""),STDEV.P(IF(Adjusted_Player_Data!A:A=Sim_FG!A3,IF(Adjusted_Player_Data!AK:AK=SImulated_Outcome!H3,IF(SImulated_Outcome!M2=0,Adjusted_Player_Data!K:K,""),""),""))))))` – Ben Jan 14 '21 at 18:29
1

For your follow-up question, your closing parentheses ) are in the wrong place. I think this is what you want:

=IF(SImulated_Outcome!M2="Y",
   STDEV.P(IF(Adjusted_Player_Data!A:A=Sim_FG!A3,IF(Adjusted_Player_Data!AK:AK=SImulated_Outcome!H3,IF(Adjusted_Player_Data!AP:AP=1,Adjusted_Player_Data!K:K)))),
   STDEV.P(IF(Adjusted_Player_Data!A:A=Sim_FG!A3,IF(Adjusted_Player_Data!AK:AK=SImulated_Outcome!H3,IF(Adjusted_Player_Data!AP:AP=0,Adjusted_Player_Data!K:K)))))
xidgel
  • 3,085
  • 2
  • 13
  • 22
  • I'm still getting werid numbers here. I'm getting a 0 if M2=Y and then the correct answer it looks like for when it doesn't equal Y =IF(SImulated_Outcome!O2="Y",STDEV.P(IF(Adjusted_Player_Data!A:A=Sim_FG!A3,IF(Adjusted_Player_Data!AL:AL=SImulated_Outcome!I3,IF(Adjusted_Player_Data!AQ:AQ=SImulated_Outcome!N3,IF(Adjusted_Player_Data!AR:AR=1,Adjusted_Player_Data!O:O))))),STDEV.P(IF(Adjusted_Player_Data!A:A=Sim_FG!A3,IF(Adjusted_Player_Data!AL:AL=SImulated_Outcome!I3,IF(Adjusted_Player_Data!AQ:AQ=SImulated_Outcome!N3,IF(Adjusted_Player_Data!AR:AR=0,Adjusted_Player_Data!O:O)))))) – Ben Jan 14 '21 at 21:27