Topic: Circular references

[Clark Kent]

perhaps one of you fine mathematical and scientific gents can lend a hand:

I am being asked to determine a figure.  Imagine three colums on a table.  The first column is a subtotal.  The next column is an adjustment to the subtotal, which is a sum of a bunch of different adjustment amounts.  Finally is the total column.

Now, my problem, right now, I use the subtotal amount to determine an amount for the adjustment column, which then feeds into the total column.  What I am being required now to do (because my boss feels it is more accurate) is to use the total column to determine the adjustment amount, which in turn feeds into the total column.  I need the new amount in the adjustments column to be such that it comes to the right amount in the totals column.  This creates a circular reference.  I'm sure that I've seen a way to get around this little mathematical problem, but my mind is blanking on this issue.  Any ideas?

[marstone]

sounds like a stupid way of doing it.  you take the total amount subtract the subtotal amount will give you the adjustment to subtotal.  But if the adjustment of subtotal is supose to be a collection of other adjustments, you would be nulling what they add up to, just to make the numbers work (or pad larger if you adjust your total higher).

how does the adjustment to the subtotal amount get figured out the old way?

[Czar Mohab]

Think I need more input, but sounds like your boss is trying to put the cart before the horse in this. Like he wants you to know the total before you find the adjustments, meaning your equation goes from SUB + ADJ = TOTAL to ADJ = TOTAL - SUB. But then I feel that there is more to this than just that, perhaps. Like your boss and you only know the SUB and want both the ADJ and TOTAL from that one number.

Since ADJ is a variable independant of SUB, I can see where the true issue is; being that ADJ will almost always be something different for any given SUB (and TOTAL).

You can try adding a "PREADJUSTMENT" column and "GRAND TOTAL" column that can be fed information to make it more linear:

SUB = Only given #
PADJ = Whatever equation you use to get the current Adjustment, output varies
TOT = SUB + PADJ
ADJ = Whatever tweak equation you need to refine the number, say add 2% or something, output varies
GTOT = ADJ + TOT

Or, Expanded:

(SUB+PADJ)+ADJ=GTOT =>
(SUB+(EQN))+(Another EQN)=GTOT

But I still think I need a bit more info. I understand that company confidentiality exists and I'm not asking you to break that in any way! Just need more input.

Czar "Johnny 5, ALIVE!" Mohab

[Clark Kent]

It is completely putting the cart before the horse.  What it boils down to is I'm trying to remove costs related to a tenant at one of our buildings, so the subtotal column is before I make that adjustment (and several others). The total column gives me the "true" cost of the cost center (after things like this cost center are removed.  This is done via proprietary software, so I can't fiddle with the calculation order.  The software will always calculate sub-adj=total. 

The reason she wants the total column used instead of the sub is because I'm trying to allocate a percent of costs for the cost center compared to the entire hospital.  If I use sub, then I'm allocating more than I would if I'm using the total for this adjustment. 

Net result: find total, so I can determine the adj column.  Once I have that figured out, I put it into the software, which then recalculates the total for me.

[Bonk]

Sounds like a job for Excel's "solver" add-in. Or a macro with an algorithm of your own design. The conditional sum wizard might also be useful.

[marstone]

to me it seems you will have to set up a second spreadsheet, that will figure total-sub=adjustment, so you can plug in the right adjustment into the program you are using to get the right answer.

If I understand correctly she wants the total to be a proper percent in the whole of things, so to make sure it fits you have to fudge the adjustment column to make it fit right.

But since you can't change how the software figures out the answer (always sub-adj=total), I think you will need another spreadsheet to work out the number to readjust the main program data.

since you know the cost involved with the cost center entailing the client, could you not put in adjustments for that to remove them?  That would skip the whole circular problem.  But adds more information to the system that might need to be noted.

[toasty0]

perhaps one of you fine mathematical and scientific gents can lend a hand:

I am being asked to determine a figure.  Imagine three colums on a table.  The first column is a subtotal.  The next column is an adjustment to the subtotal,

How is this adjustment being determined?

which is a sum of a bunch of different adjustment amounts.  Finally is the total column.

Sum of what? The subtotal column or the adjustment column, or some other rows and columns?

Now, my problem, right now, I use the subtotal amount to determine an amount for the adjustment column,

Assuming you're using a spreadsheet(Excel?), can you share your formula with us?

which then feeds into the total column.  What I am being required now to do (because my boss feels it is more accurate) is to use the total column to determine the adjustment amount, which in turn feeds into the total column. 

It shouldn't. Your adjustment is a function of something else acting upon your subtotals. In other words, you have a subtotal that is being adjusted by some other values/functions/formula (not the values in the total column) which in turn is summarized to give you your totals.

I need the new amount in the adjustments column to be such that it comes to the right amount in the totals column.  This creates a circular reference.  I'm sure that I've seen a way to get around this little mathematical problem, but my mind is blanking on this issue.  Any ideas?

a=a+n where n is the sum of some other iterative function. But that does not apply here. What you have here is either bad accounting/bookkeeping practices...

Wish you well and good luck.

[Clark Kent]

Technically, it's not bookkeeping, it's "cost allocation."  I don't really like it, but then, since when does my opinion matter?

OK, I'll try and explain again: I need an adjustment for the adjustment column.  Right now, I take the amount in the sub column and multiply it by a percent to total (square feet for this department to the total square feet for the building).  What I am told now is that I need to use the total column instead of the subtotal column to get my adjustment amount- I need to take my percent to total and multiply it by the total column. 

The problem is that I am trying to figure out the adjustment column, which then automatically is used to calculate the total column.  To figure out the adjustment column, I am now supposed to use the total column, which is then used to figure out the total column.

I can use excel to figure it out, but I still have to enter it into the software I'm using which calculates the total column itself.  I enter in the adjustments myself.