Excel Functions- Product Array

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rki1966@gmail.com - 27 Feb 2007 14:55 GMT

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

Reply to this Message

Joel - 27 Feb 2007 15:20 GMT

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

> Does anyone know how to write the same formula but with one small
> change:
[quoted text clipped - 10 lines]
>
> 1-PRODUCT(1-OFFSET($G530,0,3*(1-COLUMNS($FT530:FT530)),1,3))

Reply to this Message

vezerid - 27 Feb 2007 15:22 GMT

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, rki1...@gmail.com wrote:

> Does anyone know how to write the same formula but with one small
> change:
[quoted text clipped - 10 lines]
>
> 1-PRODUCT(1-OFFSET($G530,0,3*(1-COLUMNS($FT530:FT530)),1,3))

Reply to this Message