Excel formula problem

June 6, 2009 at 07:42:02
Specs: Windows XP

Hi there, I'm trying to construst a form in excel.

The purpose is to distribute available income between creditors on a pro rata basis. I have created the form with spaces for 25 creditors. I am using the calculation Individual debt ÷ by total debt x available income = pro rata payment. This works fine. I now want to take it a stage further. If i change a pro rata payment by inserting a manual payment (say the calculation was less than £1.00 and i wanted it to be £1) how do i get the remaining cells to recalculate to maintain the original value of the available income.

I would be grateful if anyone could help me
Thank you
Jane


See More: Excel formula problem

Report •


#1
June 6, 2009 at 09:11:45

Unfortunately, we can't see your spreadsheet from where we're sitting.

It might help if you posted the exact formula(s) you are using and what you are "manually" changing.

Off the top of my head, I could suggest wrapping an IF statement around your current formula. e.g.

=IF(current_formula<1,1,current_formula)


Report •

#2
June 6, 2009 at 10:03:40

Hi there,
sorry I'm new and I'm not very good at explaining complex excel formulas. I have uploaded a copy of my worksheet to my webspace and this is the link to it.
http://www.thetownsquare.co.uk/pror... I hope i'm not breaking any rules by doing this?
thank you for your response

Jane


Report •

#3
June 6, 2009 at 10:27:07

OK, so now that we can see your spreadsheet, please explain what you are trying to accomplish.

Details, my friend, details.


Report •

Related Solutions

#4
June 6, 2009 at 12:44:08

OK here goes.
I have a set amount of money to distribute to all of the creditors listed. That amount is in cell B3.
I have listed all the amounts I owe to each creditor in column D from cell D7 to D24.
At D25 there is the sum of D7 to D24.
In column G – From Cell G7 to G24 there is a formula.
At row 7 the formula is =SUM(D7/D25)*B3 which does the pro rata calculation and distributes the right amount of money to that creditor.
It does the individual debt ÷ total debt x available income
The same calculation is done over on each row in column D.
That part of the sheet works perfectly well and I am happy with it.

As you will see in the example sheet some of the payments to creditors are below £1. Creditors would normally accept a minimum payment of £1.00. So the next step I took was to incorporate rounding options, and what this does is round those payments of less than £1.00 to £1.00. (the option for £5.00 works the same way with payments of less than £5.00 being rounded up to £5.00)

The figures change in column F to the new payment when rounding up to £1.00 is selected. The formula references Cell J14 which changes when you go from No Rounding to Round to £1 and Round to £5.

Now we have managed to get the minimum payment rounding to work but not properly.

If you look at cell F25 this is the total from the range F7:F24 and should be equal to B3 (the available income) But when rounding is chosen the payments in column F exceed the available income in B3.

I need a formula which will allow me to round up the payments for the creditors as I choose but will also recalculate the remaining available income so that the payments do not exceed the available income.

I hope that makes sense
Thank you for your reply

Jane


Report •

#5
June 6, 2009 at 12:57:44

OK I spent some quality time with your sheet and I think I know what you want to do.

