How to calculate?

[Mix1325]

I need to sum the values corresponding to multiple locations, months, and products until a certain max value is reached. Also, whenever the values corresponding to a certain month and location need only to be "used" partially (that is not the entire Remaining Value) to reach the max value, I need to include just a portion of each product present at that location in that month based on the percentage from the total - please see attachment for clarifications. An example solved manually is also provided.

I would prefer not to use VBA.

Thank you in advance for all your help and support in this matter.

 

[John Jairo V]

Hi, @Mix1325

This could be an option:

=IF(SUMIFS(H$2:H$23,D$2:D$23,"<="&D2,F$2:F$23,F2)<VLOOKUP(F2,A$2:B$3,2,),H2,IF(MEDIAN(SUMIFS(H$2:H$23,D$2:D$23,{"<=";"<"}&D2,F$2:F$23,F2),VLOOKUP(F2,A$2:B$3,2,))=VLOOKUP(F2,A$2:B$3,2,),(VLOOKUP(F2,A$2:B$3,2,)-SUMIFS(H$2:H$23,D$2:D$23,"<"&D2,F$2:F$23,F2))/SUMIFS(H$2:H$23,D$2:D$23,D2,F$2:F$23,F2)*H2,))

Check file with formula applied. Blessings!

 

[Peter Bartholomew]

Another strategy is to forego the 'killer formula' approach and instead build the solution in a number of steps using helper ranges. In the attached, I have used a summary table that aggregates data by location and month for the calculation of percentages, rather than repeating nested calculations within each row of the original table.

 

[Mix1325]

Hi, @Mix1325

This could be an option:

=IF(SUMIFS(H$2:H$23,D$2:D$23,"<="&D2,F$2:F$23,F2)<VLOOKUP(F2,A$2:B$3,2,),H2,IF(MEDIAN(SUMIFS(H$2:H$23,D$2:D$23,{"<=";"<"}&D2,F$2:F$23,F2),VLOOKUP(F2,A$2:B$3,2,))=VLOOKUP(F2,A$2:B$3,2,),(VLOOKUP(F2,A$2:B$3,2,)-SUMIFS(H$2:H$23,D$2:D$23,"<"&D2,F$2:F$23,F2))/SUMIFS(H$2:H$23,D$2:D$23,D2,F$2:F$23,F2)*H2,))

Check file with formula applied. Blessings!

Thank you for your help, John. Can you explain this portion of the formula used?

{"<=";"<"}

 

[Mix1325]

Another strategy is to forego the 'killer formula' approach and instead build the solution in a number of steps using helper ranges. In the attached, I have used a summary table that aggregates data by location and month for the calculation of percentages, rather than repeating nested calculations within each row of the original table.

Thank you for your help with this, Peter.

 

[Peter Bartholomew]

I hadn't noticed that part of John's formula. I am caught somewhere between intense admiration and horror when I see such formulas!

The term you quoted

{"<=";"<"}

is a column array constant containing two text values. When used as a condition in SUMIFS it will cause the formula to be evaluated twice, the first time including the cutoff value and the second time excluding it. Sometimes one outputs the two results as an array formula but, more often, they are either aggregated or one is chosen.

 

[Mix1325]

Thank you for all your help!