Does anyone know how to write the same formula but with one small
change:

Here is the original formula:


=PRODUCT(1+OFFSET($G530,0,3*(1-COLUMNS($FT530:FT530)),1,3))+1

Instead of (1+h530)*(1+I530)*(1+J530)-1

I want: -(1-h530)*(1-i530)*(1-J530)+1

I tried the following but did not work

1-PRODUCT(1-OFFSET($G530,0,3*(1-COLUMNS($FT530:FT530)),1,3))

I don't see anything wrong. The data may not be what you expect.
I would highlight the cell that you are getting the wrong answer.

Then try on the Tools Menu
Formula Auditing - Evaluate formula

" wrote:

Does anyone know how to write the same formula but with one small
change:

Here is the original formula:


=PRODUCT(1+OFFSET($G530,0,3*(1-COLUMNS($FT530:FT530)),1,3))+1

Instead of (1+h530)*(1+I530)*(1+J530)-1

I want: -(1-h530)*(1-i530)*(1-J530)+1

I tried the following but did not work

1-PRODUCT(1-OFFSET($G530,0,3*(1-COLUMNS($FT530:FT530)),1,3))

How about,

=-PRODUCT(1-H530:J530)+1

Entered as an array formula, i.e. with key combo Ctrl+Shift+Enter

HTH
Kostis Vezerides

On Feb 27, 5:55 pm, wrote:
Does anyone know how to write the same formula but with one small
change:

Here is the original formula:

=PRODUCT(1+OFFSET($G530,0,3*(1-COLUMNS($FT530:FT530)),1,3))+1

Instead of (1+h530)*(1+I530)*(1+J530)-1

I want: -(1-h530)*(1-i530)*(1-J530)+1

I tried the following but did not work

1-PRODUCT(1-OFFSET($G530,0,3*(1-COLUMNS($FT530:FT530)),1,3))