Excel assistance please

Factual Questions
1

I am using the wonderful spreadsheet by Vertex42.com and am using it to reduce all my cards to $100 under 50% utilization across the board before fully paying any individual card off unless the minimum payment becomes the outstanding balance first.

The way his spreadsheet works is you enter the amount you can pay for the month and it uses that to figure out how much over the min.payment to make on each debt.

The way I’m doing that is in the reduce to field I have the following formula
=IF(C9<((G9/2)-100),“”,(G9/2)-100)

The C column holds the current balance and the G column is the credit line.

What I’d like to do is alter my formula for the reduce field so if the balance is reduced to 84% if the card is not already there or lower.

Would someone assist me please

2

You can ask the excel guy(user name Intention). Not sure if he’s still around but he knew his shit. Here’s a link to the Ask thread: http://boards.straightdope.com/sdmb/showthread.php?t=398567&highlight=excel

3

Thanks but since he hasn’t been here since 2010 I don’t thunk posting to a zombie thread will help.

4

The way I read the formula you posted is that it will show a blank cell if the current balance is less than 50% of the credit line minus $100, otherwise it will show the value of 50% of the credit line minus $100.

And you are wanting that to be 84% of the credit line instead?

If that is what you’re asking, I think the formula would be:

=IF(C9<(G90.84),"",(G90.84))

So if your credit line is $1000, C9 will show $840 if C9 is >= $840, otherwise it’ll be blank.

5

Put the percentage, 84%, in a different cell, so that you can adjust it and see the effects. Change the formula to =IF(C9<G9*<percentage>,"",G9*<percentage>), where “<percentage>” is the cell reference.

Incidentally, it would be easier to understand your question if you used proper punctuation, such as “in the ‘reduce to’ field I have the following formula”. I had to read your post a few times to make sense of it.

6

Sorry I forgot to to include a link to the original spreadsheet It’s here

The license for his spreadsheet allows it to be customized for personal use.

If you look at his spreadsheet there is a column called Reduced To. Rather than using his normal formula to pay down your debt it alters your payment guide to reach the amount you have in reduced to first.

What I want to do is make that field dynamic based on what % of each card I’m using is. Previous versions of Excel allowed me to you the function wizard to make a nested function but 2010 doesn’t seem to offer that.

So to explain a little better (It’s a little past 3:30am here)

If the outstanding balance on the card is more than 90% then reduce it to 90%.

If the outstanding balance on the card is more than 84% then reduce it to 84%

If the outstanding balance on the card is more than 49% then reduce it to 49%

Otherwise the reduce to field is blank

7

I didn’t see your post. That’s almost what I wanted. Originally if the outstanding balance was more than100 dollars less than my credit limit, it would set my goal to be 100 dollars less than my credit limit.

If my current balance under my credit limit by $100 or more it would just make that field blank.

Now I want to make it choose from four values in that reduce to field 90% , 84% 49% and then blank if what I owe is less than 49% of my credit line

8

Here’s a guide for what it sounds like you want.

Edit: Another option would be to use conditional highlighting. Basically select the range you want to apply this to. Then make a highlight rule to set the 49% green, the 84% yellow, and the 90% red or something similar.

9

No that’s nothing like I want. But thanks for trying. Conditional highlighting would work if I wanted to manually do the math and enter the new reduce to goal but I don’t.

I need it to automatically choose the next goal to reduce my debt to based on the current debt to credit limit ratio

10

Oh, I should say that I’m by no means an Excel expert…just a hack. I mostly brute-force my way through problems - there is probably a more elegant solution.

I think Ximenean is on the right track with having separate columns or some way to show the target percentage, then you would know where you are in your reduction plan, and it would make the formula a bit more straightforward, but I think this is what you’re asking for:

=IF(C9<(D90.49),"",IF(C9<(D90.84),(D90.49),IF(C9<(D90.9),(D90.84),(D90.9))))

So, if your balance is less than 49% show a blank, else if the balance is less than 84%, show the reduce-to target of 49%, else if the balance is less than 90%, show the reduce-to target of 84%, otherwise show the reduce-to target of 90%.

When making nested “IF” statements, I like to write out each condition individually, and then “roll-up” by copying/pasting into the equation steps. Building them is fairly easy, but when they get long, it gets to be a bit of a pain to debug. So, this is sorta what I do:

IF(C9<(D9*0.49),"",[else])

IF(C9<(D90.84),(D90.49),[else])

IF(C9<(D9*.0.9),(D90.84),(D90.9))

And just “roll-up” the copy/paste from the bottom into the appropriate [then] (if there are any) or [else] clauses.

11

Thanks for the help. In Excel there is no else clause but I received a solution from another site.

12

Correct. The point he was making was that you would write out the three separate conditionals, then copy the third conditional, replacing the second “[else]” then copy that extended line, replacing the first “[else]”. This would allow you to desk check the syntax of each conditional without getting lost in nested parentheses.

13

Could you post their solution? It’s fun (for low values of fun) to see solutions to interesting problems.

14

I don’t understand you saying Excel doesn’t use “else”. It doesn’t use the word “else”, but you can certainly make formulas that operate identically to IF-THEN-ELSE statements. In fact your OP formula is such a statement.