If you have ever needed to allocate an amount over time, call of duty 5 world at war multiplayer and split the amount into monthly columns based on the number of days, youll quickly realize that this simple idea is tricky to actually implement.
That is right, my friend, we can use sumproduct to do just this (and much more).
Taking the simple case, if we wanted to allocate a value from 1/1/14 to 3/31/14, there are 90 days that we need to allocate.We take an amount, divide it by the number of days in the allocation period, and then multiply this daily amount by the number of days represented by each column in the allocation table.(eomonth(H8,0)eomonth(C9,0) The above formula takes Segment C minus Segment F and then adds 1 if the last day of the current column period is equal to the start date period.It assumes that A2 is smaller than.We compute Segment C (the first line in the formula above) and subtract from it Segment F (the second line).Since there are 24 hours in a day, converting to hours is a simple multiplication 24 *.3333 or 32 hours.The unit of time in Excel is the Day.It also needs to work when the current period is greater than the end date.The Date: 7 Dec, 2010, the Time: 2:00 PM, dates in Excel.The resulting worksheet is shown in the screenshot below.Posted on November 10th, 2009.So adding a Date serial number, like 40518, to a Time serial number, like.25, gives us 40518.25.Lets assume that the column headers in the monthly allocation table are date values that represent the last day of the month.Allocation (original file) Allocation2 (this updated version has a sheet that excludes weekends from the allocation) Allocation3 (this updated version has a sheet that does daily allocation columns) Allocation4 (this updated version has a sheet that does monthly allocation columns) Allocation5 (this updated version has.
This is because 1/31 1/1 is 30 days, but, we need to make the allocation based on 31 days.At this point it might seem like an almost useless function.You can do so in Excel using, wait for it that is right, and operators, like this: "No between Formulas in Excel Summary and Examples: Here is a list of examples and the corresponding Excel Formulas to test the between condition.Our modified formula, shown below, will populate the Days column just fine.Well the Excel folks started a numbering system with Dates.Featured, Learn Excel - 244 comments, today we will learn a new and exciting excel formula the all powerful sumproduct.The updated allocation table is shown below.
This a date serial number and it makes Date calculations easy.