List to array
 

Sample data

Hour logged on Total duty time hours
0 7.5
0 1.5
0 8.5
1 6.5
2 7.5
2 8.5
3 7.5
3 6.5
....and so on to hour 23

I want a table out that shows total person hours on duty per hour

i.e. 0 3
1 3.5
2 5
3 7

to 23

There are 28 of these tables and I keep getting close then losing it ...help
someone please?

best regards Tom




----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---

Hi,

I assume that "Hour logged on" is in A1, so the values go down from
A2 and "Total duty time hours" in B1 with values under that. You can do
the summary like this:

- Put somewhere, let's say starting from D2 (headers are at row 1),
values from 0 to 23
- In E2 put this array formula:

=SUM(($D2=A$2:A$9)*(IF(B$2:B$9+A$2:A$9-$D2<0,0,IF(B$2:B$9+A$2:A$9-$D21,1,B$2:B$9+A$2:A$9-$D2))))

I know, it looks quite nasty, but I couldn't use MIN and MAX functions,
because they don't work in this case. And by array formula I mean, that
instead pressing enter after entering this formula, you should press
shift+ctrl+enter.

- Now copy this formula down all the way to the cell E25.

Now you should have the values you are looking for.

And in a real case, where you have a lot more of those logged on hours,
change the references (A$2:A$9 and B$2:B$9) so that they cover all the
data area.

- Asser


--
Jazzer
------------------------------------------------------------------------
Jazzer's Profile: http://www.excelforum.com/member.php...fo&userid=4464
View this thread: http://www.excelforum.com/showthread...hreadid=277647