Posted on

Spreadsheets: Excel: how to write text across multiple columns

Self-tutoring with Excel: the tutor shares a tip about extending text over multiple columns.

Let’s imagine you want to title a spreadsheet. You probably want to center the title at the top: likely it will run across more than one column, especially if the title font is larger than inside the sheet.

Here’s how I found to do so:

  1. Select the row across the top.
  2. Right-click the selection, then choose Format Cells.
  3. Choose the Alignment tab.
  4. Under Horizontal, click the dropdown menu to reveal Center Across Selection.
  5. Click Center Across Selection, then OK.

Source:

answers.microsoft.com

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

Posted on

Spreadsheets: Excel: the pi() function

Tutoring math, your curiosity naturally extends to spreadsheets. The tutor points out a neat feature of Excel.

If you type

=pi()

in a cell, the value of π will appear. I find that 14 decimal places are available.

Source:

www.math.com

www.engineerexcel.com

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

Posted on

Excel: how to type a forward slash, Excel 2016

Using Excel can mean self-tutoring. The tutor shares an observation about typing the forward slash in Excel 2016.

I went to type a forward slash (as a character, not part of a formula) at the lead of a statement in a cell using Excel 2016, but I couldn’t. That’s because, in my experience, typing the forward slash in a cell activates shortcut keys for the menu items across the top.

To type a slash (as a character) in a cell, here’s what I do:

  1. Select the cell in which I want it.
  2. Click in the separate text box above the cells.
  3. Type what I want, then press Enter. The text, slash and all, appears in the cell.

Cheers:)

Source:

www.howtogeek.com

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

Posted on

Excel: Analysis ToolPak

Tutoring spreadsheets, you might get asked about add-ins. The tutor mentions the Excel Analysis ToolPak add-in.

As I understand, Excel add-ins are extra tools that are available with Excel but not installed unless requested.

I installed the Analysis ToolPak add-in today. More or less, this is how I did so (Excel 2007):

  1. Click the top-left Office button (the four colored squares in a circle), then Excel Options, then Add-Ins.
  2. There are many add-ins displayed; the top one says Analysis ToolPak, and I select it.
  3. Near the bottom is the word Manage, with a menu beside it. Making sure Excel Add-ins is selected, I click Go.
  4. Now a check-box list appears. I check the box beside Analysis ToolPak and click OK.
  5. At this point a dialogue pops up asking if I want to install the Analysis ToolPak. I click Yes.

I installed the Analysis ToolPak because it offers a number of functions, including making a histogram. I’ll tell more about it in future posts:)

Source:

support.office.com

support.office.com

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

Posted on

Excel: Conditional Formatting, part II

Tutoring computer use, you might talk about conditional formatting with Excel. The tutor mentions a quick example.

You can make up your own condition that causes cells to format specifically. It might be done, more or less, like so:

Let’s imagine you want to format all cells in the range f3:f10 so that values above 70 appear in green. You would select that range.

Next, on the Home panel, you can click Conditional Formatting, then click New Rule. A list of choices is shown, at the bottom of which is “Use a formula to determine which cells to format”. Clicking that option, you’ll see a label “Format values where this formula is true”.

In the box, key in =f3>70. Next, click the Format button.

Various fonts and effects are offered. The Color option, under the Font tab, appears at centre right, and might be easy to miss; it’s a drop-down menu.

After selecting the effects and colors desired, one has to click OK a couple of times to invoke the conditional formatting:)

Source:

support.office.com

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

Posted on

Excel 2016: formula not calculating

Home computer use can mean self-tutoring. The tutor shares an idea about cell formatting in Excel.

Yesterday, using Excel 2016, I began a new spreadsheet and discovered a formula wouldn’t calculate. When I entered, for instance, =6+7, it wouldn’t evaluate to 13; the cell would simply continue to say =6+7. I’ve never encountered that behaviour.

I looked around the internet for a solution. The cell, many suggested, might be formatted as Text, in which case it would remain what was literally entered.

Checking its format, however, it said General.

Perhaps I accidentally changed the format to Text. (I can’t easily imagine that happening.) Anyway, what I seem to have learned is that just changing the format, once it’s been Text, won’t cause the formula to calculate. First you need to delete the cell’s contents, then change its format to General (for instance), then re-enter the formula. Then, the formula will calculate.

Source:

answers.microsoft.com

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

Posted on

Excel: finding loan payment at the beginning of the month

Tutoring financial math, loan payments are calculated. The tutor mentions how Excel handles payments at period beginning, as opposed to period end.

In my post of October 5, I show how to find the monthly payment of a loan using Excel. If not stated, the payment is assumed to be at month-end.

What if a loan has the payment at the beginning of the month?

Example:

Calculate the monthly payment on a 25-year, $100,000 loan at 4% compounded monthly, if the payment is made at the beginning of each month.

Solution:

=pmt(4%/12, 300, 100000,0,1)

Hopefully you receive the answer -526.08. (Negative means paid out as opposed to received.)

The fourth and fifth arguments aren’t present in my earlier post. The fourth is for the loan’s future value (assumed 0 if not specified). The fifth is for loan type: 0 for month-end payment, but 1 for beginning of month. The fifth is assumed 0 (month-end) unless given.

The parallel situation with annuities is annuity due, where payments are received at month beginning. More about that here.

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

Posted on

Spreadsheets: Excel: finding the monthly loan payment

Tutoring financial math, spreadsheets might be used. The tutor shows how to find the monthly payment on a loan using Excel.

In yesterday’s post I tell how to find the amount against the principal that a certain payment removes, using the ppmt function.

Perhaps more directly, a person might wonder how to calculate the monthly payment in the first place. Here’s how:

Example: Imagine a 25-year, $100,000 loan at 4% compounded monthly, with monthly payments as well. Find the monthly payment.

Solution: Using Excel, you’d enter

=pmt(4%/12, 300, 100000)

300 means the total number of payments: 12 per year for 25 years. The payment is assumed to be at month’s end.

Hopefully, the answer will come to -527.84, the negative meaning money paid out (as opposed to received).

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

Posted on

Excel: payment against principal

Tutoring financial math, amortization arises. The tutor mentions the Excel function for it.

Example: For a 25-year loan of $100,000 at 4%, compounded monthly with monthly payments, what is the amount against the principal of the 101st payment?

Solution:

Using Excel, it would be =ppmt(4%/12, 101, 300, 100000), which gives -271.30 (meaning a reduction of 271.30 against the principal).

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

Posted on

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.