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?