Product Formula Question

**KeithHenn** · 09-07-2011, 03:27 PM

hello,

Can someone explain the following formula to me?

{=PRODUCT(B170:B176+1)-1}

I need to re-write this formula in Java and I'm not quite sure what is being done.

I've added some dummy records to a table all with 1.0% as the value for B170 - B176 and the amount that I get as a result is 7.12%

It would be a great help if someone could explain how the 7.12% is being calculated. thank you very much in advance.

Regards,
Keith

**shg** · 09-07-2011, 03:32 PM

=(1+B170)*(1+B171)*...*(1+B176) - 1

Like compound interest.

**KeithHenn** · 09-07-2011, 08:02 PM

Originally Posted by shg

=(1+B170)*(1+B171)*...*(1+B176) - 1

Like compound interest.

thank you for getting back to me.

In my dummy example I entered 1% in 7 columns and the result was 7.12%

(1+.01)*(1+.01)*(1+.01).....(1+.01) - 1 = 0.072135352 *100 = 7.21%

Can you help me understand the difference?

Thank you.

**shg** · 09-07-2011, 08:17 PM

      --a-- -------------b-------------
  1      1%                            
  2      1%                            
  3      1%                            
  4      1%                            
  5      1%                            
  6      1%                            
  7      1%                            
  8   7.21% a8: {=product(1+a1:a7) - 1}

If you have a counterexample, post it, after making sure that the values in each cell are indeed 1%, rather than just appearing as 1% as formatted.

**KeithHenn** · 09-08-2011, 10:17 AM

You are correct. It was a long day yesterday I guess dyslexia must have set in! I just have one other question surrounding the formula. How would the formula need to change to handle negative percentages? I have some examples where the percentages are negative and the results are off. Thank you very much for your help, it is greatly appreciated.

- Solved 1 of my numbers was off by a number and it was thrown off. Thank you again!!

Keith