Hourly data to daily
Hello,
I have 3 engines that I can export operating data into .csv format. I have been trying for weeks with no luck to easly and repeatedly change the hourly data into one daily row. I have attached a copy of the data that I download from a unit that has been changed to .xlsx.
As you can the the first column is the date and time. Each compressor day starts at 7:00am instead of midnight. Which means the daily average needs to run from 7:00am one day to 6:00am the next, Column B is the running total engine hours. On Column C I will need the max for the day, and then copy the corrisponding data beside it, and move all of this information onto another sheet to be listed as so:
Date | Daily Hours | Discharge Press | Discharge Temp | Suction Press | Suction Temp |
Please explaine how the formula's work when you answer. I really am trying to learn Excel.
This seems like it should be so easy, but I am about ready to pull my hair out over it. Thank you many times over to whomever can help me.
I am using Excel 2007
In Column I,
1. Format I as date
2. In I2, =MID(A2,3,200)+0 dragged down
Your values in Column A are not being recognized as dates because of the first two characters :space & period. The mid formula says to start with the 3rd character. This is returned as Text, so the +0 tells Excel to convert to a number.
Last edited by ChemistB; 02-24-2011 at 02:19 PM.
ChemistB
My 2¢
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Then try your hand at a pivot table. Choose the new date column (make sure you put a header) as a Row and SUM of whichever values you want in the Values field. Then right click on one of the dates in the pivot table and Group as day.
Does that work for you?
ChemistB
My 2¢
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Okay let me give this a shot.
Correct: with pivot table group by Year and Day.
ChemistB
My 2¢
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Two problems. I can't seem to give a daily run hours as 24 or less, and It looks as if I need to take the max discharge pressure, and then use all the corrisponding data to fill in the rest of the table. Say the max discharge was on row 15 for that day. I need to pull the entire row 15 to fill in the rest.
So what you want, is for each day, to find the max discharge and bring across that row to represent that day?
ChemistB
My 2¢
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Yes thats correct.
On many dates, you have multiple occurances of a single value as a maximum discharge pressure. i.e. on 12/16/2009, 0.962 repeats. Do you want the first occurance?
ChemistB
My 2¢
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
yes that would be great!
Okay, here's what I did. On your initial data sheet, in I2
=INT(MID(A2,3,200)+0) give you dates only.
In Column J, J2 is
=I2&C2 this is a key to link date and discharge pressure. (you can hide it if you like)
Then I want a list of unique dates on a new sheet (I called it "Max Table").
1. Copy Column I in Data sheet to Max Table Col A. Paste Special > Values and format as date.
2. In Excel 2007, on Ribbon Data Tab> Remove Duplicates to leave unique dates.
I copied the header row over to Max Table replacing TimeStamp with Date.
Getting Max Discharge Pressure for each Date
In C2 of Max Table, you need an array formula
=MAX(IF('3f131_logs_hour'!$I$2:$I$10242=A2, '3f131_logs_hour'!$C$2:$C$10242,""))
To make this an array formula, use CNTRL SHFT ENTER instead of a simple ENTER. You'll see brackets {} appear around your formula. Drag down.
This formula looks for the max discharge matching the date in column A
Pulling in other data
In B2
=INDEX('3f131_logs_hour'!$B$2:$H$10242,MATCH('Max Table'!$A2&'Max Table'!$C2,'3f131_logs_hour'!$J$2:$J$10242,0),MATCH('Max Table'!B$1,'3f131_logs_hour'!$B$1:$H$1,0))
Copied to Columns D,E, F... and dragged down.
The first MATCH in this table finds the proper row using the key column J we created and the second match finds the proper Header to match the columns.
Is this what you want? (See attachment)
ChemistB
My 2¢
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Everything is perfect except the engine hours. They need to be represented in hours per day. ≤ 24
So what would the engine hours for 12/10/2009 be? Not sure what you are looking for.
ChemistB
My 2¢
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
24 hours. I tried using the max table sheet and just subtracting two days worth of data to get the hours for the previous day. Then I realized that wouldn't work because the max discharge occurs at diffrent times every day, and since it took the engine hours and moved them to that sheet right alog with everything else I could no longer subtract the two. The database that this information will move into once we get this sheet worked out only recognizes hours operated per day. This could be any number from 0 to 24. Also, for some reason they want the days to start at 7:00am instead of midnight. This really isn't that relavent to me since the data is a month old once it enters the database anyway. So either of the two would work. For example if the engine was running from 7:00am on the 10th until 7:00am on the 11th that would be considered 24 hours run time for the 10th.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks