hourly statistics from multitude of period info

K

konherr

New Member
Joined
Jul 17, 2011
Messages
3
Dear excel-experts,

I need your help here! Please have a look at the pic. I don't know much about array functions, but I think I need one here.

What I need is the following: I have individual non-availability values of power plants. Let's say plant 1 isn't operating from Feb 1, 2010 12AM until Feb 5, 2010 7PM. Plant 2 isn't operating from Feb 1, 2010 8AM until Feb 2, 2010 10AM... and so on (it's a lot of data)

How do I get all that information transformed into an "hourly unavailabilty schedule"? I don't need a count of plants that are non-available, but rather the sum of values associated with the unavailable plants! These should eventually appear in Column F

The pic should explain better than words.. I already tried an array function, but its not the correct one.

You would really be of great help to me! Thank you!

 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Here is the problem in excel format..
Excel Workbook
ABCDEF
1StartEndValuehourly 2010
22/1/10 12:00 AM3/1/10 1:00 AM100.01/8/10 8:00 PM#VALUE!
32/1/10 12:00 AM2/1/10 8:00 AM254.01/8/10 9:00 PM
42/1/10 8:56 PM2/2/10 7:43 AM366.01/8/10 10:00 PM
52/1/10 9:00 PM2/2/10 8:00 AM254.01/8/10 11:00 PM
62/1/10 9:11 PM2/2/10 8:00 AM355.01/9/10 12:00 AM
72/2/10 6:00 AM2/2/10 3:00 PM174.01/9/10 1:00 AM
82/2/10 9:05 AM2/2/10 6:44 PM153.01/9/10 2:00 AM
92/2/10 9:00 PM2/3/10 8:00 AM254.01/9/10 3:00 AM
102/2/10 9:36 PM2/3/10 7:42 AM366.01/9/10 4:00 AM
112/3/10 1:00 AM2/3/10 5:35 AM596.01/9/10 5:00 AM
122/3/10 1:09 AM2/4/10 3:00 PM190.01/9/10 6:00 AM
132/3/10 3:30 AM2/3/10 6:30 AM150.01/9/10 7:00 AM
142/3/10 7:00 AM2/3/10 5:00 PM216.01/9/10 8:00 AM
152/3/10 2:15 PM2/4/10 1:00 AM400.01/9/10 9:00 AM
162/3/10 8:13 PM2/4/10 6:35 AM355.01/9/10 10:00 AM
172/3/10 8:58 PM2/4/10 7:38 AM366.01/9/10 11:00 AM
182/3/10 9:00 PM2/4/10 8:00 AM254.01/9/10 12:00 PM
192/4/10 4:22 AM2/7/10 11:27 PM147.01/9/10 1:00 PM
202/4/10 8:35 AM2/4/10 11:00 AM119.01/9/10 2:00 PM
212/4/10 10:14 AM2/4/10 2:58 PM219.01/9/10 3:00 PM
222/4/10 3:00 PM2/4/10 5:44 PM227.01/9/10 4:00 PM
232/4/10 3:00 PM2/4/10 5:44 PM113.81/9/10 5:00 PM
242/4/10 4:44 PM4/4/10 12:02 PM113.51/9/10 6:00 PM
252/4/10 8:58 PM2/5/10 8:36 AM366.01/9/10 7:00 PM
262/4/10 9:00 PM2/5/10 8:45 AM355.01/9/10 8:00 PM
272/4/10 9:00 PM2/5/10 8:00 AM254.01/9/10 9:00 PM
282/5/10 12:00 AM2/7/10 12:00 AM419.81/9/10 10:00 PM
292/5/10 8:00 AM2/5/10 11:00 AM119.01/9/10 11:00 PM
2010
Excel 2007
Cell Formulas
RangeFormula
F2=SUMPRODUCT((--$A$2:$A$2562<=$E2)*(--$B$2:$B$2562>=$E2))
 
Upvote 0
Try this is...
Code: 
=SUMPRODUCT(--($A$2:$A$2562< E3),--($B$2:$B$2562>E2), $C$2:$C$2562)

Or this if you have Excel 2007 or later...
Code: 
=SUMIFS(C:C. A:A, "< "&E3, B:B, ">"&E2)

Note: don't include the space after the < sign in either formula.
 
Last edited:
Upvote 0
Alphafrog, superb! I looked at it for a while to figure out how you did it, but as I said, I don't know about those arrays! You have a sharp mind, especially the way you figured out to translate the relevant time frame into the array-formula!

Thank you very much, that saved me a good 12 hours of manipulating.. ;) I already did January by hand, and I can confirm that the values from your solution are fitting!
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Calculating avg. prices across specific hours of the year from a dataset using PivotTables?
Replies
0
Views
555
helnuma2k
H
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
A
  • Solved
Calendar issue
Replies
1
Views
2K
AndyEd
A
Alphacsulb
  • Question Question
Get array from filtered results
Replies
2
Views
2K
Akuini
Akuini
L
  • Question Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13

Forum statistics

Threads
1,224,581
Messages
6,179,668
Members
452,936
Latest member
anamikabhargaw

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top