Inciteful
asked on
How to calculate product of a sequence?
Please see attached image in order understand this very simple question:
Column K contains the formula I'm trying to figure out. I'm having to manually input these formulas and I'm trying to figure out a quick way to do it.
K52 =PRODUCT(1-J52)
K53 =PRODUCT(1-J52,1-J53)
K54 =PRODUCT(1-J52,1-J53,1-J54
K55 should be = PRODUCT(1-J52,1-J53,1-J54,
...and so on...
Please let me know what would be a quick way to copy this formula down till row K127? Thanks.
Product-Sequence.JPG
Column K contains the formula I'm trying to figure out. I'm having to manually input these formulas and I'm trying to figure out a quick way to do it.
K52 =PRODUCT(1-J52)
K53 =PRODUCT(1-J52,1-J53)
K54 =PRODUCT(1-J52,1-J53,1-J54
K55 should be = PRODUCT(1-J52,1-J53,1-J54,
...and so on...
Please let me know what would be a quick way to copy this formula down till row K127? Thanks.
Product-Sequence.JPG
Try this version
=PRODUCT(1-J$52:J52)
confirmed with CTRL+SHIFT+ENTER and copied down
Barry
=PRODUCT(1-J$52:J52)
confirmed with CTRL+SHIFT+ENTER and copied down
Barry
While both sshah254 and barryhoudini formulas work, you may prefer to dispense with PRODUCT altogether and copy down the following formula in cell K53
=(1-J53)*K52
=(1-J53)*K52
ASKER
Barry - i like your formula best.
One question though...
If I want to copy this formula across COLUMNS on another worksheet ? How would I set up the formula so that the references continue to work?
(Say the sheet in the image is called "sheet1")
One question though...
If I want to copy this formula across COLUMNS on another worksheet ? How would I set up the formula so that the references continue to work?
(Say the sheet in the image is called "sheet1")
The $ fixes the row reference (makes it absolute) in the above so that when you copy down the J$52 remains J$52, if you want to copy across then it's the same principle but you use $ in front of the column letter so if you wanted to start with B4 and then add C4, D4 etc. you could use
=PRODUCT(1-$B4:B4)
then copy across
regards, barry
=PRODUCT(1-$B4:B4)
then copy across
regards, barry
Oh, I missed the sheet part, if that was on another sheet it would be like this
=PRODUCT(1-sheet1!$B4:B4)
still confirmed with CTRL+SHIFT+ENTER
....although if you want to avoid "array entering" you could add an INDEX function like this
=PRODUCT(INDEX(1-sheet1!$B
regards, barry
=PRODUCT(1-sheet1!$B4:B4)
still confirmed with CTRL+SHIFT+ENTER
....although if you want to avoid "array entering" you could add an INDEX function like this
=PRODUCT(INDEX(1-sheet1!$B
regards, barry
ASKER
Barry - when i copy that formula across columns A to Z the references dont change appropriately...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks!
K54 = PRODUCT(K53, 1-J54)
and so on.
Ss