Google Sheets – Calculate Average from Last 14 Days

I'm guessing you basically want to sum up the expenses for each month, and then find an average amount per month.

Let's first sum up expenses for each month. Each row in your spreadsheet has a date, but that does not immediately link it to other rows within the same month.

So let's introduce a new column Month, derived from the date. There is a formula =MONTH, so let's use that. It takes a date as parameter, and returns 1 for January, 2 for February and so on:

 A: Date     B: Amnt   C: Month
 11/16/2016  $133      =MONTH(A2): 11
 1/7/2017    $ 30      =MONTH(A3): 1
 1/15/2017   $ 50      =MONTH(A4): 1
 2/21/2017   $120      =MONTH(A5): 2
 2/28/2017   $300      =MONTH(A6): 2

Then we can write a =QUERY to group expenses per month:

=QUERY(A2:C;"select C, sum(B) group by C"; 0)

This should give us:

11  $133
 1  $80
 2  $420

So far, so good. But wait a minute: What if we add an expense ($44) for November 2017? That reveals a problem - November 2016 and November 2017 will be summed up together, so:

11  $177 ($133+$44)
 1  $80
 2  $420

So we don't want to group just by month - we want to group it by year/month, so that November 2016 is summed separately from November 2017.

We can fix that by appending =YEAR to the Month column:

 A: Date     B: Amnt   C: Year/Month
 11/16/2016  $133      =YEAR(A2) & "/" & MONTH(A2): 2016/11
 1/7/2017    $ 30      =YEAR(A3) & "/" & MONTH(A3): 2017/1
 1/15/2017   $ 50      =YEAR(A4) & "/" & MONTH(A4): 2017/1
 2/21/2017   $120      =YEAR(A5) & "/" & MONTH(A5): 2017/2
 2/28/2017   $300      =YEAR(A6) & "/" & MONTH(A6): 2017/2
 11/1/2017   $ 44      =YEAR(A7) & "/" & MONTH(A7): 2017/11

That will automatically fix our query result, so it is now:

2016/11  $133
2017/1   $ 80
2017/2   $420
2017/11  $ 44

If our query result is output in column F, we can now get the =AVERAGE per year-month:

=AVERAGE(F2:F)

I have made an example spreadsheet to demonstrate this, feel free to copy it.

This solution solves for calendar days. In order to calculate per diem, you need to know the sum of funds and the days they would be distributed across (e.g. $100 for 10 days is $10/day).

Where the sum for the remainder of the pay period is in G2, the divisor is the difference in calendar days between today and either the 15th or last day of the month (or the previous business day if a non-business day), based on the day of the month.

For a concrete example, today is the 6/21, the last day of the month is 6/30. 6/30 is a Saturday, so the previous business day is 6/29. There are eight days (including today) between now and the pay date, so the formula would be G2/8.

This formula solves for per diem based on that pattern, and works for today() being any day in the month:

=G2/if(day(today())<16,workday(workday(concatenate(month(today()),"/15"),1),-1)-day(today()),workday(workday(EOMONTH(today(),0),1),-1)-today())

This formula does not account for holidays