Variables needed to calculate one another

JavaScript
1

Hi,

I’ve got a calculation:

estimateCost = 5,000,000
tenantingCost = 1,250,000

capitalCost = financeRate + estimateCost;
financeRate = capitalCost + tenantingCost;

as you can see I need the one to calculate the other one,
is this possible to do in JavaScript, because in .Net you have to calculate the one before the other?

2

Apparently, there is a function in excel called circular reference which enables this.

http://www.bettersolutions.com/excel/EUZ139/VC827516332.htm

3

Assume a financeRate of 1,000,000
The capitalCost = 1,000,000 + 5,000,000 which = 6,000,000
The financeRate then = 6,000,000 + 1,250,000 = circular.

Let’s rearrange the formulas.
capitalCost = financeRate + estimateCost;
financeRate = capitalCost + tenantingCost;

Let’s replace the financeRate in the first line with the definition of it from the second.
capitalCost = capitalCost + tenantingCost + estimateCost;

Subtract capitalCost from both sides
capitalCost - capitalCost = tenantingCost + estimateCost;

and we end up with this:
tenantingCost + estimateCost = 0.

So the original formulas are only valid when the tenantingCost and the estimateCost precisely cancel each other out - which has me too thinking that the formulas are wrong.

Edit:

I see I repeated the same conclusion here as Jake. That’ll learn me to keep a thread open for an hour or so before replying to it.

4

Ok, I changed both inputs to numbers and still I get a total, without errors.

I did a test:
K44 = 150
K45 = 5000
K46 = 260
K47 = 2255

J47 =SUM(K45:K47,J48) = 759,015
J48 =SUM(J47,K47,K46,K45) = 766,530

but J47 gives me an error:
The formula in this cell refers to a range that has additional numbers adjacent to it.

Is that what should happen?

5

Alright, let’s put it this way, I pose this math problem to you:
X + Y = 42.
Solve for X and Y.

If you assume the value of one, the value of the other becomes immediately apparent, but without more information all we can do is plot a linear graph.

6

Try changing both the inputs to numbers. I think your excel file is taking data from another source to fill in the gaps.

7

Alright, let me walk you through what you seem to be explaining. I just tried this in excel:
A2=5000000
B2=1250000
C2=D2+A2
C2=C2+B2
In excel, this results in an error. If you try to resolve this on paper, you will see it is unresolvable to a single answer without defining at least one variable between capitalCost and financeRate. Variables cannot depend on each other in such a fashion.

8

My calculation comes from an excel spreadsheet, do you have an idea how it works there?

9

Mathematically, your only option is plotting a graph. You have to fill in one of the blank variables to give your other one any meaning.
This isn’t a programming issue, it’s a mathematics problem.

10

If they have found a way to make this work the way it seems, send them to your nearest computing research center so they can take a look at the paradox-free undefined circular variable mathematics.

… oh, then I’ll finally be able to pull myself up by my bootstraps!

11

capitalCost = financeRate + estimateCost;
financeRate = capitalCost + tenantingCost;
Something has gone wrong in the maths here… big time.

Typically financeRate, estimateCost, capitalCost and tenantingCost are all positive. In the above situation, that is impossible (how could one get the other and add to it, whilst the other does the same!?)

If financeRate = capitalCost + tenantingCost, and capitalCost = financeRate + estimateCost, then financeRate = financeRate + estimateCost + tenantingCost, therefore estimateCost + tenantingCost = 0, therefore estimateCost and tenantingCost are equal but opposite. Obviously this cannot be the case.

12

That would be wise. If they have found a way to make this work the way it seems, send them to your nearest computing research center so they can take a look at the paradox-free undefined circular variable mathematics.
Good luck!

13

This is the exact formulas and calculations from excel:

H42 =+(H33+H35+H36+H37+H38+H39+H40+H41+H44)Input!G101Input!G103
H44 =+(H43+H42+H41+H40+H39+H38+H37+H36+H35+H33)*0.004

H42 gives an answer of 616,356
H44 gives an answer of 251,991

if I change something in H42 e.g(Input!G103 to 0.25)
H42 gives an answer of 10,280,086
H44 gives an answer of 294,758

14

Thanks for your help, I’m going to contact the owner of the spreadsheet.

15

Again, for me this result is telling me exactly what I’d expect. I get what’s called a Circular Reference warning. I’ve included a screenshot to show you what the result is. If you’re not getting a warning like this, your workbook must have a special case for this scenario, because in math you cannot have variables that are determined by the answer they are meant to find. Input begets output, output does not alter input.