Forum Home
Press F1
Thread ID: 93471	2008-09-18 02:04:00	Some Excel formula help required....	NZMacka (11756)	Press F1

Post ID	Timestamp	Content	User
706060	2008-09-18 02:04:00	Gidday guys, I am a sparky by trade, and have been working on a home electricity montoring project for the last month or so. The hardware sid eof things is working well, and I now have my electrical kWh data feeding into Excel every minute. I end up with a Excel spreadsheet looking like this (updating with a new row of external data every minute: 12:03:04 09/18/2008 25641 12:04:04 09/18/2008 25647 12:05:04 09/18/2008 25653 12:06:04 09/18/2008 25658 12:07:04 09/18/2008 25664 12:08:04 09/18/2008 25670 Here is where I start to get a bit stuck as my Excel skills are very limited! What I would like to do is get excel to check the value of the third column every 60 minutes to establish hourly kW usage. How do I acheive this? I am sure it is a simple solution, but I can't work it out! I assume that I need to somehow tie the timestamp in the A column with the counter value in the C column... Once I have the hourly formula working, I would then obviously like to extrapolate this out to include a daily, weekly and monthly usage. So I need to somehow get excel to check 24 hours of data and report that figure. If anyone can point me in the right direction here, that woud be great!:banana Cheers Macca	NZMacka (11756)
706061	2008-09-18 05:24:00	Gidday guys, I am a sparky by trade, and have been working on a home electricity montoring project for the last month or so . The hardware sid eof things is working well, and I now have my electrical kWh data feeding into Excel every minute . I end up with a Excel spreadsheet looking like this (updating with a new row of external data every minute: 12:03:04 09/18/2008 25641 12:04:04 09/18/2008 25647 12:05:04 09/18/2008 25653 12:06:04 09/18/2008 25658 12:07:04 09/18/2008 25664 12:08:04 09/18/2008 25670 Here is where I start to get a bit stuck as my Excel skills are very limited! What I would like to do is get excel to check the value of the third column every 60 minutes to establish hourly kW usage . How do I acheive this? I am sure it is a simple solution, but I can't work it out! I assume that I need to somehow tie the timestamp in the A column with the counter value in the C column . . . Once I have the hourly formula working, I would then obviously like to extrapolate this out to include a daily, weekly and monthly usage . So I need to somehow get excel to check 24 hours of data and report that figure . If anyone can point me in the right direction here, that woud be great!:banana Cheers Macca Easiest way to do this Macca would be to use a pivot table - if you drop dates/times into a row field, you can group them into seconds, minutes, hours, days, months, quarters & years . Let me know if you need more help - or PM an excel file with your data, and I'll show you how it's done . HTH :thumbs:	nofam (9009)
706062	2008-09-18 05:58:00	Hey, thanks heaps for the response Nofam...standby for a PM later on tonite (once I have the kids in bed!) Cheers mate, appreciate your time. Andrew	NZMacka (11756)
706063	2008-09-19 01:42:00	If as you say your data is coming in with one line for every minute of every hour of every day, then the simplest method is that the sum of every block of 60 rows is one hour, every 1440 rows is one day and every 10080 rows is one week etc	rad_s4 (7401)
706064	2008-09-19 10:20:00	What about creating a chart, maybe easier to see variations	Ofthesea (14129)
706065	2008-09-19 10:29:00	What about creating a chart, maybe easier to see variations Possibly may be easier to see. But then to create the chart you need to have correct data input. Only then can you plot variations.	Sweep (90)
1