SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Member
    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?

  2. #2
    SitePoint Addict Zarin Denatrose's Avatar
    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.

  3. #3
    SitePoint Member
    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?

  4. #4
    SitePoint Addict Zarin Denatrose's Avatar
    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.

  5. #5
    SitePoint Member
    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

  6. #6
    SitePoint Addict Zarin Denatrose's Avatar
    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.

  7. #7
    SitePoint Addict Zarin Denatrose's Avatar
    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.

  8. #8
    SitePoint Member
    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?

  9. #9
    SitePoint Addict Zarin Denatrose's Avatar
    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.
    Attached Images Attached Images

  10. #10
    SitePoint Member
    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.

  11. #11
    SitePoint Addict Zarin Denatrose's Avatar
    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 paradox-free undefined circular variable mathematics.
    Good luck!

  12. #12
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    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;
    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.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  13. #13
    om nom nom nom Stomme poes's Avatar
    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 paradox-free undefined circular variable mathematics.
    ... oh, then I'll finally be able to pull myself up by my bootstraps!

  14. #14
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,527
    Mentioned
    84 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by jellybeannn View Post
    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?
    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

  15. #15
    SitePoint Member
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •