PDA

View Full Version : Solved: sum(if..) until ..



neditheg
12-21-2011, 05:35 AM
Hello guys!

I need your help again. I've Attached an example.

So I'll try to write some rows to explain what I have to do.

In column A --> I have agents names.
In column C --> I have the agents outstanding leave days from 2010
In column D --> I have the amount of money to pay to the agents for the outstanding leave days from 2010 at the end of 2010.

Next ... I have the situation of the leave days taken in each month of 2011 and the amount of money payed each month..to the agents for those days.(days, cost).

Now .. in column AO I have the number of vacantion days taken in 2011
AP ... same thing in hours and and in column AQ I have the cost for this days.

What I want is this--> In column I want to calculate the Cost of vacation days taken in 2011 overdue from 2010. So I need to sum all the cost colmns from each month ..until the number of outstanding leave days from 2010= the number of vacantion days taken in 2011. So for the first row in my example table "Maria Mihai" had 16 outstanding leave days from 2010 and she reached this number in august so I need to sum the cost from january to august to get the cost paid in 2011 for this days.

There is another situation ... and you can se that A8 cell "Paval Sorin " had 9 days from 2010 .. and he had 11 days of vacantion in january 2011 so in this case the cost paid in 2011 for the 2010 days is : {( G8 / 11) *9 } {( 1121/11) * 9 =917.19.

But I have other cases when an agent has 8 days from 2010....he had 3 leave days in janury , 2 leave days in february , and 6 in march ...so the cost I want to calculate for him is

january cost+february cost+ [ march cost/6)*3] =x .

Hope you guys can understand this and you can give me an answer.

Thanks!!

Bob Phillips
12-21-2011, 11:14 AM
I would build a UDF



Public Function LastYearCost(ByVal rng As Range, ByVal carry As Range) As Double
Dim lastCost As Double
Dim tmp As Double
Dim residual As Long
Dim i As Long

residual = carry
For i = 1 To rng.Cells.Count Step 3

If rng.Cells(1, i).Value > 0 Then

If residual > rng.Cells(1, i).Value Then

lastCost = lastCost + rng.Cells(1, i + 2).Value
residual = residual - rng.Cells(1, i).Value
Else

tmp = rng.Cells(1, i + 2).Value / rng.Cells(1, i).Value * residual
lastCost = lastCost + tmp
Exit For
End If
End If
Next i

LastYearCost = Round(lastCost, 2)
End Function


and call like so

=LastYearCost(E3:AN3,C3)