Help with a formula to sum values and stop at certain value

[Rajabajar]

Hi,

As per attached file, this is the problem that I need help with:

Premise: Information on “Summary Table” is pulled from “Table 1” data. The data values in “Table 1” change over time (more data is added)

Problems:
1. I need a formula on Cell E3 that sums the values from “Summary Table” Column B (it can sums the values individually or just pull the sub-total or even directly the total) but only until E3 reaches the exact value displayed on D3. When reaching that value E3 should stop summing from Column B so that E3 can’t be higher or smaller than value on D3.

2. I need a formula on cell E4 that once E3 reaches the value displayed on D3 then and only then starts summing values from “Summary Table” Column B This formula will stop summing values from Column B once it reaches the value displayed on cell D4. This same formula will he dragged to E5.

3. Ultimately, the values on (E3 + E4 + E5) = B15. When adding the values of E3, E4 and E5 together must equal the value displayed on B15. So in certain way, the formulas complement each other so that they are not summing the same values on “Summary Table” column B.

4. In principle it does not matter the order in which the formulas on E3, E4 and E5 are summing the values from “Summary Table” column B. But the data on “Table 1” is ordered chronologically (based on date, month and year.)

Can Excel and someone smarter than me help me resolve this?

Thanks,
Rajab

 

[p45cal]

E3:
=MIN($B$15,D3)

E4:
=MIN($B$15-SUM(E$3:E3),D4)

E5 and below:
Copy formula in E4 down.

Re. point no.4
This will happen as long as the values in TABLE 1 sum to less than the sum of cells D3:E3, otherwise your constraints make this stipulation impossible.

 

[Peter Bartholomew]

My solution was

= MMULT(DiffMat, IF(SUM(Source)<limits, SUM(Source), limits))

  where DiffMat is
 
={1,0,0;-1,1,0;0,-1,1}

Using 365 with Charles Williams's FastExcel functions I started with

= LET(
  total, SUM(Source),
  capped, IF(total<limits, total, limits),
  DIFF(capped,0))

but that is not going to help anyone else.

 

[Rajabajar]

E3:
=MIN($B$15,D3)

E4:
=MIN($B$15-SUM(E$3:E3),D4)

E5 and below:
Copy formula in E4 down.

Re. point no.4
This will happen as long as the values in TABLE 1 sum to less than the sum of cells D3:E3, otherwise your constraints make this stipulation impossible.

Hi,

First of all an most important, thanks for your reply! Much appreciated!
Second, could you please elaborate on why the values on TABLE1 would have to be always less than the sum of cells D3:E3.
Thanks again!

 

[Rajabajar]

My solution was

= MMULT(DiffMat, IF(SUM(Source)<limits, SUM(Source), limits))

  where DiffMat is

={1,0,0;-1,1,0;0,-1,1}

Using 365 with Charles Williams's FastExcel functions I started with

= LET(
  total, SUM(Source),
  capped, IF(total<limits, total, limits),
  DIFF(capped,0))

but that is not going to help anyone else.

Hi,
Same as before...first and most important thanks for your reply.

I'm sort of familiar with MIN formulas (I used them before for other things) and tried what p45cal suggested and worked. However, I'm always open to learn new ways, and although my Excel understanding is somewhat limited, I wondered if you could explain me in more detail how to use the CODE solution that you sent to my problem as I'm not familiar with it. If not, I get it, as perhaps is just way above my Excel knowledge , but if you have time and the desire to break it down for me and explain me how to exactly apply/use it, I would love to give a try to your solution too. Thanks again!

 

[Peter Bartholomew]

Hi @Rajabajar

The term
= IF(SUM(Source)<limits, SUM(Source), limits))
does the same thing as MIN (i.e. imposes and upper limit on Source) but it works with an array of Source items to give an array of capped values. MIN would just give a single value which would normally be the smallest Source value within the array.

The MMULT part of the formula, applied with the array constant,
={1,0,0;-1,1,0;0,-1,1}
gives the difference between adjacent terms of the array. If larger arrays are considered, the method is less attractive because the number of 'multiply by zeros' would increase as a proportion of the overall calculation. Using INDEX or OFFSET would scale better to large arrays but a function such as DIFF would be better still. The array constant 'DiffMat' is shown below. MMULT performs a SUMPRODUCT-type operation between rows of 'DiffMat' and the column of capped scores to give pairwise differences.

 

[p45cal]

Second, could you please elaborate on why the values on TABLE1 would have to be always less than the sum of cells D3:E3.

My comment on your point no. 4 was based on the comment no.4 in the workbook: "4. No matter what (E3 + E4 + E5) must be always equal to B15" rather than the point no. 4 in your msg#1. You're limiting the max values in E3:E5 to those in D4:D5, regardless of what's in the Table 1, so if those total less than what's in Table 1 (=B15) there's going to be a difference. The only time they'll be the same is if D3:D5 sum is more than what's in the Table.