CODEWITHSUNDEEP
excelvbaconditional-statementsconfidence-interval
DanGitR
DanGitR

Reputation: 47

Conditional Confidence.t Excel

I've tried to calculate a conditional t-distribution confidence interval in excel using the IFS function. In my example column V is used to define the inequality conditions to select rows with values between 0 and 15 to calculate statistics on matching rows from column AO

I've tried calculating a conditional sample standard deviation stdevs as:

STDEV.S(IFS($V$8:$V$304>=0,$AO$8:$AO$304,$V$8:$V$304<15,$AO$8:$AO$304))

which I could then use within the CONFIDENCE.T function as:

=CONFIDENCE.T(0.05,stdevs,COUNTIFS($V$8:$V$304,">0",V$8:V$304,"<15"))

My use of IFS to select a range to calculate stdevs failed. What is the problem?

Other conditional statistics can be calculated built-in functions. For example, the conditional call for the average is:

=AVERAGEIFS($AO$8:$AO$93,Compare!$V$8:$V$93,>=0,$V$8:$V$93,<15)

and the equivalent conditional call for the count as used above is:

COUNTIFS($V$8:$V$304,">0",V$8:V$304,"<15")

It would be ideal if built-in STDEVS.IFS or CONFIDENCE.TIFS function was avaiable or could be easily created by modifying the VBA used within the existing AVERAGEIFS.

Is the VBA for AVERAGEIFS available for modification?

Upvotes: 0

Views: 114

Answers (1)

DanGitR
DanGitR

Reputation: 47

First, I used FILTER() to specify a conditional range to calculate the standard deviation stdevs of the data sample:

=STDEV.S(FILTER(Compare!$AO$8:$AO$93,(Compare!$V$8:$V$93>=0) *(Compare!$V$8:$V$93<15), ""))

Next, I used COUNTIFS() to calculate the sample size for the same conditional range:

=COUNTIFS($V$8:$V$93,">0",V$8:V$93,"<15")

Finally, I used CONFIDENCE.T to calculate the conditional t-distribution for a 95% confidence interval (alpha = 0.05 significance level):

=CONFIDENCE.T(alpha,stdevs,size)

Upvotes: 1

Related Questions