Go Back   TechArena Community > Software > Tips & Tweaks
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



How to create a table of assumptions in Excel 2007

Tips & Tweaks


Reply
 
Thread Tools Search this Thread
  #1  
Old 30-09-2009
Member
 
Join Date: May 2008
Posts: 176
How to create a table of assumptions in Excel 2007
  

With this trick, we'll show you how to create a table with two entries in Excel.

You want to compare loan offerings which vary in two parameters: the duration of the loan and the interest rate (the principle is, of course, for all types of double-entry table). Excel allows you to create the entire table while writing a single formula.

- Enter the term in the field B3: B7, interest rates in C2: F2, and the amount to be refunded in C9, negative because it is an amount received.

- You will now have two cells (B11 and B12 here) to be used for calculations but where you do not understand. In B2, enter the formula for the repayment of a loan. You will use for this the PMT function. The first argument, the interest rate, is B11/12 (cell B11, which contains the annual interest shall be divided by 12 because we have the monthly interest). The second argument (number of payments) is B12 * 12 or 12 times the number of years of repayment. The formula in B2 is =PMT(B11/12;B12*12;$C$9). Do not worry about the result (or error) that appears.

- Now select the field B2: F7 and pull down the Data menu, Table.

- In the field input cell line, type in cells B11 and input column, type B12, and click OK.

Your table is finished. You can hide (but not remove) the cell B2. To do this, right click on it, choose Format Cells, then Protection and select Hidden.

Reply With Quote
  #2  
Old 30-09-2009
Member
 
Join Date: May 2008
Posts: 4,828
Re: How to create a table of assumptions in Excel 2007

Quote:
You can hide (but not remove) the cell B2
How can you hide individual cells? I don't think it is possible or rather effective. However you can hide a full column or row. Simply right click the heading (column or row), then click Hide.
Reply With Quote
Reply

  TechArena Community > Software > Tips & Tweaks
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to create a table of assumptions in Excel 2007"
Thread Thread Starter Forum Replies Last Post
Need help to create cross Reference table/Matrix in Microsoft Excel Eas!war MS Office Support 2 15-02-2012 04:01 PM
How to create table and Modify tables in Microsoft Access 2007 Antonio1 Windows Software 7 26-11-2009 11:31 PM
Create html table from excel file Laler Windows Software 3 26-06-2009 08:44 AM
Pivot Table in Excel 2007 NIcaBoy Windows Software 2 02-03-2009 02:16 PM
Importing HTML table in a Spreadsheet - Excel 2007 Aarif Tips & Tweaks 1 01-12-2008 01:35 PM


All times are GMT +5.5. The time now is 03:09 PM.