Let’s say you want to allocate a value across multiple months based on a start date and how many months you want to allocate. The monthly allocation will be averaged based on the number of months. The solution isn’t that hard.
The table below shows the layout we will use. Columns A to D are for input and columns F to Q are formula columns. You can download the example at the end of the post.
The formula in cell F2 is
=IF(AND(F$1>=$C2,F$1<EDATE($C2,$D2)),$B2/$D2,0)
The formula has been copied across and down. Note the $ signs – these allow you to copy the formula down and across.
The AND function allows you to handle multiple logical tests and it returns a single TRUE or FALSE. When using AND all of the logical tests must return TRUE for AND to return TRUE. If any of the logical tests is FALSE then the AND function will return FALSE.
The first logical test is comparing the start month in column C to the month in row one.
The second logical test uses the EDATE function.
The EDATE function increments a date based on a number of months. The second logical test is confirming that the month in row one does not exceed the number of months from the start month.
The calculation performed is straightforward, dividing the value by the number of months.
If the month is not within the date range then a zero is returned.
“dear sir,
i am a accountant in government institue.creating salary bills my job.
please send me a excel sheet with formula which we give him amount one month ,sheet should automatic gernate salary betweent to diffrent period and add increment decenber .for example,
salary bill
period 1-oct-2019 to 31-dec-2019.
amount / month.1200=calculate amount oct to nov 2019 ,i want show ruslt with formula.rate/month with add increment amount december =show ruslt with formula…=g.total
.”
Hello
Sorry not sure what you want please send me an example of the layout you want to a4@iinet.net.au.
Regards Neale