Automatic Date Based Formulas Without VBA

BustACode

New Member
Joined
Sep 17, 2015
Messages
2
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.


A
B
C
D
1
DateRev.CostsGross
2
4/20/2013$63.45$41.00$22.45
3
4/21/2013$35.45$3.00$32.45
4
4/22/2013$42.15$15.00$27.15
5
4/23/2013$44.45$16.00$28.45
6
4/24/2013$39.45$22.00$17.45
7
4/25/2013$34.60$16.00$18.60
8
4/26/2013$37.10$23.00$14.10
9
4/27/2013$43.90$21.00$22.90
10
4/28/2013$42.85$4.00$38.85
11
4/29/2013$61.20$15.00$46.20

<tbody>
</tbody>

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

Formula 1:
Code:
=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
Code:
=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:
Code:
=(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:
Code:
=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.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Forum statistics

Threads
1,186,662
Messages
5,959,062
Members
438,396
Latest member
Louise Rodway

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top