STDEV.S/IF Formula Explanation

Arts · Oct 6, 2023

Hi All

I spent hours upon hours trying to figure out why I was getting different answers from using the STDEV.S Formula over the same range

What I was trying to achieve was to calculate the STDEV.S from my data which was collated with "Team A" and "Team B". I first did this separately and when calculating for "Team A" I go the outcome which all seemed fine. I then decided that rather than splitting data into separate sheets I could exclude the results for each team by using an IF formula within the STDEV.S function.

This is where I spent most of the day trying to figure out why I was getting different results.

I initially had the formula =STDEV.S(C3:C153) which was returning me 216.50 (this was all the results for Team A) then found out I could use (from online searches) =STDEV.S(IF($B$3:$B$250=$G$2,$C$3:$C$250,)) but this was returning me 183.36 and I couldn't figure why until by pure chance I deleted the "," to use =STDEV.S(IF($B$3:$B$250=$G$2,$C$3:$C$250)) which returned the 216.50.

Why would the extra "," matter if this was being left blank giving me differing results over the same range ? If someone could please explain that would be most appreciated.

Name	Count	Standard Deviation	Team A	Team A
Team A	-111	216.5016118	183.3607	216.5016
Team A	-25
Team A	-24
Team A	-16
Team A	-15
Team A	-15
Team A	-14
Team A	-13
Team A	-13
Team A	-13
Team A	-12
Team A	-12
Team A	-12
Team A	-11
Team A	-11
Team A	-11
Team A	-9
Team A	-9
Team A	-9
Team A	-9
Team A	-8
Team A	-8
Team A	-8
Team A	-8
Team A	-8
Team A	-8
Team A	-8
Team A	-8
Team A	-7
Team A	-7
Team A	-7
Team A	-7
Team A	-7
Team A	-6
Team A	-6
Team A	-6
Team A	-6
Team A	-5
Team A	-5
Team A	-4
Team A	-4
Team A	-4
Team A	-3
Team A	-1
Team A	-1
Team A	0
Team A	0
Team A	1
Team A	1
Team A	2
Team A	4
Team A	5
Team A	5
Team A	5
Team A	5
Team A	7
Team A	7
Team A	8
Team A	8
Team A	8
Team A	9
Team A	10
Team A	16
Team A	20
Team A	21
Team A	24
Team A	25
Team A	26
Team A	26
Team A	30
Team A	33
Team A	34
Team A	36
Team A	39
Team A	50
Team A	55
Team A	58
Team A	63
Team A	71
Team A	74
Team A	76
Team A	81
Team A	82
Team A	83
Team A	91
Team A	91
Team A	95
Team A	96
Team A	98
Team A	105
Team A	106
Team A	106
Team A	110
Team A	116
Team A	116
Team A	116
Team A	124
Team A	139
Team A	139
Team A	154
Team A	157
Team A	157
Team A	164
Team A	167
Team A	174
Team A	175
Team A	175
Team A	176
Team A	181
Team A	185
Team A	200
Team A	203
Team A	228
Team A	250
Team A	257
Team A	257
Team A	262
Team A	265
Team A	280
Team A	288
Team A	288
Team A	288
Team A	288
Team A	288
Team A	288
Team A	313
Team A	313
Team A	315
Team A	318
Team A	343
Team A	368
Team A	372
Team A	391
Team A	426
Team A	432
Team A	441
Team A	448
Team A	469
Team A	492
Team A	520
Team A	520
Team A	584
Team A	659
Team A	707
Team A	721
Team A	740
Team A	778
Team A	798
Team A	856
Team A	906
Team A	906
Team B	-84
Team B	-18
Team B	-17
Team B	-15
Team B	-15
Team B	-15
Team B	-14
Team B	-14
Team B	-14
Team B	-14
Team B	-14
Team B	-13
Team B	-13
Team B	-13
Team B	-13
Team B	-12
Team B	-12
Team B	-12
Team B	-11
Team B	-11
Team B	-10
Team B	-10
Team B	-9
Team B	-9
Team B	-8
Team B	-8
Team B	-8
Team B	-8
Team B	-8
Team B	-8
Team B	-6
Team B	-6
Team B	-5
Team B	-4
Team B	-2
Team B	1
Team B	1
Team B	4
Team B	6
Team B	7
Team B	11
Team B	11
Team B	13
Team B	14
Team B	16
Team B	18
Team B	21
Team B	21
Team B	21
Team B	21
Team B	22
Team B	27
Team B	36
Team B	39
Team B	40
Team B	42
Team B	42
Team B	45
Team B	47
Team B	49
Team B	54
Team B	54
Team B	54
Team B	54
Team B	54
Team B	56
Team B	58
Team B	64
Team B	68
Team B	68
Team B	70
Team B	79
Team B	89
Team B	99
Team B	110
Team B	110
Team B	196
Team B	199
Team B	199
Team B	216
Team B	241
Team B	271
Team B	314
Team B	369
Team B	413
Team B	490
Team B	512
Team B	557
Team B	600
Team B	609
Team B	614
Team B	622
Team B	644
Team B	671
Team B	762
Team B	783
Team B	833

DanteAmor · Oct 6, 2023

For example:

Libro1

A

B

C

D

E

F

G

1

2

3

1

4

2

1.41421

0.70711

5

2

3

6

5

4

7

6

Hoja1

Cell Formulas
Range	Formula
F4	F4	=STDEV.S(IF(B3:B7=G2,C3:C7,))
G4	G4	=STDEV.S(IF(B3:B7=G2,C3:C7))
Press CTRL+SHIFT+ENTER to enter array formulas.

Arts said:
=STDEV.S(IF($B$3:$B$250=$G$2,$C$3:$C$250,))

When you put the comma, then the return value when the condition is not met is a 0. And the function then considers the zeros for the calculation.

Without the comma, the returned value is FALSE, and the function ignores text and logical values:

To evaluate a formula:

Evaluate a nested formula one step at a time - Microsoft Support

With the Evaluate Formula dialog box, you can see how a nested formula calculates its result by inspecting its intermediate calculations and logical tests.

support.microsoft.com

Regards
Dante Amor

Arts · Oct 6, 2023

Thank you so much! That clears that up, should have really asked earlier and saved myself hours

STDEV.S/IF Formula Explanation

Arts

Well-known Member

Excel Facts

DanteAmor

Well-known Member

Evaluate a nested formula one step at a time - Microsoft Support

Arts

Well-known Member

Similar threads

Forum statistics

Share this page

STDEV.S/IF Formula Explanation

Arts

Well-known Member

Excel Facts

DanteAmor

Well-known Member

Evaluate a nested formula one step at a time - Microsoft Support

Arts

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock