Excel Functions- Product Array

R

rki1966

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))
 
J

Joel

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
 
V

vezerid

How about,

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

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

HTH
Kostis Vezerides
 
Top