Share via

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

Jack Miller 60 Reputation points
2025-12-17T18:34:12.99+00:00

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.

Microsoft 365 and Office | Excel | For home | Windows
0 comments

Answer accepted by question author

AI answer

2025-12-17T18:34:24.55+00:00

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.

AI-generated content may be incorrect. Read our transparency notes for more information.

Was this answer helpful?

1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Harold Milan 17,505 Reputation points Independent Advisor
    2025-12-17T19:16:16.0133333+00:00

    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!

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.