Calculating performance in Excel using an array?


Posted by Chris on October 01, 2001 8:47 AM

I seemed to have lost my memory regarding how to input a formula to calculate performance in Excel easily using an array or the PRODUCT function. If you have four cells of performance data A1 contains "10", A2 contains "10" and A3 contains "5", a method that works is
=(PRODUCT(1+A1/100,1+A2/100,1+A3/100)-1)*100
to get the right answer of 27.05
This process gets very laborious if you have many cells of performance data (i.e. daily data) to enter. I vaguely recall someone showing me a way to enter this formula much easier using the {} characters and having to press CTRL-ENTER keys to get the formula to work. Maybe like =((PRODUCT{1+A1:A3/100})-1)*100. This method (!) would work much better for a larger range of cells. Does this ring any bells for anyone out there?

Posted by Juan Pablo on October 01, 2001 8:52 AM

To enter that array formula press Control Shift Enter at the same time.

Juan Pablo



Posted by Juan Pablo on October 01, 2001 8:57 AM

Oops, again...

The formula should be

=((PRODUCT(1+A1:A3/100))-1)*100 and press Control Shift Enter at the same Time.

Excel puts the { } automatically after you press the three keys.

Juan Pablo