Copy and paste formula diagonally

dhazi

New member
Joined
Mar 20, 2013
Messages
10
Reaction score
0
Points
0
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
 

Attachments

  • Copy and paste formula diagonally.xlsx
    17 KB · Views: 897
What exactly is the requirement? I see formulas in Row 18 and below... what is not correct about this?
 
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.
 

Attachments

  • Copy and paste formula diagonally.xlsx
    18.3 KB · Views: 1,113
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.
 
What can I say? It is perfect. Thanks very much for your help.

Cheers,

David
 
No worries, you probably have got no idea how much you have helped, so yeah, thanks again.
 
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.
 

Attachments

  • Copy and paste formula diagonally.xlsx
    23.6 KB · Views: 308
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;;
 
Back
Top