Results 1 to 10 of 10

Thread: Copy and paste formula diagonally

  1. #1

    Copy and paste formula diagonally



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

    Hi,


    I have been trying various things to be able to copy and paste a string of multiplications diagonally without much luck, actually any luck to be precise...anyhow, I have attached a spreadsheet in the hope for a solution.


    David
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,463
    Articles
    0
    Excel Version
    Excel 2016
    What exactly is the requirement? I see formulas in Row 18 and below... what is not correct about this?


  3. #3

    Copy and paste formula diagonally

    Quote Originally Posted by NBVC View Post
    What exactly is the requirement? I see formulas in Row 18 and below... what is not correct about this?
    The first and second set of data are engine deterioration factors and number of engines. The calculation goes like this: one year old engine multiplied by its deterioration factor gives the result for the calculation and so on for the consecutive years. The difficulty is that this calculation goes out to 50 years and when the new year starts, the formula for the corresponding cell has to move down and by one to the right (as it is meant to describe next year's engine deterioration, it is essentially a cumulative distribution function.) I have attached another spreadsheet that looks a lot more intuitive.

    Thanks very much for your help.
    Attached Files Attached Files

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

    In B4:

    =IFERROR(INDEX($B:B,3,COLUMNS($B:B)-ROWS($3:3)+1)*INDEX($B:B,2,ROWS($3:3)),"")

    copied across and down.


  5. #5
    What can I say? It is perfect. Thanks very much for your help.

    Cheers,

    David

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,463
    Articles
    0
    Excel Version
    Excel 2016
    Great

    Thanks for the feedback!


  7. #7
    No worries, you probably have got no idea how much you have helped, so yeah, thanks again.

  8. #8
    Quote Originally Posted by NBVC View Post
    Try this:

    In B4:

    =IFERROR(INDEX($B:B,3,COLUMNS($B:B)-ROWS($3:3)+1)*INDEX($B:B,2,ROWS($3:3)),"")

    copied across and down.

    I was wondering how the formula changed if it got multiple level variables i.e. for each year there were multiple deterioration factors for multiple engine ratings. I have tried modifying your formula but could not get it work and was wondering if you could help out with this one too, thanks. I have attached the spreadsheet again to show the model.
    Attached Files Attached Files

  9. #9
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,463
    Articles
    0
    Excel Version
    Excel 2016
    Try, in C19:

    =IFERROR(INDEX($C:C,MOD(ROW()-ROW($A$19),8)+11,COLUMNS($C:C)-MOD(INT((ROW()-ROW($A$19))/8),8))*INDEX($C:C,MOD(ROW()-ROW($A$19),8)+3,MOD(INT((ROW()-ROW($A$19))/8),8)+1),"")

    copied across and down.

    You can get rid of the 0's by formatting the output as Custom Number with Type: 0;-0;;


  10. #10
    WORKS!

    Many thanks.

    David

Posting Permissions

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