Results 1 to 4 of 4

Thread: "Unnesting" a formula

  1. #1

    "Unnesting" a formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,


    I've just spent a lot of time creating a sheet with many levels of temporary results, these temporary results I've saved in cells. The calculation depends on different parameters that the user enters into other cells.


    For example


    A1 10 (entered by user)


    B1 =A1+5 (temporary result level 1)


    C1 =(B1 + 2)/2 (temporary result level 2)


    D1 =C1 ( (8,5) final result level 3)


    Of course my real sheet has closer to 20 levels and many more parameters otherwise I wouldn't have a problem which is this:


    I want to automatically go through a range of values for A1 and then put the result from D1 in another range of cells. My thought was to just write a VBA function, put this function in the formula field of a cell and choosing the input cells by clicking on them. When executing this function it should set the value of A1 which would make the sheet compute the value of D1. Then using autofill i could just copy this formula, this would take the range of input values automatically and produce a range of output values, also automatically.


    But after I've spent many hours creating the sheet I found out that you can't use a VBA function to change the value of other cells than the one it's called from.


    Does Excel have a feature that automatically "unnests" a formula? Ideally I would click on D1, click on "unnest" and Excel would produce "=( ((A1+5) + 2) / 2 ).


    Any other ideas?


    Really grateful for any help!


    BR / Öyvind

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    792
    Articles
    0
    Excel Version
    2010
    Im more than a little puzzled by this and I haven't come across anyone needing to expand formulae from dependent cells into their components in the primary cell. You can see this to an extent in the Evaluate Formula routine but my guess is that it wont help you.
    I would suggest working with the single "unnested" version instead would eliminate the problem and give the required return value? Can you provide more information on what your trying to acheive, and restate your D1 formula which (as given) is not valid.

  3. #3
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    742
    Articles
    0
    Excel Version
    Excel 2010 64bit

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    oyvindstrand ,

    Please read... http://www.excelguru.ca/content.php?184 to understand how to crosspost.


Posting Permissions

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