Thanks:  0
Likes:  0

1. ## Re: Looking for help with an addition formula.

Ah interesting. Also don't be sorry, if I haven't given you something that works, that's not your fault!

Hmm I'm a little stumped as to how you could do this neatly in one formula. I'll have to give it some thought

2. ## Re: Looking for help with an addition formula.

This kind of iterative processing is very tricky to accomplish with native Excel functions, especially with non-continuous functions. Compu-babble aside, it just means that the result of the February calculation is dependent on the result of the January calculation, etc. In order to accomplish this, you actually need to calculate each individual monthly total, then select the one you need. For example:

Excel 2012
AF AG AH AI AJ AK AL AM AN AO AP AQ AR
4 8-Dec
5 45 45 45 45 45 45 45 45 45 45 45 45 45
Sheet1

Worksheet Formulas
Cell Formula
AF5 =IF(MONTH(\$AF\$4)=1,\$D5,OFFSET(\$AG5,0,MONTH(\$AF\$4)-1))
AG5 =MIN(60,MAX(0,IF(COLUMN(\$AG5)=COLUMN(AG5),\$D5,AF5)+1-SUM(OFFSET(\$F5:\$G5,0,(COLUMNS(\$AG5:AG5)-1)*2))))

Put the formula in AG5, and drag it across to AR5. Then put in the AF5 formula. You can now hide columns AG:AR if you want.

This is a bit easier with VBA. If you want to try that, open a COPY of your workbook. Press Alt-F11 to open the VBA editor. From the menu, select Insert > Module. In the window that opens, paste this code:
Code:
```Public Function GetLeave(ByVal StartVal As Long, ByVal MonthVals As Range, MyDate As Date) As Long
Dim i As Long

For i = 1 To Evaluate("MONTH(""" & MyDate & """)")
StartVal = Evaluate("MAX(0,MIN(60," & StartVal - MonthVals(1, 2 * i - 2) - MonthVals(1, 2 * i - 1) + 1 & "))")
Next i
GetLeave = StartVal
End Function```
Press Alt-Q to exit the editor. Now in AF5, put this formula:

=GetLeave(D5,F5:AC5,\$AF\$4)

The first parameter is the starting value, the next is the range containing the monthly totals, and the last is the date. With the appropriate use of \$, you can drag this formula down the column just like any other formula.

Hope this helps.

3. ## Re: Looking for help with an addition formula.

This is a great reply!! Thank you. Although, I literally just 5 minutes ago managed to do this by adding a column after every month. I went a completely different approach with it, since I kept getting stuck.

So in the very first "Days available" I ended up using =IF(OR(F5="",)*OR(G5=""),"",MIN(60,MAX(0,SUM(-F5,-G5,D5+1))))

THEN... in the following "Days available" (February) I take =IF(OR(I5="",)*OR(J5=""),"",MIN(60,MAX(0,SUM(-I5,-J5,H5+1))))

So basically the first month takes beginning balance, and the following months take from the previous month. Just like you said

It's not exactly what I was originally looking for, but we're happy with the results. It gives me a good running tally, and at the end of the year I sum it all up. So it's good now.

Anyways, just wanted to say thanks for the help. It was definitely a good learning experience. Thank you both for your help and replies!!!!!!!

4. ## Re: Looking for help with an addition formula.

My first stab at this actually involved adding another column for each month, like you ended up doing. I didn't know if you wanted to do that, which is why I added them all on the right. Incidentally, you can shorten your first formula a bit:

=IF(COUNTA(F5:G5)=0,"",MEDIAN(0,60,D5-F5-G5+1))

and the other one in the same way. MEDIAN returns the middle number of the 3, and if you look at all the possibilities, you'll see it works out right in each case.

In any case, glad we could help, and glad you got something that works for you!

5. ## Re: Looking for help with an addition formula.

Nice figuare9. This is also where I was heading... an additional column was the only way I could get it to work without VBA, for the exact iteration issue that Eric W stated. Glad we could have some fun with it along the way though!

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•