Confidence Interval function with LAMBDA and Optional Parameters

-

Excel provides CONFIDENCE.T formula to calculate the confidence interval. This value, however, does not stand on its own. We usually want to know the upper and lower confidence limits as well. 

In this blog, I will implement the calculation of upper and lower confidence intervals using LAMBDA. I will also demonstrate how to implement optional parameters, if a different significance level from the default 5% is required.

But before that, I will introduce the LET function.

LET Function

The LET function assigns names to calculation results. This is useful for storing intermediate results or values, or defining names inside a formula. The general syntax is as follows:

=LET(name1, name_value1, [name2, name_value2,...], calculation)

It supports up to 126 name-and-value pairs. The last parameter calculation is the return result of LET. Let's see this in action.

Confidence Interval function

I will create a new function dcrConfidence that takes an array and calculates the confidence interval at 0.05 significance level.

Name: dcrConfidence

Comments: Returns the confidence interval for the mean of a sample at significance level alpha = 0.05.

Refers To:

=LAMBDA(array,
  LET(alpha, 0.05,
      stdev, STDEV.S(array),
      size, COUNT(array),
      confidence, CONFIDENCE.T(alpha, stdev, size),
      confidence
  )
)

By using LET we can do intermediate calculations and assign to named variables. This improves readability and also provides a way to output debug values if necessary, e.g. you can swap confidence with size if you want to return size.

Note: name-value comes as a pair, and the last parameter is the return value.

Add in optional significance level

I will modify dcrConfidence to calculate the confidence interval at an alpha significance level, but as optional input.

The ISOMITTED function is used to check if alpha is provided. If not, a default value will be set.

Name: dcrConfidence

Comments: Returns the confidence interval for the mean of a sample. Default alpha = 0.05.

Refers To:

=LAMBDA(array, [alpha],
  LET(alpha, IF(ISOMITTED(alpha), 0.05, alpha),
      stdev, STDEV.S(array),
      size, COUNT(array),
      confidence, CONFIDENCE.T(alpha, stdev, size),
      confidence
  )
)

The square brackets ("[" and "]") is a good practice to indicate optional parameters. It is also consistent with how Excel does with its other functions.

Add in optional return value

Since we are at this, I will add in a type return parameter. A SWITCH function is used to determine the what value to return. The table below is a reference of what to return when type is specified.

[type]
0 = interval
-1 = lower limit
1 = upper limit
5 = mean

The LAMBDA function is then changed as follows:

 
=LAMBDA(array, [alpha], [type],
  LET(alpha, IF(ISOMITTED(alpha), 0.05, alpha),
      type, IF(ISOMITTED(type), 0, type),
      mean, AVERAGE(array),
      stdev, STDEV.S(array),
      size, COUNT(array),
      confidence, CONFIDENCE.T(alpha, stdev, size),
      SWITCH(type, 0, confidence, -1, mean - confidence, 1, mean + confidence, 5, mean, "Type: Interval=0, Lower Limit=-1, Upper Limit=1, Mean=5")
  )
)

In the SWITCH function, I added a syntax error output. I would have liked to put the return table in the comments, but I found the comments does not display multiple lines correctly. Do try this to confirm for yourself.

Note: when implementing a LAMBDA function, do it in steps and verify each output. 

Confidence Interval Summary function

Lastly, I will implement a summary function using the dcrConfidence.

Name: dcrConfidence.Summary

Comments: Returns a summary of confidence limit for the mean a sample. Default alpha = 0.05.

Refers To:

=LAMBDA(array, [alpha],
  LET(alpha, IF(ISOMITTED(alpha), 0.05, alpha),
    HSTACK(
      VSTACK(
      "Confidence Interval",
      "Lower Confidence Limit",
      "Upper Confidence Limit"
      ) ,
      VSTACK(
        dcrConfidence(array, alpha,0),
        dcrConfidence(array, alpha,-1),
        dcrConfidence(array, alpha,1)
      )
    )
  )
)

The below screenshot shows an example of the dcrConfidence.Summary function.

Reflection

Instead of the summary function making 3 calls to CONFIDENCE.T function, I could switch this around, i.e. compute the summary array first with a single CONFIDENCE.T call, and then extract the interval, upper and lower values from the array. This would be computationally less intensive. I have however kept it this way for consistency with other summary functions that I will be showing in other blogs.

Although not shown here, I do verify the results manually. Pre-calculating some values before hand and then comparing against the LAMBDA results, would give yourself more confidence in your implementation.

Now that you know about implementing optional parameters, go apply it in your DC-DEN!

Comments

Post a Comment