+ Reply to Thread
Results 1 to 12 of 12

Changing the value of a cell based on the value of another cell

  1. #1
    Registered User
    Join Date
    12-08-2006
    Posts
    17

    Changing the value of a cell based on the value of another cell

    Hi, I would like to seek your help in this problem,
    I have cell "U6" that caluclates a deficit or surplus based on a value entered on another cell "B6" now what i do is enter values manually in cell "B6" till te value in "U6" reached zero. but when dealing with a range of cell, its time consuming to enter values manually, so I though of using Porblem solver, it worked fine, but it works on one cell at a time, again it is time consuming.
    what i am looking for is a formula that changes values of cell "B6" untill "U6" is zero, then i can apply to the rest of cells (B6:B5000) ?

    Hope somebody can help me with that

    Thanks in advance
    Joenash

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Joenash
    Hi, I would like to seek your help in this problem,
    I have cell "U6" that caluclates a deficit or surplus based on a value entered on another cell "B6" now what i do is enter values manually in cell "B6" till te value in "U6" reached zero. but when dealing with a range of cell, its time consuming to enter values manually, so I though of using Porblem solver, it worked fine, but it works on one cell at a time, again it is time consuming.
    what i am looking for is a formula that changes values of cell "B6" untill "U6" is zero, then i can apply to the rest of cells (B6:B5000) ?

    Hope somebody can help me with that

    Thanks in advance
    Joenash
    Hi,

    is it possible to re-calculate back from the zero to deduce B?

    If not, by what increments shoule B rise, just the ol 'Guessnum' best guess?

    Cal a zero be guarentted?

    Any preferred starting value?

    Any negatives involved?

    ---

    also - do you have a sample couple of rows for testing?
    Last edited by Bryan Hessey; 12-08-2006 at 07:10 AM.
    Si fractum non sit, noli id reficere.

  3. #3
    Registered User
    Join Date
    12-08-2006
    Posts
    17
    Hello Brayan,

    The sheet calculates the share a person has to pay to recieve a certain lumpsum amount of money (Pension Plan)

    Lets say someone wants to recieve $ 100,000 at retirement, i start by entering a value in the cell "B6" lets say $50 based on which the sheet calcluates his accumulation and measure it againest the $100,000 benfit at target, in the cell U6 , it shows me negative amount meaning that i have to increase value in "B6" to reach the 100,000 or zero meaning that the $ 50 per month will satisfy the required target. it does not show postive signs, it either negative or zero.

    starting values are of a wide variety, depending on the age of the person, and the target required.

  4. #4
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    Hi,

    Ideally, it would help a lot if you could post a zipped copy of your worksheet

    Carim

  5. #5
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Joenash
    Hello Brayan,

    The sheet calculates the share a person has to pay to recieve a certain lumpsum amount of money (Pension Plan)

    Lets say someone wants to recieve $ 100,000 at retirement, i start by entering a value in the cell "B6" lets say $50 based on which the sheet calcluates his accumulation and measure it againest the $100,000 benfit at target, in the cell U6 , it shows me negative amount meaning that i have to increase value in "B6" to reach the 100,000 or zero meaning that the $ 50 per month will satisfy the required target. it does not show postive signs, it either negative or zero.

    starting values are of a wide variety, depending on the age of the person, and the target required.
    ok thanks -positive only.

  6. #6
    Registered User
    Join Date
    12-08-2006
    Posts
    17
    Hi Carim,

    I cannot attach the original file, because its owned by my company, alternatively I have made the attached file.

    Attached Files Attached Files

  7. #7
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    Hi,

    Attached is your worksheet ...
    Just input the total amount expected ... ( blue cell) to get he exact monthly amount ... ( red cell )

    HTH
    Carim
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    12-08-2006
    Posts
    17
    Thanks Carim,

    This really works very fine for this sheet, but what if the formula got more complicated, this solution might not then be applicable. what I am thinking of can it be done with "if function" for example comparing value in B6 to U6 if U6 is still less than zero, it would alter the value in B6 and so on ?

  9. #9
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    I am not sure to fully understand your new request ...

    Two comments :

    1. If "more complicated" means with more variables, my assumption would be it is still feasible ..

    2.Now, if I mean you would like to continue inputing your "forecasted" monthly amounts ...
    Two possibilities :
    a) have two lines per customer : first line with input, second line with formula
    b) only one line with input : Use Tools Goal Seek ...

    HTH
    Carim

  10. #10
    Registered User
    Join Date
    12-08-2006
    Posts
    17
    Exactly its 2-b , I started using Goal Seek,
    But now I am faced with a new problem
    Goal Seek is applied only to single cell, It can't be used over ranges.
    Now what I am looking for (And I would much appreciate if you could help me with) is a VBA code to make Goal seek work over ranges, for example

    Change cell U2 to value of zero by changing cell B2, when done, it would go to cell u3 and repeat the same, and so on.


  11. #11
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    Hi,

    With many variables and constraints, you are better off using Data Solver ...

    HTH
    Carim

  12. #12
    Registered User
    Join Date
    11-30-2006
    Location
    UK
    MS-Off Ver
    Microsoft Office XP
    Posts
    61

    Question Why interpolate when you can go straight to the answer?

    I'm struggling to understand how it could be better to make a series of guesses to approach an answer than it would be to go straight to it.

    On the information given Carim came up with an ideal formula. I worked it out before I looked at Carim's answer and came up with something very similar:

    A2=(J2+F2-H2-2*C2)/480
    This will give a circular reference but if you assume J2 to be ideal (=0) the equation now has a solution

    I added ROUNDUP so that you would be guaranteed a surplus and fixed the answer to 2 decimal places (constraint of currency)
    A2=ROUNDUP((F2-H2-2*C2)/480,2)

    Then I added a first payment column to account for the small surplus that can be left; this moved all other columns 1 space to the right.
    A2=B2-L2
    B2=ROUNDUP((G2-I2-(2*D2))/480,2)

    Solver will give solutions to 2 or more variables by changing other variables but you are only looking for a single answer here.

    I may be missing the mark or not be understanding the problem but haven't you already been handed the answer by Carim? Can you elaborate further on why this solution will not be suitable? What other varibles would you add?

    Finished sheet attached.

    Free.zip
    Last edited by Json; 12-11-2006 at 06:49 PM.

    Jason

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1