List to array

[tom borg]

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

 

[Jazzer]

Hi,

I assume that "Hour logged on" is in A1, so the values go down fro
A2 and "Total duty time hours" in B1 with values under that. You can d
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-$D2>1,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, tha
instead pressing enter after entering this formula, you should pres
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 th
data area.

- Asse