# Tutoring statistics, the tutor is happy to share the CONFIDENCE.T() function from LibreOffice Calc.

My last couple of posts (here and here) I’ve talked about confidence intervals for the mean. Yesterday I mentioned finding one using Excel or LibreOffice Calc’s CONFIDENCE() function.

While the CONFIDENCE() function assumes the population standard deviation is known, I pointed out that, with sample size n≥31, the t-distribution approximates the normal closely enough that the sample standard deviation can be used. Today, I’ll make a direct comparison.

Yesterday’s post considered a sample mean of 67.3, known population standard deviation of 12.4, and sample size 42. The input

=confidence(0.05,12.4,42)

gave the result 3.75, meaning a confidence interval of 67.3±3.75, or 63.55 to 71.05.

LibreOffice Calc’s CONFIDENCE.T() function has the following format:

=confidence(1-confidence_level, sample_standard_deviation, sample_size)

Since it uses the sample standard deviation, CONFIDENCE.T() calculates the confidence interval from the t-distribution. By constrast, CONFIDENCE() takes the population standard deviation, so uses the normal distribution to calculate the confidence interval.

The following input

=confidence.t(0.05, 12.4, 42)

gives the result 3.864, implying a confidence interval of 67.3±3.864 or 63.44 to 71.16. Obviously this is not much different from the confidence interval 63.55 to 71.05 gotten using =confidence(0.05,12.4,42).

So, the CONFIDENCE.T() function seems to demonstrate that, for a sample size n≥31, the t-distribution approximates the normal distribution closely enough that the sample standard deviation can be used when the population standard deviation is unavailable.

HTH:)

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

# Tutoring statistics, you realize how convenient using a spreadsheet can be.

In yesterday’s post I mentioned some theoretical points about two-sided confidence intervals for the population mean.

On the practical side, if you simply need a confidence interval for the population mean, you can use Excel’s CONFIDENCE() function, which works the same on LibreOffice Calc. It has the following format:

=confidence(1-confidence_level, pop_standard_deviation, sample_size)

The formula assumes the population standard deviation is known. If not, you can just use a sample_size ≥31, calculate the sample standard deviation, and use it. This gives a pretty good approximation (see yesterday’s post).

The CONFIDENCE() formula gives the margin of error for the confidence interval. To get the actual lower and upper bounds, you both subtract and add its output to the sample mean.

Example:

Imagine an exam written by 706 students. A sample of 42 papers reveals a mean grade of 67.3 and standard deviation 12.4. Give a 95% confidence interval for the mean exam mark.

Solution:

The confidence level is 95% = 0.95, so the first parameter is 1-0.95=0.05.

In a cell, key

=confidence(0.05, 12.4, 42)

Hopefully, you obtain the output 3.75, which means the confidence interval for the mean is given by

67.3±3.75

or

63.55 to 71.05

Apparently the mean, with 95% confidence, is between 63.55 and 71.05.

HTH:)

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

# Tutoring spreadsheets, here’s the next level of complexity with the sumif() function: a separate test range.

In my March 9 post I brought up sumif(), pointing out that it will selectively add values within a given range.

sumif() offers one more level of flexibility: you can use a different test range from the sum range.

Example: Consider the following partial spreadsheet:

 A B 110 green 40 red 50 red 90 green 70 green

Let’s imagine that, above, 110 is in the A1 position.

The sumif() formula can be used with the following format:

Note that, in this case, the first parameter is the range to check for agreement with the condition, while the range of numbers to potentially add is the final parameter.

Continuing with the example, let’s imagine that the formula

=sumif(b1:b5,”red”,a1:a5)

is entered in cell c6. It will add only the cells adjacent to the value “red”, and return the value 90.

seems to work the same with Excel or LibreOffice Calc:)

Oracle Tutoring by Jack and Diane, Campbell River, BC.

# The tutor shows the neat function sumif() found on Excel and also on LibreOffice Calc.

At its simplest, the sumif() function adds the members of a range that meet a certain condition; those that don’t, aren’t included in the sum. For instance, let’s imagine a spreadsheet that contains the following values in cells a1 to a4:

101
96
55
78

Let’s imagine, for instance, that entered into a10 is the formula

=sumif(a1:a4,”<90")

It will return the result 133.

The sumif() function has more complex possibilities as well, which I will pursue in a future post:)

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

# Tutoring spreadsheet usage, cell references are important.

Let’s imagine you want a1 in the first sheet of a workbook to have the value of b1 in sheet 2.

Here’s how you can do so with text:

• Excel: type, in a1 of the first sheet, =sheet2!b1
• LibreOffice Calc: type, in a1 of the first sheet, =sheet2.b1

HTH:)

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

# The tutor explains how to change from scientific notation to regular number format in Excel or LibreOffice Calc.

In my post from Feb 14 I mention that 1.69e-05 equals 0.0000169. Written 1.69e-05, the number is in scientific notation. Perhaps the user is not familiar, or not comfortable with that format – what can be done?

1. Right-click the cell with the number in scientific notation.
2. Click Format Cells…
3. Click Number. There is a click box for the number of decimal places; if the number is in scientific notation with e-05 or such, you’ll likely need lots (maybe 10 or more). There are also choices for how you want the number to appear; select the one desired.
4. Click OK: Hopefully the number will now be in “ordinary” format.
5. You may need to widen the column to accommodate the number.
6. If there is a positive number after the e, the cell contains a large value: for example, 1e+06 is 1 000 000. In such a case, you probably won’t need more decimal places, but might need to widen the column to accommodate the number in regular format:)

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

# The tutor shows the seriessum() function, found on both Excel and LibreOffice Calc.

A geometric series has the form

Sn = cr0 + cr1 + … + crn-1

An example is

3 + 3*4 + 3*42 + … + 3*45

which might also be written

3 + 12 + 48 + 192 + 768 + 3072

With Excel or LibreOffice Calc, the function to evaluate that sum is =seriessum(a,b,c,d), where

• a is the number whose exponent increases each term (4, in the example just above)
• b is the initial exponent of a (0, in the example just above)
• c is the increment by which the exponent increases each term (1, in the example just above)
• d is an array of the coefficients of the terms. In the example just above, the coefficient is always 3. You’d need to enter {3,3,3,3,3,3} for d: one 3 for each term.

So, to evaluate 3 + 12 + 48 + 192 + 768 + 3072, you might enter

=seriessum(4,0,1,{3,3,3,3,3,3})

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

# The tutor shares a nice function that seems to work the same in Excel or LibreOffice Calc.

The frequency() function tells, from an array of values and another of categories, the frequency in each category. A potential use is to make a histogram.

Example: Organize the following values into categories:

49,55,81,63,99,48,77,37,86,93,88,97,73,78,65,67,71,60,90,55

Solution:

1. Enter the values into a range: let’s imagine you use b1:b20
2. Decide the categories you want. In this case, six might make sense: 0-49, 50-59, 60-69, 70-79, 80-89, 90-100
3. To the frequency() function, a category is an upper limit. Your category array, perhaps entered in range c1:c6, could be as follows:

49 59 69 79 89 >90

4. With the values entered in b1:b20, and the categories entered in c1:c6, you might select the range e1:e6.
5. Type =frequency(b1:b20,c1:c6) but don’t press Enter!
6. frequency() is an array function; you must press Ctrl+Shift+Enter.
7. Hopefully you receive, in e1:e6, the array 3 2 4 4 3 4

Btw: In LibreOffice Calc, the frequency() function is under ARRAY functions. In Excel, it’s a Statistical function (under More Functions).

HTH:)

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.