Hi folks.
I have base Hourly data for production of a unit. I want to analyse it accroding to Year, Month, Summer( june-Sep), Non Summer( Rest of year), Peak( Weekdays: Hours 08-23) offPeak( Weekdays:00-07 and Weekends 08-23 and 00-07: the difference being that production within offpeak weekend hours varies and would need to analyse data for the two buckets of OffPeak).
The data comes in this format:
Date Production
1/3/2000 8:00 200
and so on.
For breaking up into years, i have used the Year(Cell Ref) function. For Breaking it into Month, i have used the formula: Vlookup( month(CellRef), DataSet1,2,False) where dataset is in the format:
Month Number Month Name Season
1 Jan non Summer
and so on
Did the same thing for Summer/Non Summer Differenciation. the formula is Vlookup(Month(CellRef),Dataset1,3,False)
For Hours I used the formula:
IF(OR(VLOOKUP(INT(Basedata cellreference),Dataset2,2,FALSE)="Sat",VLOOKUP(INT(Basedata cellreference),Dataset2,2,FALSE)="sun")=TRUE,IF(AND(HOUR($B57)>7,HOUR($B57)<=23)=TRUE,"2x16","7x8"),IF(AND(HOUR($B57)>7,HOUR($B57)<=23)=TRUE,"5x16","7x8"))
DataSet 2 is like this
Base data Day
Int(Basedata) Sat
and so on
Finally, the breakup into Peak offpeak is by using the IF logic.
Now i have 65000 Rows base data for each machine( there are 4 machines)
also, DataSet2 is 2900 Rows, Dataset1 is small( 12 rows corresponding to each month)
As you can see, given the amount of data, the Vlookup function becomes too time consuming( not to mention xcel crashes).
Can you suggest a more elegant solution. Remember, I need to have all analysis( Peak, offpeak, summer, non summer, year, 5x16, 7x8, 2x16) on hourly basis.
Thanks!
Kanwaljeet