Automatic Date Based Formulas Without VBA

[BustACode]

So I now have a small business that uses cash accounting. Since I don't need much in the way of accounting, I use an Excel workbook for the numbers. Overtime I needed to see sums and averages based on time--7 days, 30 days, etc.

To accomplish this, without using the dreaded--for me--VBA, I used to use a formula that I would copy and paste. Excel would then retrieve the desired numbers relatively, and I would have my desired information. However, what I wanted was a "dashboard," sums and averages that would just update each day on their own, without resorting to VBA.

I gave up about three times, and then, while trying to solve another thing, I found the solution: Combining "INDIRECT," ADDRESS," and "TODAY" into a complex offset formula that would auto update at the start of each day.


[TABLE="class: grid, width: 312"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]Date[/TD]
[TD]Rev.[/TD]
[TD]Costs[/TD]
[TD]Gross[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: right"]4/20/2013[/TD]
[TD="align: right"]$63.45[/TD]
[TD="align: right"]$41.00[/TD]
[TD="align: right"]$22.45[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: right"]4/21/2013[/TD]
[TD="align: right"]$35.45[/TD]
[TD="align: right"]$3.00[/TD]
[TD="align: right"]$32.45[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: right"]4/22/2013[/TD]
[TD="align: right"]$42.15[/TD]
[TD="align: right"]$15.00[/TD]
[TD="align: right"]$27.15[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: right"]4/23/2013[/TD]
[TD="align: right"]$44.45[/TD]
[TD="align: right"]$16.00[/TD]
[TD="align: right"]$28.45[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: right"]4/24/2013[/TD]
[TD="align: right"]$39.45[/TD]
[TD="align: right"]$22.00[/TD]
[TD="align: right"]$17.45[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: right"]4/25/2013[/TD]
[TD="align: right"]$34.60[/TD]
[TD="align: right"]$16.00[/TD]
[TD="align: right"]$18.60[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: right"]4/26/2013[/TD]
[TD="align: right"]$37.10[/TD]
[TD="align: right"]$23.00[/TD]
[TD="align: right"]$14.10[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: right"]4/27/2013[/TD]
[TD="align: right"]$43.90[/TD]
[TD="align: right"]$21.00[/TD]
[TD="align: right"]$22.90[/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: right"]4/28/2013[/TD]
[TD="align: right"]$42.85[/TD]
[TD="align: right"]$4.00[/TD]
[TD="align: right"]$38.85[/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD="align: right"]4/29/2013[/TD]
[TD="align: right"]$61.20[/TD]
[TD="align: right"]$15.00[/TD]
[TD="align: right"]$46.20[/TD]
[/TR]
</tbody>[/TABLE]

The following pulls the value of a cell referenced by internal coordinates, not cell address:

Formula 1:

=INDIRECT((ADDRESS(2, 2)))

This will return the value in cell B2, $63.45, as it is at coordinate 2, 2 (Row, Col.).

Now, we need the date related part. Type

=TODAY()

into an unused number formatted cell. Today, 9/17/2015 will show a number, 42264. Now, if we desire a 2 for the row, then we must subtract 42262 from the date serial number:

Formula 2:

=(TODAY()-42262+0)

The "+0" is the range-offset, and will be referred to later.

Now substitute Formula 2 into the row designator in Formula 1 like so:

Formula 3: =INDIRECT((ADDRESS(TODAY()-42262+0,2)))

If completed correctly, the formula should return $63.45 from B2 (2,2).

Now the fun part. If you desire an average of over, say 7 days, use Formula 3 as the start and end of your desired range to average, and then set the range-offset of the "bottom" of your range, less one (As is inclusive of populated cell)--"+6." *The "bottom" is the part to the right of the colon in your "AVERAGE" formula.

Formula 4:

=AVERAGE(INDIRECT((ADDRESS(TODAY()-42262+0,2))):INDIRECT((ADDRESS(TODAY()-42262+6,2))))

So, we now have, in Formula 4, two of Formula 3 as range anchors, and the "bottom" one further modified for our 7 day range (Using "+6"). This should return the average of cells B2:B8, which is $42.38. Check it with a conventional 'AVERAGE" formula.

Now, this formula will "slide" down your data, and update automatically, to give you the "SUM," "AVERAGE," or whatever at midnight of each day.

You can modify the range-offset value as need for various ranges: "+29" for 30 days, etc. You can even use controls to and other cell references to adjust the ranges on the fly.

And of course, adjust the "Col." number to reference the required column.

A note caution: If you add rows or columns, Formula 4 will not automatically adjust itself. You will need to test, and then modify as needed.

Enjoy.