jediwa1ker
New Member
- Joined
- Jun 24, 2013
- Messages
- 4
Veh Type | Sun | Mon | Tues | Wed | Thu | Fri | Sat | Total |
1 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | |
2 | 1 | 1 | 2 | 2 | 2 | 2 | 2 | |
3 | 2 | 2 | 2 | 1 | 1 | 1 | 1 |
<tbody>
</tbody>
Veh Type | Sun | Mon | Tues | Wed | Thu | Fri | Sat | Total |
1 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | |
2 | 1 | 1 | 2 | 2 | 2 | 2 | 2 | |
3 | 2 | 2 | 2 | 1 | 1 | 1 | 1 |
A | B | C | D | E | F | G | H | I | J | K | L | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Veh Type | Sun | Mon | Tues | Wed | Thu | Fri | Sat | Total | 1 | 2 | 3 |
2 | 1 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 7 | 7 | 0 |
3 | 2 | 1 | 1 | 2 | 2 | 2 | 2 | 2 | 2 | 7 | 5 | 0 |
4 | 3 | 2 | 2 | 2 | 1 | 1 | 1 | 1 | 1 | 7 | 3 | 0 |
5 | 3 | 3 | 3 | 3 | 2 | 2 | 2 | 2 | 2 | 7 | 7 | 3 |
Worksheet Formulas
<thead> </thead><tbody> </tbody> |
Array Formulas
<thead> </thead><tbody> </tbody> Note: Do not try and enter the {} manually yourself |
try this, Cols J, K & L are helper columns
copy I2 down, J2 down and across
A B C D E F G H I J K L 1 Veh Type Sun Mon Tues Wed Thu Fri Sat Total 1 2 3 2 1 2 2 2 2 2 2 2 2 7 7 0 3 2 1 1 2 2 2 2 2 2 7 5 0 4 3 2 2 2 1 1 1 1 1 7 3 0 5 3 3 3 3 2 2 2 2 2 7 7 3
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>Sheet1
Worksheet Formulas
Cell Formula I2 =COUNTIF(J2:L2,">=5")
<thead>
</thead><tbody>
</tbody>
<tbody>
</tbody>
Array Formulas Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Cell Formula J2 {=MAX(FREQUENCY(IF($B2:$H2>=J$1,ROW($B2)),IF($B2:$H2>=J$1,ROW($B2))))}
<thead>
</thead><tbody>
</tbody>
Note: Do not try and enter the {} manually yourself
<tbody>
</tbody>
It gives 2 on my sheet (not 1) because 2 cars are in for 5 or more days in the week. I did not infer that the days had to be sequential.
after read the OP again, you may be right.
can the OP confirm or otherwise?
CSE formula (up to10 cars):
=MAX((MMULT(--(B2:H2>=ROW($1:$10)),ROW($1:$7)^0)>4)*ROW($1:$10))