Math: Excel: entering complex numbers

Tutoring differential equations or complex variables, you might use a spreadsheet sometimes. The tutor gives a hint about entering complex numbers on Excel.

Excel does, indeed, handle complex numbers. Formulas for them are among the Engineering ones.

Apparently, to enter a complex number, quotes are needed around it. For instance, to obtain the product of 6-i and 2+i, you would enter

=improduct(“6-i”,”2+i”)

Hopefully you receive the answer 13+4i.

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

Statistics, spreadsheets: confidence interval for the mean, population standard deviation unknown: CONFIDENCE.T() function on LibreOffice Calc

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.

Statistics, spreadsheets: confidence interval for population mean: CONFIDENCE() function on Excel and LibreOffice Calc

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.

Spreadsheets: Excel or LibreOffice Calc: sumif(), continued

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:

=sumif(decision_range,condition,range_to_add)

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.

=sumif(decision_range,condition,range_to_add)

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

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

Spreadsheets: Excel or LibreOffice Calc: sumif()

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.

Spreadsheets: how to reference a cell on a different sheet in Excel and in LibreOffice Calc

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.

Spreadsheets: Excel, LibreOffice Calc: number formatting: how to get rid of E-05 (for example)

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.

Spreadsheets: how to use Excel to solve an equation

The tutor gives an example of using Excel’s Goal Seek.

Example:

Solve lnx + 15= 0.25x

Solution:

  1. Select a cell (let’s imagine e3), then name it X (or whatever name you prefer. See how to name a cell in my post here.)
  2. Select another cell (let’s imagine d3), then enter the formula =0.25*X
  3. Select yet another cell (let’s imagine c3), then enter =ln(X) + 15
  4. Select yet another cell (let’s imagine b3), then enter =c3 – d3
  5. Now, select the Data pane (it’s one of those across the top, along with Home, Page Layout, Formulas, etc). When you do, you’ll see a Data Tools area that will include What-If Analysis. Click that, then Goal Seek…
  6. A dialogue will open. In our case, we want b3 to have value 0. So for Set Cell we can just select b3.
  7. In the box To Value, we enter 0.
  8. Our variable is in the cell named X. In the By Changing Cell box we enter X, e3, or simply select e3.
  9. Clicking OK, we see the cells change as the answer evolves. Then a dialogue called Goal Seek Status opens, telling the solution if one is found. When you click OK, the solution (if found) will be applied to the relevant cells.
  10. In this case, it seems that X becomes 77.39566, giving b3 a value of 1.69e-5 which equals 0.0000169. Excel deems this value close enough to 0, and I tend to agree:)

HTH:)

Source:

Microsoft Excel 5.0 User’s Guide. Microsoft Corporation, 1993.

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

Spreadsheets: Excel and LibreOffice Calc: the sum of a geometric series

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})

Hopefully you receive the answer 4095:)

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