Need formula for creating Heatmap log files

[sherpadude]

I'm using excel 2007 and I'm trying to create heat map of system utilization. This is the first step (I will pivot data later to create map) - at the moment I'm just trying to understand the formula I need.

I believe I'm coming at this from a very low tech approach that will require a pretty extensive conditional formula but perhaps this is the only way.

http://picasaweb.google.com/lh/photo/SAm8IvJTWh4T5Ost5ymBKw?feat=directlink

this above screen shot shows the formula. I basically trying to get the amount of minutes in each hour that a log entry takes

for instance if a event goes on from 1:10pm to 2:10pm I would want 50minutes to show up in the 1pm hour and 10minutes to show up in the 2pm hour..

thanks for any help pointing in the right direction...

 

[DonkeyOte]

Using your screen shot as an example:

K2: =MAX(0,MIN(K$1+"1:00",$F2)-MAX(K$1,$E2))
copied across matrix

Excel Workbook
	E	F	G	K	L	M	N	O	P	Q	R
1				00:00	01:00	02:00	03:00	04:00	05:00	06:00	07:00
2	04:37	04:51	0.009722					00:14
3	04:37	04:51	0.009722					00:14
4	04:37	04:51	0.009722					00:14
5	04:37	05:51	0.051389					00:23	00:51
6	03:23	07:34	0.174306				00:37	01:00	01:00	01:00	00:34
Sheet1

resulting values will be Time (Decimal) and should be formatted accordingly (in above time format of h:mm;; is applied)

 

[sherpadude]

WOW... simple, elegant, complex....
Thanks so much for that.. I owe a few solutions on this board as well - you should have seen the mess that I was creating! This saved me (my client) thousands of dollars in effort!

 

[sherpadude]

as the confetti came down and I applied the fix.. I realized I must have some formatting issues that inhibit the ease of application...

http://picasaweb.google.com/lh/photo/eHJicQePBe-VNmGNgjDybA?feat=directlink

When I evaluate the formula ...
http://picasaweb.google.com/lh/photo/02dtsP3-ZzVv36ooPpPU_g?feat=directlink

perhaps the fact that evaluates to fraction (excel time format) is the issue?

 

[DonkeyOte]

If you format E & F as General what are the resulting values.

 

[sherpadude]

no different (formatting general) but this variant works
=MAX(0,MIN(O1+"1:00",F2)-MAX(O1, "4:37")) - for O2 (the cell that should show a value for row 2...

apparently the formating E2 value is breaking it I guess.

 

[DonkeyOte]

My point was/is ... if you format E2:F2 as General and inspect the resulting numbers I suspect you will find you have an Integer & Decimal not just Decimal (ie they are datetime stamps not time)

If so you need to adjust references references such that you use the Time portion (decimal) only:

=MAX(0,MIN(K$1+"1:00",MOD($F2,1))-MAX(K$1,MOD($E2,1)))

This also raises the issue of whether or not the End Time crosses midnight from Start Time (ie Decimal of F is less than Decimal of E) ... if so the above won't work in those circumstances and will require further adjustment.

 

[sherpadude]

:-> great points, I was thinking about the midnight issue as well, you have certainly given me enough to work through I would never thought to approach it this way - My approach was much more convoluted - I'm going to see if I can solve with the base you have given me, thanks again!