Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 24

Thread: Is it arrays? pivot? both? Arrgh!

  1. #1
    Seeker Pookah's Avatar
    Join Date
    Jul 2016
    Posts
    12
    Articles
    0
    Excel Version
    2016

    Lightbulb Is it arrays? pivot? both? Arrgh!



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

    I know there's a simple solution to this, but my mind is blocked. I have a spreadsheet with 5 cells of data. Based on these 5 cells, I generate 4 calculated values.
    I have another spreadsheet with a list of 50 situations. Each situation has cells that correspond to the 5 cells of data (above). I would like to apply each of the 5 sets of data to the 1st spreadsheet and populate the remainder of the rows with the 4 associated calculated values.

    It's not a normal lookup function as the 1st spreadsheet changes dynamically with the data.

    A better analogy would be if I had 50 loans with different maturities, interest rates, initial amounts. I have a sheet that calculates an amortization table for the loan. I then would like to update my sheet of 50 loans with, say the principal balance, principal paid and interest paid in 2015.

    I've tried a number of approaches and know it must be elegantly simple, but for the life of me I don't know what it is.

    Thank you,

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,673
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Hi

    if you know there is a simple solution, then you know the solution, no?

    Anyway please post a sample sheet ( no pics pse) showing your data and an example of result. Thanks
    Thank you Ken for this secure forum.

  3. #3
    Seeker Pookah's Avatar
    Join Date
    Jul 2016
    Posts
    12
    Articles
    0
    Excel Version
    2016
    If I knew what it was, I wouldn't ask. I merely guess that I'm missing a relatively simple technique.

    I'm new to this site, please let me know how to upload a file to this thread.

  4. #4
    Seeker Pookah's Avatar
    Join Date
    Jul 2016
    Posts
    12
    Articles
    0
    Excel Version
    2016

    Post Here is the attachment

    Attached is the example. Also, please look at cells H8 and H9. I added year values because these functions were not working. The interesting thing is the Function Arguments dialog box show the correct result, but the cell does not. Also one is #VALUE and the other is #N/A, but the syntax is the same. Same result if I make both cells identical.
    Attached Files Attached Files

  5. #5
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013
    If your problem is in cells H8 and H9 then you notice that there ARRAY formulas (or CSE formulas).
    MATCH function returns the number of row. Format cells as 'General'
    Respective formulas you end up with Ctrl+Shift+Enter (not just enter)
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  6. #6
    Seeker Pookah's Avatar
    Join Date
    Jul 2016
    Posts
    12
    Articles
    0
    Excel Version
    2016
    Thank you that worked. Previously, I had applied CSE to the elements and not the whole expression. H8 and H9 were merely bonus questions, however, the initial question still apples.

  7. #7
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by Pookah View Post
    however, the initial question still apples.
    If I understood you well, look at the attached file.
    I've moved the formulas to the 'Loan' Sheet
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  8. #8
    Seeker Pookah's Avatar
    Join Date
    Jul 2016
    Posts
    12
    Articles
    0
    Excel Version
    2016
    Navic,

    You missed the point. What you did was fill in each row with the input data from the first row only.


    Row 3 should show the results for a $250,000 loan at 6% for10 years
    Row 4 should show the results for a $10,000 loan at 7% for3 years
    etc.


    See how I2 and I5 are the same? They should be different.

    The idea is that I want to apply the inputs sequentially to 'Amort, to generate the results on each line as opposed to setting up a separate 'Amort table for each loan.

  9. #9
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013

    Get data ffom multiple sheets

    Quote Originally Posted by Pookah View Post
    You missed the point. What you did was fill in each row with the input data from the first row only.
    I'm sorry, I can not read your thoughts. You should set the expected results.
    Here's a new example, you adapt to your needs.
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  10. #10
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013
    See attachment file, there is a new idea for a solution. There are one sheet 'Amort'.
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

Page 1 of 3 1 2 3 LastLast

Posting Permissions

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