# Sum until?

#### alinta

##### New Member
I have a column of daily rainfall data in, say column A. What I want to do is apply a loss to that data, so that each time a new rainfall event occurs (i.e. there was no rain the day before), it will take off say 20mm. HOWEVER, the rain on that day might be less than 20mm, and also the day after, so it may be neccessary to spread that 20mm loss over several days.

I have got as far as working out whether or not a loss should be applied, but can't work out how to calculate that loss.

Example data:
 Rainfall Apply Loss? Loss Amount 2.5 Y 2.5 11.8 Y 11.8 12.8 Y 5.7 0.6 Y 0 0.0 0 0 0.0 0 0 2.7 Y 2.7 4.7 Y 4.7 4.8 Y 4.8 10.6 Y 7.8

<tbody>
</tbody>

My apply loss is formula for cell n+1 is: =IF(cell_n+1=0,0,if(cell_n>20,0,"Y"))

What I want in the 'Loss amount column' is the actual loss amount that should be taken off. So, for example, if it only rains 2mm, then I can't take of 20mm, I can only take off 2mm. But if the loss hasn't re-set (i.e. we've had continuous rain over a week), then I need to check all the cells above it (after the last non-rain day) and check if I've already taken off the 20mm.

I've put what should be the answers in the table above in italics.

Any suggestions? Thanks in advance.

#### pgc01

##### MrExcel MVP
Hi

What if the total of a period of consecutive days with rain is less than the 20mm of rain?

For ex.:

In this case the first 2 periods with rain have less than 20mm in total. What happens in this case?
Can you please fill the table?

#### alinta

##### New Member
Can you please fill the table?

ABCD
1RainfallApply Loss?Loss Amount
22.5 Y
2.5
30 N
0
412.8 Y
12.8
50.6 Y
0.6
60 N
0
70 N
0
82.7 Y
2.7
94.7 Y
4.7
104.8 Y
4.8
1110.6 Y
7.8
120N
0
1314
Y
14
1415 Y
6
1512N
0
1611 N
0
17

<tbody>
</tbody>

#### pgc01

##### MrExcel MVP
Hi

An easy way is to use an auxiliary column to calculate the partial sum of each raining period. This way you can solve your problem with very basic formulas.

Try

in E2: =IF(B2<>"Y",0,SUM(E1,A2))
in C2: =IF(B2<>"Y",0,MAX(0,MIN(A2,20-N(E1))))

Copy down

Remark: you can have a solution without the auxiliary column, but it will be a more complex and inefficient formula.

#### alinta

##### New Member
Thanks PGC - that's working perfectly.

#### pgc01

##### MrExcel MVP
You're welcome. Thanks for the feedback.

1,081,976
Messages
5,362,511
Members
400,678
Latest member
alecalec202

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...