Can math functions be applied to the array inside the product function?

Question

I have multiple discounts: 8%, 1%, 5%, 3.5%, 9 total. To apply a discount on top of a discount, they can be converted to .92, .99, .95, .965, etc. and then multiplied. I seem to remember seeing something like =product(1-a1:a9/100) where the math calculations are applied to the array elements before the product is evaluated. I cannot get this to work.

Answer 1

In Excel, the PRODUCT function can indeed be applied to an array, but the syntax you mentioned (=PRODUCT(1-A1:A9/100)) will not work as intended because the division operation needs to be performed element-wise before the PRODUCT function is called. Instead, you can use an array formula to achieve this.

To apply the discounts correctly, you can use the following formula:

=PRODUCT(1 - A1:A9/100)

However, to ensure that this formula works correctly, you need to enter it as an array formula. In Excel, you can do this by pressing Ctrl + Shift + Enter after typing the formula, which will wrap it in curly braces {} indicating it's an array formula. This way, Excel will evaluate each element in the range A1:A9 individually and then multiply the results together.

If you're using Excel 365 or Excel 2021, you can simply enter the formula without needing to use Ctrl + Shift + Enter, as these versions support dynamic arrays natively.

Answer 2

Hi,

To calculate multiple successive discounts in Excel, you need to convert each discount into a multiplier and multiply them together. The combined multiplier is:

 

=PRODUCT(1 - A1:A5/100)

 

Works directly in Excel 365/2021 (dynamic arrays).

In older Excel, enter as an array formula with Ctrl + Shift + Enter.

If discounts are formatted as percentages, use:

=PRODUCT(1 - A1:A5)

 

Let me know how it goes, I’m here to help!