Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Please
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Please
On Sun, 14 Feb 2010 15:19:02 -0800, Spikey
wrote: 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? =LOOKUP(1E+307,'Meters-10'!F:F,'Meters-10'!F:F) --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Please
"Spikey" wrote:
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 The following trick might work with some other changes, described below. =lookup(1E307,'Meters-10'!F10:F61) This should work as long as F17:F61, for example, appear blank if the units for the most-recent week is in F16. So.... 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. Change the formula to: =if(E11="","",E11-E10) This assumes that column E is blank until you fill in the reading for that week. If the null strings ("") in column F cause problems in other formulas, that is usually easy to fix, either with an IF() expression like the one above or by using the N() function -- for example, N(E11). Note: The null strings in column F should not cause problems for the SUM() expression that you use to compute the 52-week (YTD) total because the SUM function ignores cells with strings, among others. ----- original message ----- "Spikey" wrote: 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Please
In D10: =LOOKUP(2,1/('Meters 10'!F2:F100<""),'Meters 10'!F2:F100)
voila? hit the YES below -- Max Singapore --- "Spikey" wrote: 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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Please
Sorry, I missed the hyphen in your sheetname earlier
It should read, in D10: =LOOKUP(2,1/('Meters-10'!F2:F100<""),'Meters-10'!F2:F100) -- Max Singapore --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Please
=OFFSET(StartE,MATCH(MAX(colE),colE)-10,1)
solved. OP looking for value in F based on max in col e where starte is e10 -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "Spikey" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|