If what you are trying to do is force =SUM(F7:F24) (which I'll call the "The SUM") to always be equal to B3, I see a couple of issues.

First, I don't think you'll be able to it with formulae in the same column as The SUM.

Have you ever heard of a Circular Reference?

If I understand what you are doing correctly, you won't know the actual value of The SUM until you click an Option Button and round-up some varying number of cells. After you click the Option button, you'll need to compensate for the difference between The SUM and the value in B3. Therefore, you'll have to refer to The SUM in each of the formulae that you want to "adjust".

The problem is that if you refer to a cell within a formula that is dependent on that cell, Excel will throw up a Circular Reference error. Basically Excel is saying "I can't evaluate Formula 1 until I evaluate Formula 2 but I need the result from Formula 1 in order to evaluate Formula 2." That's your classic Circular Reference.

So at a minimum, that is going to mean another column of formulae where you use the "Overage" (The SUM - B3) to modify the values that haven't been adjusted by the rounding.

OK, so now let's say we have an Overage that we have to deal with. We can't simply divide the Overage by the number of values that haven't been rounded and then subtract that from each non-rounded payment because some of the payments may end up being less than zero.

So we would need to find a way to "prorate" the Overage across the non-rounded values based on some criteria. That criteria would be up to you.

But hold on a second - what will you do if you subtract the prorated amount from a non-rounded value and it ends up below the value that you rounded the other values up to? You'd then have to round that value up, which would change your Overage, and you'd have to recalculate a new prorated amount. I doubt there's any way you're going to do that sort of "looping calculation" with formulae. I'm guessing that even the VBA to do this would be pretty complicated.

At first glance, I think you have too many moving parts to accomplish your goal.

Maybe someone else will see something I've missed.


Report •

#6
June 6, 2009 at 15:32:30

Thank you for trying to help me.
I appreciate the time you have spent. I kept getting a circular reference and have tried in various ways to overcome this, all to no avail.

I know it can be done because i use a financial statement with this function in a programe called PGDebt - a specialist program for money advisers. I have also seen it on the "Common Financial Statement" devised by the British Bankers Association along with the Money Advice Trust. The latter being a protected excel spreadsheet.
I'm no programmer and i only have a limited knowledge of excel so it looks like i will have to draw a line under this one and accept it's beyond me.

I do appreciate the help and time you have given though. Thank you.
Jane


Report •

#7
June 6, 2009 at 21:09:25

If you seen it done in an Excel spreadsheet, I'd be interested in knowing what the result of the examples you used in your spreadsheet would be.

If I use the Round Up To 5 button, I have an overage of 39.56 and the following entries that were not rounded:

£30.17
£6.91
£24.86
£5.56
£7.05

If I spread the overage evenly over those 5 values (39.56/5 = 7.91) and then subtract that from each value, 3 of the 5 values would fall below 0, so that can't be right.

If I prorate the overage across those values by using something like:

=F9/(SUMIF($F$7:$F$24,">5")) for each value over 5, I'll get a percentage of the total of the values over 5 for each value over 5.

If I then subtract that percentage of the sum from each value that is over 5, I will indeed get a column of values that adds to 100, but the same three values will have dropped below 5 and have to be rounded up, changing the overage, which would then have to prorated again over the remaining 2 to force the sum to be 100 - assuming that that proration didn't knock one of those values below 5. You see what I mean when I say it's kind of a looping calculation?

Is it possible for you to put the same values you used in your example into the Common Financial Statement and tell me what the final results are after using either the Round Up to 1 or Round Up to 5 option?

Knowing the results that they were satisfied with might give us some clue as to how they are doing it.


Report •

#8
June 7, 2009 at 02:51:00

Hi there,
I have taken some screenshots of both the BBA financial statement and that of PGDebt with an ordinary pro rata, round up to £1 and Round up to £5 for each, They can be found here......
http://www.thetownsquare.co.uk/ProR...
http://www.thetownsquare.co.uk/roun...
http://www.thetownsquare.co.uk/roun...


http://www.thetownsquare.co.uk/PGDP...
http://www.thetownsquare.co.uk/PGDR...
http://www.thetownsquare.co.uk/PGDR...

What seems to happen is that PGDebt will not use the full £100 in its calculation but the BBA financial statement uses slightly more than £100.

PGDebt was unable to round the smallest to £5 although it was not clear why - the BBA FS handled it OK.

I would be happy if a small amount was unused if that was the only way to get it to work.

The BBA FS is protected and so i cannot see the formula or code behind it. If i select in the options to show the code it appears to be pulling the data from a hiden sheet for the calculations but nothing shows up in the box where the Round up figure is at cell L54.

Thank you for taking the time to look at this.
Regards
Jane


Report •

#9
June 7, 2009 at 14:26:29

I guess I have to start with this, not to say "I told you so", but to ask for clarification:

Earlier you said "I know it can be done because I use a financial statement with this function in a programe called PGDebt - a specialist program for money advisers. I have also seen it on the "Common Financial Statement" devised by the British Bankers Association along with the Money Advice Trust.

As far as I can tell, neither of those programs actually did it.

The first three links gave totals of 101.61 and 139.56, just like your spreadsheet did. I don't see any adjustments to get the total back to 100. Please explain.

The next three links come close, with the Round To 1 showing an adjustment to get back below 100, but as you said, it didn't do anything when trying to Round To 5.

Please explain what you meant when you said I know it can be done.

Now, for the one sheet where it appears to have come close, the one that Rounds To 1 and totals 99.92. I think I see how they did it.

If you take the total of the overage after rounding to 1, you get a very small amount: 1.61.

Now, divide that overage by the total of the values that didn't need to be rounded (SUMIF(F7:F24,">1")) and you get a percentage: ~1.6486%.

Finally, reduce each of the values that didn't get rounded by that percentage and you'll get the "new" values shown in the Round to 1 screenshot:

30.17*(1-.016486) = 29.67
3.02*(1-.016486) = 2.97
etc.

The only reason I think it works is because the overage is so small that it never forces any of the "adjusted" values below the "1" threshold so it never goes into the "loop" I've mentioned in my earlier responses.

However, if you tried to Round to 5, the overage would be much bigger. If you then applied the same "percentage logic" that I used earlier many of the values that did not have to be rounded up would end up being less than 5 and then have to be rounded up, changing the overage, and creating the dreaded loop.

Bottom line is that based on the examples you gave, I'm not convinced it can be done without some pretty complex logic.



Report •

#10
June 9, 2009 at 23:07:12

Hmmm I think you are right. When I look at it now I can see that neither program/spreadsheet is able to get the answer I want (Or thought I was getting). I have used PGDebt for over 14 years (various versions in that time) and mostly used the round to £1.00 when needed and so because I was used to that working I was convinced that the round to £5.00 function did as well. It was only this morning that I couldn’t get it to work. Now I believe that it was probably for the reasons you say in that to round to £5.00 with those figures would create the circular reference because it would need to keep adjusting the payments in the calculation and create the circular ref.

I also just looked more closely having taken out some of the figures, just to get it to work and this is what it did. The smaller payments were rounded up to £5.00 but some of the other payments were reduced to £5.00 and then others calculated using the remainder of the available income.

In over 14 years I have never realised what it was actually doing. But like I said I didn’t often use that particular function.

Now the BBA financial statement is something else. That was plain to see that it was not working, yet I completely missed it. I assumed that it worked because it was an accepted document in the money advice industry and creditors accepted it.

The one thing with the PGDebt financial statement is that when you input a manual payment to one of the creditors (this is another function) or round up to £1.00 for the smallest payments it recalculates the other payments based upon the reduced available income. I couldn’t get mine to do that.

Thank you for looking at it for me. I’m going to have to draw a line under it now because I don’t know excel well enough to construct complex formulas.
Jane


Report •

#11
June 10, 2009 at 09:41:55

when you input a manual payment to one of the creditors (this is another function) or round up to £1.00 for the smallest payments it recalculates the other payments based upon the reduced available income. I couldn’t get mine to do that.

If you need that function you could always add a column titled Manuel Payments and simply subtract the total from your available funds.

Make column H your manual payments using cells 7 thru 24
cell H25 is your =SUM(H7:H24)

Simply change your formula for Pro Rata Payments to:

=SUM(D7/$D$25)*($B$3-$H$25)

If you make the four payments that are less than 1.00 (H7,H10,H11,H17) equal to 1.00 in the Manual Payments column you should get:

Column G will total to 96.00
Column H will total to 4.00
Which totals your initial 100.00

It's not real fancy, but ...........

.

MIKE

http://www.skeptic.com/


Report •

#12
June 10, 2009 at 10:23:10

Mike,

Your suggestion does a great job of illustrating my earlier point about the looping that occurs as you round-up, or in your case, manually change the values.

For the 1.00 payment it works fine on the first pass, but try it for the 5.00 payment. Keep in mind that each time you increase a payment that is less than 5.00 to 5.00, you then have to go back and increase any payments than get recalculated to less than 5.00.

Try your method with 5.00 and let me know how many values you had to change. I'd like to know if you get the same results I did.


Report •

#13
June 10, 2009 at 10:49:35

Hi there,
I tried the suggestion of putting in another column for manual payments and at first glance it seems to work, but the problem is that the value in column D is used in the pro rata calculation and so a proportion of the available income is allocated to that value.

If I then reduce the available income down by the amount in column H to reflect a manual payment. I just reduce the available income down. It doesn’t ignore the corresponding figure in column D and then recalculate on a pro rata basis for the remainder.

The result I got was £93.70 at F25 and £4.00 at H25

If I then substituted all payments beneath £5.00 to a manual £5 in column H the result is that all payments would need to be £5.00 and the sum at H25 would be £90 and not £100, and Zero at F25

I do like the manual payment column though, and I put in a bit of extra formula so that if there was a number > 1 in column H is would return £0.00 in column F, making it easier to see what payments need to be made (I also turned of zero in the options so nothing is displayed is the cell contains 0)

=IF(H7>=1,0,SUM(D7/$D$25)*($B$3-$H$25))

Whilst it doesn’t use the whole of the available income I’m pleased it doesn’t use more than the available income.


Report •

#14
June 10, 2009 at 10:57:53

I tried the 5.00 figure where every amount that was less than 5.00 was updated to equal 5.00 and it was a mess. Every cell had to be recalc'ed.

But, if you did just the cells that were less than 1.00 and updated to 5.00 then only one additional cell needed to be updated.

But with only 100.00 in the pot and 24 creditors, you can only spread it so far.

MIKE

http://www.skeptic.com/


Report •

#15
June 10, 2009 at 11:13:45

Every cell had to be recalc'ed

Exactly!

The looping that occurred when rounding to 5.00 is why I feel that there isn't any way to automatically get the payments to equal the available funds by strictly using formulae.

However, I'm wondering if the problem is based on the example data we're using. If the values in the example are not real world data, then we may be beating our heads against the wall for nothing.


Report •

#16
June 10, 2009 at 11:48:06

I can assure you that as a debt adviser i do see people with debts just like that and more. £40,000 of debt is not unusual. If someone has disposable income of more than £100 I would not necessarily be advising that they distribute it themselves but perhaps applied for an IVA (which is why the available income figure was £100)

Report •

#17
June 10, 2009 at 12:16:12

Jane,

What's an IVA?

I didn't mean to doubt the accuracy of the figures. In fact, it wasn't the overal debt that I was questioning. It was more the allocation of .88 (rounded to 1) to a given creditor that looked strange.

I've been in debt - I still have debts - and there have been times when things were pretty tight. That said, I don't think I've ever had to say, "Well, I was going to send them .88, but their minimum is 1.00. I guess somebody else gets shorted."

When it gets to the point where I'm only sending in an .88 payment, I'm thinking it's time for a little consolidation i.e. fewer payments of larger amounts, but still totalling the 100 I have to spend.

Anyway, if Mike's manual method works for you, then you're all set. I hope I've offered some degree of help.


Report •

#18
June 10, 2009 at 13:44:31

To increase one you must decrease another (Old programmers proverb).
Try this:

Starting with your original sheet.

Insert four blank columns next to Column D

In column E cell 7 enter the formula:

=SUM(D7/$D$25) and copy down to cell 24

put your =SUM(E7:E24) in cell E25


In column F cell 7 enter the formula:

=IF(E7<1%,1%-E7,0) and copy down to cell 24

put your =SUM(F7:F24) in cell F25


In column G cell 7 enter the formula:

=IF(E7=MAX(E5:E22),E7-F23,E7+F7) and copy down to cell 24

put your =SUM(G7:G24) in cell G25


In column H cell 7 enter the formula:

=G7*$B$3 and copy down to cell 24

put your =SUM(H7:H24)


All the action takes place in the G column
we find the =MAX payment percentage (who we are going to pay the most)
and decrease the payment by the total of the increase to those cells that are less then 1.

It would be nice if we could spread the difference between all the creditors but for now, this is one way of doing it.

It’s not bulletproof but………

MIKE

http://www.skeptic.com/


Report •

#19
June 10, 2009 at 14:56:18

OOPS, forgot to anchor the formulas in G column:

=IF(E7=MAX($E$7:$E$24),E7-$F$25,E7+F7)

MIKE

http://www.skeptic.com/


Report •

#20
June 10, 2009 at 15:41:23

Hi there
In a nutshell, an IVA is an Individual Voluntary Arrangement. Its a legal way to dispose of your debts over 5 years ( or can be 6 if you have a property that you wish to preserve) The basics are that you pay back your creditors a percentage of what you owe over 5 years (or 6) and at the end you are debt free. The arrangement is set up by an insolvency practitioner and the creditors agree to the proposals at the begining of the arrangement. An IVA is expensive to set up as there are fees involved - I would always refer to Payplan because they dont charge any upfront fees. http://www.payplan.com/individual-v...

I currently work in a deprived area and my clients have low level debts of typically less than £10,000, mostly my clients have no money to offer their creditors. In that case a £1 minimum payment keeps the non-priority creditors at bay and enables the client to pay their rent.

The spreadsheet will form part of a financial statement for those wishing to help themselves with their debt problems.

You have helped with my excel spreadsheet, put in a great deal of thought thank you.
--------------------------------------
Mike,
i'll have a look at your follow up message tomorrow when i'm not too tired to think straight. Thank you for your suggestions and assistance.
Jane


Report •

#21
June 11, 2009 at 23:37:59

Hi Mike,
I've done as you have suggested and it works! I need to have a look at it when i get back from work in order to try and understand it. Thank you. Can I alter anything to increase anything less than £5.00 to £5.00? (I know with this data it might mean all payments and cant be done, but in theory could your calculations do it?)

Jane


Report •

#22
June 12, 2009 at 06:25:21

re: In that case a £1 minimum payment keeps the non-priority creditors at bay and enables the client to pay their rent.

That leads me to my next question:

Let's say we use Mike's method to round everything to 1.00, and that it can be adapted to work for 5.00.

Would that automatic calculation result in just the "non-priority creditors" getting the 1.00 or is it possible that a priority creditor will be upset that they only got a 1.00?

I only ask in case you roll this out to your clients and they are assuming that the distribution set up by the spreadsheet is acceptable to all creditors. Could you be "liable" for making payment recomendations that get the client in trouble?


Report •

#23
June 12, 2009 at 08:36:03

Can I alter anything to increase anything less than £5.00 to £5.00? (I know with this data it might mean all payments and can’t be done, but in theory could your calculations do it?)

With a wee bit of modification:

Try this:

Column M cell 15 add the formula:

=IF(M14=1,0%,IF(M14=2,2%,IF(M14=3,5%)))

(Were going to use your Rounding Options for the moment. More on this below.)

Now modify the formula in the F column to:

=IF(E7<$M$15,$M$15-E7,0)

Now when you use your Rounding Options it will be reflected in the formulas.

With this in mind, you can now modify your Round Options to round up from Zero through 5 simply by adding three more options:

No Rounding = 0 in cell M14
1 = 1 in cell M14
2 = 2 in cell M14
3 = 3 in cell M14
4 = 4 in cell M14
5 = 5 in cell M14

Your new formula in cell M15 would be:

=IF(M14=0,0%,IF(M14=1,1%,IF(M14=2,2%,IF(M14=3,3%,IF(M14=4,4%,IF(M14=5,5%))))))

If you decide to go this route, then you can do away with the M15 formula altogether and simply have the Column F formula reference M14.
Just be sure to format the cell M14 as percent.


The big draw back in the formulas is in the Column G formula.
Using the =MAX function we find the single largest payment percentage (who we are going to pay the most, Creditor #3 ) and decrease the payment by the total of the increase to those cells that we have manually increased.
With the current data, if you increase the manual payments to 5 then the largest creditor, #3, goes into the negative. Not good.

The modification to your Round Options should help in this area, as now you will be able to utilize 6 payment options instead of the three you currently have.

MIKE

http://www.skeptic.com/


Report •

#24
June 12, 2009 at 14:21:12

To go along with my suggestions from Response # 18 & # 23
This should help distribute the monies a bit more evenly:

In column C cell 7 add the formula:

=RANK(D7,$D$7:$D$24,1)

Copy down to cell C24

This will Rank the Creditors from “Owed the Most” to “Owed the Least”.

Now to utilize the ranking we just created,

In Column F cell 26 enter the formula:

=COUNTIF(F7:F24,"=0")

With NO manual payments, this figure should be 18

In Column F cell 27 enter the formula:

=COUNTIF(F7:F24,">0")

With No manual payments, this figure should be 0

In Column F cell 28 enter the formula:

=F25/F26

With No manual payments, this figure should be 0

Now if you make a manual payment of 1 the following should occur:

Since there are 4 amounts that need to be upgraded to 1
Cell F26 changes from 18 to 14
And
Cell F27 changes from 0 to 4

The total of the manual payments from cell F25 is then divided by the 14 creditors that did not have their amounts manually increased and
Cell F28 changes from 0 to 0.1149% the average that needs to be spread between them.

Once again all the action will occur in Column G.

In Column G cell 7 enter the formula:

=IF(C7>$F$27,E7-$F$28,E7+F7)

Copy down to cell G24

As long as the rank of the current cell is greater than the bottom creditors ranking we subtract the average from Cell F28, otherwise we upgrade them to 1 by adding what they “would have” paid with the amount needed to reach 1.
It’s all a bit convoluted but I think you can figure out what’s going on.

MIKE

http://www.skeptic.com/


Report •


Ask Question