First of all, this is more of an excel-related question than an openpyxl-related one, but I mention it because the solution needs to be feasible by openpyxl means.
The problem:
I'm trying to generate a spreadsheet using openpyxl, containing the quantity of some products (rows) and their selling prices(rows) for each month (columns):
the generated worksheet looks like this
All rows have an associated defined name. For "example product_1_quantity" or "product_1_selling_price".
I would like to create a row that calculates the total value of my products by multiplying each quantity by its selling price, and then adding all together.
What I've tried:
I've already done what I want by inserting a CSE formula in my target row like: {=product_1_quantity*product_1_selling_price + product_2_quantity*product_2_selling_price + product_3_quantity*product_3_selling_price} where "product_1_quantity" is defined as "E15:P15" and "product_1_selling_price" is defined as "E18:P18" and so on.
What I'm looking for:
I would like to find a cleaner way (maybe using SUMPRODUCT column-wise?) that does not make my formula incredibly long when adding extra products BUT not dropping the use of my defined names.
I would like to keep using the defined names for each product because they make my formulas much more readable and maintainable (not only from excel perspective, but also from python-openpyxl perspective). I would also accept using, for example, a defined name for the whole range containing prices and another for the range containing the quantities, as long as the code and the excel can remain quite readable and maintainable.
Define a name quantity for E15:T17 and another prices for E18:T20 then use the CSE formula =MMULT({1,1,1},quantity*prices) in row 14.
If you have a version of Excel that supports dynamic arrays, you can enter the formula in E14 and it will spill to the other columns automatically.
If you add products, the number of ones in the first argument will need to equal the number of products.
