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:
<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.
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.
