-6+6*6 = 25. No, that isn't right...

November 10, 2009 3:48 PM

Any Excel whizzes out there? I'm having a problem that I just know is going to have a head-slappingly simple solution, but I still can't figure it out.

Okay, so here's the skinny:

I have an Excel spreadsheet I've been using to keep track of the various debts I owe. One of those debts is a personal loan I took out thinking that it would be a debt consolidation loan...another topic for another time. What I want to have Excel do is give me the difference of $19,268.11 (the total loan amount including interest) and x (the number of payments remaining on the loan multiplied by the amount I have to pay each month). The formula bar for that cell looks like this:

=19,268.11-((60-C2)*(321.2))

C2 is x, which at this moment is 10. The trouble is, it's not coming out right. If I do just the part to the right of the minus sign, I get 16060; so far so good. When I include 19,268.11, though, it comes out to 3208.11. What gives?

Lightning round: how would I have the difference display itself as dollars and cents rather than just a number?

posted by andrewcilento to computers & internet (8 answers total)

Try removing the comma.
posted by jeffamaphone at 3:51 PM on November 10, 2009

If you want to format a cell as money, click the $ in the ribbon at the top, or Right Click the cell, select Format... and then find the thing that lets you say "currency."
posted by jeffamaphone at 3:52 PM on November 10, 2009

1: The formula is correct, maybe you mean to put a 50 in for C2 (instead of 10)?
2: Right click the cell the formula is in and select Cell Format (then select format as Currency)

Should solve both problems...
posted by milqman at 3:54 PM on November 10, 2009

What are you trying to calculate?

If the 16,060 is correct and you take the 19,268.11 and subtract 16,060 you get 3,208.11.

Lightning Round: Right Click - Format Cells - In the number tab select Currency.
posted by kasperj74 at 3:56 PM on November 10, 2009

Hold on, that's right, isn't it?

19268.11 - 16060 = 3208.11

Or am I missing something about your calculation?
posted by Electric Dragon at 3:57 PM on November 10, 2009

Also you are inconsistent about how you describe x... it is either the amount remaining (as you described above the formula), or it is the number of payments remaining (C2 described below the formula).
posted by milqman at 3:58 PM on November 10, 2009

Well, thanks to kasperj74, I realized that I was making this more difficult than I need to and figured it out in the process!

milqman: "Also you are inconsistent about how you describe x... it is either the amount remaining (as you described above the formula), or it is the number of payments remaining (C2 described below the formula)."

Ach, yeah, you're right. In trying to word this question right, I wasn't clear. Some English major I turned out to be!
posted by andrewcilento at 4:02 PM on November 10, 2009

Yep, and removing the "60-" fixed it.
posted by andrewcilento at 4:04 PM on November 10, 2009