Software

Use one-way Excel tables to analyze data

Analyzing data doesn't have to be a headache-inducing endeavor. This example shows how you can use Excel's one-way tables feature to determine if a project will come in under budget without a lot of calculations.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Excel's one-way tables feature can help you make everyday decisions. For example, suppose you are given a budget of $100,000 to upgrade the PCs in your department. You know that that will entail a fixed cost of $20,000 over a three-month time period. Each PC will require an additional upgrade cost of $200. You have 50 PCs in the department.

At a unit price of $2,000 each (including hardware and software), how many PCs can you upgrade and still come in under budget? Follow these steps to let Excel figure out the answer to this question:

  1. Enter "Fixed Upgrade Cost" in A1.
  2. Enter 20000 in B1.
  3. Enter "Variable upgrade cost per unit" in A2.
  4. Enter 200 in B2.
  5. Enter "Unit Price" in A3.
  6. Enter 2000 in B3.
  7. Enter "Number of Units" in A4.
  8. Enter 50 in B4.
  9. Enter "Total Upgrade Cost" in A5.
  10. Enter =B1+(B4*B2)+(B4*B3) in B5.
  11. Enter "Total Upgrade Cost" in A9.
  12. Enter =B5 in B9.
  13. Enter 30 in C8, 35 in D8, and 40 in E8.
  14. Select B8:E9.
  15. Go to Data | Table.
  16. Enter B4 in Row Input Cell text box and click OK.

As shown in this example, you cannot upgrade all the PCs and still be under budget. However, you can upgrade at least 35 of them.

0 comments