3

I have a range of percentages in cells A1:A5. I want to calculate how much all of these percentages compound up to and give a final factor.

e.g., 1.00 times A1 (10%) times A2 (6.5%) is 1.1715.

I can't think of a way to do this dynamically. The best I have is:

=(1 + A1) * (1 + A2) ...

which is not graceful at all.

The range length constantly changes as I enter new data so a fixed approach like that just doesn't work. The range could be A1:A3, then A1:A9999 for example.

1

In one cell,

=arrayformula(product(1+A1:A))

does the job. Empty cells become 1 and do not affect the product. If there is a concern about unnecessary multiplication of many 1s coming from empty cells, use the filtered version

=arrayformula(product(1+filter(A1:A, len(A1:A))))
| improve this answer | |
1

UNTESTED. Please try in B1:

=ArrayFormula(if(A1:A=0,"",1+A1:A))

and

=product(B1:B)

IF
PRODUCT

I was hoping the OP would do some testing (I can't be bothered creating 9,999 rows of data OP already has) and since it seems, as usual, OP is not going to bother with any feedback I should explain that I offered two formulae in view of a comment regarding the "product guarantee":

Although PRODUCT is specified as taking a maximum of 30 arguments, Google Sheets supports an arbitrary number of arguments for this function.

In two pieces could make it easier to determine if some other, higher, limit than 30 exists. If not, I had planned to offer a formula that skipped the need for a helper column:

=product(ArrayFormula(if(A1:A=0,,1+A1:A)))

It seems this should work but is still a little suspect for me for very large numbers of multipliers.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.