# Thread: Copy and paste formula diagonally

1. ## Copy and paste formula diagonally

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  Reply With Quote

2. What exactly is the requirement? I see formulas in Row 18 and below... what is not correct about this?  Reply With Quote

3. ## Copy and paste formula diagonally Originally Posted by NBVC 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.  Reply With Quote

4. 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.  Reply With Quote

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

Cheers,

David  Reply With Quote

6. Great Thanks for the feedback!  Reply With Quote

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

8. Originally Posted by NBVC 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.  Reply With Quote

9. 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;;  Reply With Quote

10. WORKS!

Many thanks.

David  Reply With Quote

#### Posting Permissions

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