Copy and paste formula diagonally

**dhazi** · 2013-03-20, 04:34 PM

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

**NBVC** · 2013-03-20, 05:04 PM

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

**dhazi** · 2013-03-21, 12:35 AM

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.

**NBVC** · 2013-03-21, 02:50 PM

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.

**dhazi** · 2013-03-21, 03:12 PM

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

Cheers,

David

**NBVC** · 2013-03-21, 03:18 PM

Great

Thanks for the feedback!

**dhazi** · 2013-03-21, 03:26 PM

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

**dhazi** · 2013-03-22, 10:55 AM

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.

**NBVC** · 2013-03-22, 02:08 PM

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;;