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