Need Help with a Formula

User avatar
BobH
UraniumLounger
Posts: 8338
Joined: 13 Feb 2010, 01:27
Location: Temple - Deep in the Heart of Texas

Need Help with a Formula

Post by BobH »

As happens far too frequently these days, my mind will not stretch to even a clue about how to set up a formula.

Here's the problem. I monitor our electricity consumption. I can download usage since the last billing date. I do so and load it into a spreadsheet (attached below). The days in the billing cycle are defined when I set up the spreadsheet. I've created a named range that contains the actual Kwh consumed each day. I created a formula that calculates as =SUM(July_Usage). This works fine. I'd like to be able to have the average daily consumption calculated without my entering the cell that contains the number of days. IOW, I'd like to be able to determine the last row with data and use the A column value to use as divisor to the total usage.

Can this be done? If so, how?
July Usage.xlsx
You do not have the required permissions to view the files attached to this post.
Bob's yer Uncle!
(1/2)(1+√5)
Intel Core i5, 3570K, 3.40 GHz, 16 GB RAM, ECS Z77 H2-A3 Mobo, Windows 7 >HPE 64-bit, MS Office 2016

User avatar
HansV
Administrator
Posts: 72002
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Need Help with a Formula

Post by HansV »

You can use

=AVERAGE(Usage_July)

The AVERAGE function only looks at filled cells. It ignores blank cells that have not yet been filled in.
Regards,
Hans

User avatar
BobH
UraniumLounger
Posts: 8338
Joined: 13 Feb 2010, 01:27
Location: Temple - Deep in the Heart of Texas

Re: Need Help with a Formula

Post by BobH »

:drop:

Mental blocks come far too often when you near 80.

Thank you, Mr. V. :cheers: :chocciebar: :thankyou:
Bob's yer Uncle!
(1/2)(1+√5)
Intel Core i5, 3570K, 3.40 GHz, 16 GB RAM, ECS Z77 H2-A3 Mobo, Windows 7 >HPE 64-bit, MS Office 2016