SitePoint Sponsor 

User Tag List
Results 1 to 15 of 15

Jun 8, 2010, 02:30 #1
 Join Date
 Jun 2010
 Posts
 9
 Mentioned
 0 Post(s)
 Tagged
 0 Thread(s)
variables needed to calculate one another
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?

Jun 8, 2010, 03:16 #2
 Join Date
 Jan 2009
 Location
 Surrey BC, Canada
 Posts
 309
 Mentioned
 0 Post(s)
 Tagged
 0 Thread(s)
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.

Jun 8, 2010, 03:22 #3
 Join Date
 Jun 2010
 Posts
 9
 Mentioned
 0 Post(s)
 Tagged
 0 Thread(s)
My calculation comes from an excel spreadsheet, do you have an idea how it works there?

Jun 8, 2010, 03:31 #4
 Join Date
 Jan 2009
 Location
 Surrey BC, Canada
 Posts
 309
 Mentioned
 0 Post(s)
 Tagged
 0 Thread(s)
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.

Jun 8, 2010, 03:41 #5
 Join Date
 Jun 2010
 Posts
 9
 Mentioned
 0 Post(s)
 Tagged
 0 Thread(s)
This is the exact formulas and calculations from excel:
H42 =+(H33+H35+H36+H37+H38+H39+H40+H41+H44)*Input!G101*Input!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

Jun 8, 2010, 03:48 #6
 Join Date
 Jan 2009
 Location
 Surrey BC, Canada
 Posts
 309
 Mentioned
 0 Post(s)
 Tagged
 0 Thread(s)
Try changing both the inputs to numbers. I think your excel file is taking data from another source to fill in the gaps.

Jun 8, 2010, 03:56 #7
 Join Date
 Jan 2009
 Location
 Surrey BC, Canada
 Posts
 309
 Mentioned
 0 Post(s)
 Tagged
 0 Thread(s)
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.

Jun 8, 2010, 04:01 #8
 Join Date
 Jun 2010
 Posts
 9
 Mentioned
 0 Post(s)
 Tagged
 0 Thread(s)
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?

Jun 8, 2010, 04:13 #9
 Join Date
 Jan 2009
 Location
 Surrey BC, Canada
 Posts
 309
 Mentioned
 0 Post(s)
 Tagged
 0 Thread(s)
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.

Jun 8, 2010, 04:16 #10
 Join Date
 Jun 2010
 Posts
 9
 Mentioned
 0 Post(s)
 Tagged
 0 Thread(s)
Thanks for your help, I'm going to contact the owner of the spreadsheet.

Jun 8, 2010, 04:19 #11
 Join Date
 Jan 2009
 Location
 Surrey BC, Canada
 Posts
 309
 Mentioned
 0 Post(s)
 Tagged
 0 Thread(s)
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 paradoxfree undefined circular variable mathematics.
Good luck!

Jun 8, 2010, 04:20 #12
 Join Date
 May 2006
 Location
 Lancaster University, UK
 Posts
 7,062
 Mentioned
 2 Post(s)
 Tagged
 0 Thread(s)
capitalCost = financeRate + estimateCost;
financeRate = capitalCost + tenantingCost;
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.Jake Arkinstall
"Sometimes you don't need to reinvent the wheel;
Sometimes its enough to make that wheel more rounded"Molona

Jun 8, 2010, 04:21 #13
 Join Date
 Aug 2007
 Location
 Netherlands
 Posts
 10,233
 Mentioned
 47 Post(s)
 Tagged
 1 Thread(s)
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 paradoxfree undefined circular variable mathematics.

Jun 8, 2010, 06:06 #14
 Join Date
 Jan 2007
 Location
 Christchurch, New Zealand
 Posts
 14,527
 Mentioned
 84 Post(s)
 Tagged
 4 Thread(s)
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.Programming Group Advisor
Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
Car is to Carpet as Java is to JavaScript

Jun 8, 2010, 06:13 #15
 Join Date
 Jun 2010
 Posts
 9
 Mentioned
 0 Post(s)
 Tagged
 0 Thread(s)
Apparently, there is a function in excel called circular reference which enables this.
http://www.bettersolutions.com/excel...C827516332.htm
Bookmarks