Summing to a point

[hardgrafting]

Hi,

I have the below data that I want to sum, but only to a certain point:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Number of cats[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cumulative cats[/TD]
[TD]7[/TD]
[TD]15[/TD]
[TD]25[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cap on the number of cats[/TD]
[TD]33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have information on the number of cats in each house, as can be seen it is 7 in one house, 8 in the other house, 11 in another house and 12 in another house. I am adding them cumulatively in the column below, as can be seen from the 7-15-25.

However, there is a cap on the number of cats in the homes. As such, the house with 11 is only allowed to have 8 cats, since adding 8 to 25 makes it 33. Can this be done as a formula? Which will sum until a point and then at the number which exceeds the cumulative cap, will get reduced to the cap?

 

[gaz_chops]

Wouldn't the cumulative be

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Cumulative cats[/TD]
[TD]7[/TD]
[TD]15[/TD]
[TD]25[/TD]
[TD]33[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[hardgrafting]

Correct, yes, I meant this for illustration.

I basically want a formula, that will make the 11 8 automatically, since this is the cap

 

[gaz_chops]

Then try

=IF(A2=33,0,MIN(33,SUM($B$1:B1)))

Assuming your table starts in cell A1

[TABLE="width: 583"]
<colgroup><col width="148" style="width: 111pt;"><col width="87" span="5" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 148"]Number of cats[/TD]
[TD="class: xl63, width: 87, align: right"]7[/TD]
[TD="class: xl63, width: 87, align: right"]8[/TD]
[TD="class: xl63, width: 87, align: right"]10[/TD]
[TD="class: xl63, width: 87, align: right"]11[/TD]
[TD="class: xl63, width: 87, align: right"]12[/TD]
[/TR]
[TR]
[TD="class: xl63"]Cumulative cats[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[gaz_chops]

or this is better if you have more columns

=IF(MAX($A$2:A2)=33,0,MIN(33,SUM($B$1:B1)))

 

[hardgrafting]

Hi Gaz chops, thanks for this, think I havent explained myself well.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[/TR]
[TR]
[TD]Number of cats[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cumulative cats[/TD]
[TD]7[/TD]
[TD]15[/TD]
[TD]25[/TD]
[TD]33[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cap on the number of cats


The cumulative cats should be 36 where it is 33. I would like to restrict the 36 to 33 and going forward, I would like to make the next cumulatives after the 33, always zero

[/TD]
[TD]33[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[hardgrafting]

To be very clear, what I want to do, is have output instead of 11, this should be 8, which would infer, that the house with 11 cats, can only have 8 cats.

So the final output should become

[TABLE="class: cms_table_cms_table, width: 500"]
<tbody>[TR]
[TD]Number of cats[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]10[/TD]
[TD]8[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]

 

[gaz_chops]

No idea what you mean! If the cap is 33 where does 36 come in?

show an example with the actual result you expect.

 

[hardgrafting]

Okay, really sorry, I am not the best excel user, so I will explain for the very beginning again

Number of cats per house: This shows the number of cats, per each household. Total of 5 households, each with 7/8/10/8/12 cats respectively.

Cumulative cats per house: This shows the cumulative cats, summing the number of cats per household

Cats allowed per house: This shows the numbers of cats allowed per house. Note, that the final 2 house are allowed only 8 cats and the final house, is allowed 0 cats. This is because the cap is 33.

What I would like, is a formula, that takes into account the cap and basically using the number of cats per house data and the cap, solves for the "cats allowed per house line".

Hope this makes sense


[TABLE="width: 459"]
<colgroup><col span="9"></colgroup><tbody>[TR]
[TD="colspan: 3"]Number of cats per house[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Cumulative cats per house[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]15[/TD]
[TD]25[/TD]
[TD]36[/TD]
[TD]48[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Cats allowed per house[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]10[/TD]
[TD]8[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CAP[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Is this what you want?

Excel 2013/2016
	A	B	C	D	E	F
1	Number of cats per house	7	8	10	11	12
2	Cumulative cats per house	7	15	25	36	48
3	Cats allowed per house	7	8	10	8	0
4
5	CAP	33
Color

Cell Formulas
Range	Formula
B2	=SUM($B$1:B$1)
B3	=IF(B2>$B$5,MAX($B5-A2,0),B1)