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.