CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
IronKirby
IronKirby

Reputation: 708

Standard Deviation IF Excel {} Formula Issues

Like many of the STDEV IF questions asked I have checked my format to see whether it was in an array formula, yet it still does not work.

The data is arranged in a column format with some fields, provided they have no data, having a NA() formula applied. 

=IFERROR(ABS(AVERAGEIFS('Daily data'!$I$5:$I$1865,'Daily data'!$A$5:$A$1865,Engine!C68)),NA())

This is the STDEV IF formula I am using:

={STDEV.S(IF(D5:D82>"#N/A",D5:D82))}.

I have tried different variations including using the NA() function inside the STDEV IF formula but it hasn't worked either.

Could anyone point out what the problem could be?

Upvotes: 1

Views: 3350

Answers (1)

user4039065
user4039065

Reputation:

The AGGREGATE function has both STDEV.S function (7) and STDEV.P function (8) sub functions and be asked to ignore errors (option 6).

=AGGREGATE(7, 6, D5:D82)   '◄ STDEV.S
=AGGREGATE(8, 6, D5:D82)   '◄ STDEV.P

Your original array formulas¹ could have been written as,

=STDEV.S(IF(ISNUMBER(D5:D82), D5:D82))
=STDEV.P(IF(ISNUMBER(D5:D82), D5:D82))

¹ Array formulas need to be finalized with Ctrl+Shift+Enter↵. If entered correctly, Excel with wrap the formula in braces (e.g. { and }). You do not type the braces in yourself. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.

Upvotes: 2

Related Questions