Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Boston suburb (Acton), Massachusetts, USA
    Posts
    109
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Use 1 set of formulae for 5 cells? REVISED

    <P ID="edit" class=small>Edited by hobkirk on 23-Feb-02 14:11.</P>Revised "Explanation" of problem

    i am looking for a formula for a cell like:
    CustCopy c1 = LeaseRate b2 >>>if (LeaseRate a1 = CustCopy b1)<<<

    Worksheet LeaseRate calculates numerous lease payments for the "principal" in cell a1 -
    a1 = input principal value
    b2 = lease payment for a1 for a 60 month lease
    b3 = lease payment for a1 for a 48 month lease
    Note: the calculation varies according to points added, size of lease, and several other factors, so it really isn't practical to reproduce it multiple times (unless I must)

    Worksheet CustCopy has 3 cells with 3 different "principals".
    b1 = option 1 principal (e.g., $5,000)
    b2 = option 2 principal (e.g., $8,000)
    b3 = option 3 principal (e.g., $11,000)

    GOAL: I want the 60 mo. lease payments for the 3 amounts in column "b" in column "c"...
    c1 = value b2 in LeaseRate if b1 in CustCopy (e.g., $5,000) were plugged into a1 in LeaseRate
    c2 = value b2 in LeaseRate if b2 in CustCopy (e.g., $8,000) were plugged into a1 in LeaseRate
    c1 = value b2 in LeaseRate if b3 in CustCopy (e.g., $11K) were plugged into a1 in LeaseRate

    I also want the 48 mo. lease payments for the 3 amounts in column "b" in column "d"...
    d1 = value b3 in LeaseRate if b1 in CustCopy (e.g., $5,000) were plugged into a1 in LeaseRate
    d2 = value b3 in LeaseRate if b2 in CustCopy (e.g., $8,000) were plugged into a1 in LeaseRate
    d1 = value b3 in LeaseRate if b3 in CustCopy (e.g., $11K) were plugged into a1 in LeaseRate

    I hope this is expressed more coherently... so coherently that someone can understand me!

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Use 1 set of formulae for 5 cells?(Excel 2000)

    How about a Data table Sam?

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Use 1 set of formulae for 5 cells?(Excel 2000)

    Note -- this reply is not really applicable now that the above post has been edited

    This won't be an exact answer to your question, because I had trouble following your layout, but hopefully this will give you something to get started.

    I think that you want to use the Data | Validation to select the principle from the list; however, for data validation the list must be on the same sheet as the cell that uses it. See attached worksheet for an example. If absolutely necessary, you can get around this by making a copy of your list and hiding those cells. In the attachment, I also created a dynamic length list: notice that you can add more principles, terms, and rates. I did this with dynamic named ranges. See Insert | Name | Define to see the definitions. I also protected the worksheet so that the user can only change the parameters. HTH --Sam
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Use 1 set of formulae for 5 cells? REVISED

    Do you mean something like the attached? (uses the PMT function assuming 8% interest on the various principals over the two terms).
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Use 1 set of formulae for 5 cells? REVISED

    I hate to disagree Sam (it's against my principles), but principal was correct as meaning an original amount!
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Use 1 set of formulae for 5 cells? REVISED

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>Edited by Sam to change principle to principal

    As Catharine says, the best way would be to create a two variable (principal & term) data table, but that would probably require a major design change. To investigate this, look at help for Data Table, Ways to forcast values....

    For less changes, I have attached a workbook the just uses linked cells and absolute references to do the sort of calculations that you want to do. Obviously I am not in the leasing business, but hopefully this example will help you with your calculations. Notice that I placed all of the information that does not depend on the principal at the bottom and used absolute references (the dollar-signs in front of the row & column e.g. $B$8) to refer to this data. On the other hand, I placed all of the calculations that used the principal in the same row as the principal. Now if you select C1:F1, you can copy & paste these formulas into the other two rows without any changes.

    You should be able to rearrange you sheet fairly quickly using drag-and-drop: after you select a cell or range, you can "grab" its border and move it anywhere else. You also may want to use the Tools | Auditing | Show Auditing toolbar, because it has a tool to draw arrows to dependent cells. Finally, you can quickly change a relative reference in a formula to absolute by clicking in the middle of the reference and pressing <F4>. HTH --Sam
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Boston suburb (Acton), Massachusetts, USA
    Posts
    109
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Use 1 set of formulae for 5 cells? REVISED

    Hi, everyone. It's frustrating to find I still have not expressed myself cogently. But I really appreciate your efforts, and I will take another shot at clarification.

    I simplified my original post to try and make it easier. Maybe more detail would help.
    Attached Files Attached Files

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Use 1 set of formulae for 5 cells? REVISED

    As complicated as your spreadsheet is, I would just record a macro that copies each of the principals (as Rory observed Harvard spelling is better than Wittenberg), one-by-one, to D11 and then copy just the values back into the proper leasing columns. Is this an acceptable solution? --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Boston suburb (Acton), Massachusetts, USA
    Posts
    109
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Use 1 set of formulae for 5 cells? REVISED

    Yes, I suspect a macro would work. Great idea. I try to avoid macros in Office files (primarily so email recipients I share with won't get "nervous") and I don't have much experience with them. But I think I just start the "record macro", do all my moving, then "end the macro," and save it to the spreadsheet [adding a cool button, of course).

    I really thought there would be some sort of command like:

    = CELL-RESULT if CELL-LEASE-CALC-INPUT were made to equal CELL-RAW-INPUT

    (this would obviously make it a breeze - and it seems like this would be useful to you "industrial Excel users")

    Thanks again for your efforts. I really appreciate it.

  10. #10
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Boston suburb (Acton), Massachusetts, USA
    Posts
    109
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Use 1 set of formulae for 5 cells? IT WORKED

    The macro works great. Took about 5 minutes!
    I added some BIG arrows outside the print area so I don't forget to "click the macro button" before I print. Problem solved.
    Of course I still think MS should include something that works like the "formula" I wanted (hubris - bg).

    Thanks again.

  11. #11
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Use 1 set of formulae for 5 cells? REVISED

    Check out the attached. The columns I added to the right could be hidden or on another sheet if you like. It may not be exactly what you're looking for, but should point you in the right direction.
    Attached Files Attached Files

  12. #12
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Boston suburb (Acton), Massachusetts, USA
    Posts
    109
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Use 1 set of formulae for 5 cells? REVISED

    A radically different answer than I was "looking for" but brilliant. Even if I had looked up "VLOOKUP" I would not have realized it did what you just illustrated. And your formulae certainly are clean.

    Thanks. Have a beer (or Diet Coke) on me [but for pragmatic reasons get the money out of your wallet].
    .
    .
    .
    .....doesn't this guy understand the artistic beauty of using 27 convoluted steps to achieve a solution instead of just getting it done in one step......this is a really hard problem.......why doesn't he realize that....he probably does rubik cubes with his eyes closed......this wopr lounge is a real trip.....i hope he realizes that i really am impressed and appreciative....but of course he does, he's pretty sharp....

Posting Permissions

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