When working with YTD percentages you must be careful with the calculation. Adding up values or amounts is easy. Working with YTD percentages require a bit more work.
I have written about this previously in the post below.
I thought I would update the formulas and add a custom function solution.
The solution in the past included the SUMPRODUCT function. In the latest Excel version, we can use the SUM function.
See the structure below and the formula solution in cell N3.
The values in row 2 are multiplied by the corresponding percentages in row 3. The resulting values (which equates to the defect quantities) are compiled and added up by the first SUM function. This total is then divided by the total of the values in row 2.
Custom function
If you are new to custom functions check out the post below.
We can develop a custom function using the formula progression below.
This is a common progression where a LET function is created to capture cells and ranges. Then the LAMBDA function is added to test and used to create the custom function as a range name.
The custom function is shown in the image below.
Please note: I reserve the right to delete comments that are offensive or off-topic.