Addition formula.

[figuare9]

I'm currently trying to create a formula that will allow me to enter in sick days for employees. I'm a little bit stuck with it though at the moment, and could really use a hand..

Basically, Every month an employee "earns" one sick day, up to a maximum of 60. At the beginning of the year, I'd like to enter the amount that they have starting in the beginning balance.

So For instance, cell D5 & E5 (Merged) is the beginning balance. Then in every other Cell F5, H5, J5, etc.. I'll enter in their "excused" sick days per month. And of course, cells next to them will be "unexcused" sick days. Cells G5, I5, K5, etc..

What I'd like is that in cell AF5 to show me the total days they have available, but I'd like it to automatically calculate the accruing days the employee earns every month. So if start with 50 sick days, and I enter in 3 sick days for Jan, They should have 48 sick days available at the very end of Jan. (Since they earn one every month). The days are entered at the very end of the month, so the days available "Todays Date" Should only be shown AFTER entering data for the month. If that makes sense.. lol

Also, an excused and unexcused count the same, but are tracked elsewhere. I guess, the easiest way to see is to just look at the picture.

So if I start with 50 sick days, and enter 4 excused, and 1 unexcused, (And I only enter in January) I should see 46 available days because it should automatically calculate the one accruing day.

This is what I have..

=IF(OR(F5="",)*OR(G5="")*OR(H5="")*OR(I5="")*OR(J5="")*OR(K5="")*OR(L5="")*OR(M5="")*OR(N5="")*OR(O5="")*OR(P5="")*OR(Q5="")*OR(R5="")*OR(S5="")*OR(T5="")*OR(U5="")*OR(V5="")*OR(W5="")*OR(X5="")*OR(Y5="")*OR(Z5="")*OR(AA5="")*OR(AB5="")*OR(AC5=""),"",MIN(60,MAX(0,SUM(-F5,-G5,-H5,-I5,-J5,-K5,-L5,-M5,-N5,-O5,-P5,-Q5,-R5,-S5,-T5,-U5,-V5,-W5,-X5,-Y5,-Z5,-AA5,-AB5,-AC5,D5,))))

But it's not calculating accruing days. Not really sure how to do that.. This picture here, should show me 45 days available.

 

[NiMip]

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

 

[Eric W]

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]
[TD="align: right"]8-Dec[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]45[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AF5[/TH]
[TD="align: left"]=IF(MONTH($AF$4)=1,$D5,OFFSET($AG5,0,MONTH($AF$4)-1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AG5[/TH]
[TD="align: left"]=MIN(60,MAX(0,IF(COLUMN($AG5)=COLUMN(AG5),$D5,AF5)+1-SUM(OFFSET($F5:$G5,0,(COLUMNS($AG5:AG5)-1)*2))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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:

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.

 

[figuare9]

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!!!!!!!

 

[Eric W]

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!

 

[NiMip]

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!