Attention: The forums are currently placed on Read Only.

Thank you for visiting the CNET forums. Our site is currently undergoing some maintenance. During this period (6:30 AM to 8 PM PDT,) you can read the forums content, however posting in the forum will not be available. We apologize for this inconvenience. Click here to read details

Office & Productivity Software forum

General discussion

Excel Function Question

by Mike7p / February 14, 2010 6:38 AM PST

Every Saturday I record the readings from my Electricity & Gas meters in a spreadsheet which I use to calculate that weeks usage.
Week 1 reading for Gas goes into Cell E10, week 2 into E11 Week 3 into E12 and so on through to week 52.
In cell F11 I have put the function =(E11-E10) and filled down this formula down the column which produces the number of units used that week.
After 52 weeks I use Auto-sum to give a total for the year.
I do the same for Electric
On sheet 2 of the workbook I have set up a calculator that takes the value from column F each week, and using the conversion calculation provided by the power company to convert Units to Kilowatt hours and then multiply this by the cost per KwH. Standing charges and taxes are also added at the appropriate stage, giving a final total cost for the energy used each week.
At present I have to copy the weekly unit used into the calculator manually. The figure is copied from column F and pasted into Cell D10 in the calculator.
What I want to know is, is it possible to put a function in Cell D10 in the calculator so that the Units used figure is placed there automatically each week? If the Cell Address remained constant it would be simply a matter of putting ='Meters-10'!F16 in Cell D10 but as the following weeks reading would be in F17 this would not work. How do I tell Excel to use the value in the latest row in column F?
Many thanks

Discussion is locked
Collapse -
You can't use a function ...
by Kees Bakker / February 15, 2010 4:08 AM PST

in a cell to overwrite itself. Makes sense?

But you can program a macro to find the last filled cell in a column and do something with it (like putting it in a certain place).

Personally, I would write a custom function to do the calculations and call that function in the cell next to F16, F17 and so on. Something like =mytotalcosts(F16) and drag that down.


Collapse -
possible solution
by wil_peter / February 15, 2010 4:16 AM PST

Had you thought to define a range name, like "current", for the 52 rows in column E being used for the readings? Then in the calculator cell, you can use the function =MAX(current) to keep the calculator updated with the highest recorded reading.

Collapse -
by wil_peter / February 15, 2010 4:18 AM PST

Sorry Kees, your fingers fly faster than mine.

Popular Forums

Computer Newbies 10,686 discussions
Computer Help 54,365 discussions
Laptops 21,181 discussions
Networking & Wireless 16,313 discussions
Phones 17,137 discussions
Security 31,287 discussions
TVs & Home Theaters 22,101 discussions
Windows 7 8,164 discussions
Windows 10 2,657 discussions


Your favorite shows are back!

Don’t miss your dramas, sitcoms and reality shows. Find out when and where they’re airing!