Excel Formulas

"Hey hey! So, we're buying a new printer for the office, and we're trying to decide between these two: the Nidi 2600 and the Ace X1.

Can you help us figure out which one would be cheaper in the long run? Keep in mind that we probably won't buy another printer for at least three years. Also, we're printing an average of about 12,000 sheets per year."

Building the spreadsheet

It looks like our coworker Carter has already done some research and collected information about each of these printers:

This is all useful information, but it's not very consistent or well organized. In order to compare these two printers, we'll need to make sure our spreadsheet organizes the data consistently, which means we'll need to move some things around.

If you download our spreadsheet, you'll see that we've rearranged the printer cost, cartridge cost, and number of sheets per cartridge so we have a clear, side-by-side comparison of the two printers. We've also added the total number of sheets printed per year, since we'll need to use that value in our formulas.

See how this format makes it easy to compare the different information for each printer? Now that we have our spreadsheet organized, we're ready to start solving this problem.

How can we answer this question?

We'll use the spreadsheet to answer this question: Which printer will be less expensive over the course of three years? The cost of each printer will be determined by two things:

• The original cost of the printer
• The cost of toner cartridges needed over a three-year period

Writing the formulas

We already know the original costs of the printers—those values are stored in cells B2 and C2. This means we'll just need to figure out the total cost of the cartridges over three years. There are many different ways to go about this, but to make it simpler we'll break it down into multiple steps.

First, we'll calculate how many toner cartridges we'd need to purchase each year for each printer. We know the office prints about 12,000 sheets of paper per year (that value is stored in cell F2). If we divide that by the number of sheets per cartridge, we can determine how many cartridges we'll need over one year. Here are our formulas:

• In cell B5, type =$F$2/B4
• In cell C5, type =$F$2/C4 (Note: You can also just copy and paste the formula from cell B5.)

OK, it looks like we'd need to purchase 24 cartridges over one year for the Nidi and only 12 for the Ace.

Next, we'll calculate how much money we'll spend each year on cartridges. Because we just calculated the number of cartridges, we just need to multiply that value by the cartridge price:

• In cell B6, type =B5*B3
• In cell C6, type =C5*C3 (or copy the formula from cell B6)

It looks like cartridges would cost $420 each year for the Ace and $600 each year for the Nidi. Next, we'll multiply those values by 3 to determine the cost for three years. Here are our formulas:

• In cell B7, type =B6*3
• In cell C7, type =C6*3

Finally, we'll add the printer cost and the cartridge cost over three years to determine the total cost over three years for each printer. Here are our formulas:

• In cell B8, type =B7+B2
• In cell C8, type =C7+C2

And there's our answer! As you can see, the Ace X1 is cheaper than the Nidi 2600 in the long term, even though the upfront costs for the Nidi are much lower. And because the yearly cartridge cost for the Ace X1 is much lower, we'll continue to save money the longer we keep this printer.

"Excellent work here! I didn't expect the Ace to be such a good deal…

…but I guess the savings from those bigger toner cartridges really add up over time! Maybe we can have a pizza party with the extra money we'll save!"