Not quite an average...

[jediwa1ker]

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>

Can anyone advise on how I could automate the below calc, we do manually at the moment but there is probably and easy formula I am overlooking.... we have a scenario where I can charge for a parking space if the space is used for 5 or more days in the week, so from the table above for Veh type 1 I would charge 2 spaces, veh type 2, 2 spaces and veh type 3, 1 space....

 

[AlanY]

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 Cell Formula J2 {=MAX(FREQUENCY(IF($B2:$H2>=J$1,ROW($B2)),IF($B2:$H2>=J$1,ROW($B2))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[AlanY]

sorry, that didn't work

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 Cell Formula J2 {=MAX(FREQUENCY(IF($B2:$H2>=J$1,ROW($B2)),IF($B2:$H2>=J$1,ROW($B2))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[AlanY]

ok, try this

Book1
	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	1	2	2	2	1	7	3	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
Sheet1

Cell Formulas
Range	Formula
I2	=COUNTIF(J2:L2,">=5")
J2	{=MAX(FREQUENCY(IF($B2:$H2>=J$1,COLUMN($B2:$H2)),IF($B2:$H2$B2:$H2))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mopp1]

CSE formula (up to10 cars):
=MAX((MMULT(--(B2:H2>=ROW($1:$10)),ROW($1:$7)^0)>4)*ROW($1:$10))

 

[AlanY]

CSE formula (up to10 cars):
=MAX((MMULT(--(B2:H2>=ROW($1:$10)),ROW($1:$7)^0)>4)*ROW($1:$10))

it didn't work for Veh Type 1 in post #4 above

 

[mopp1]

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.

 

[AlanY]

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?

 

[jediwa1ker]

after read the OP again, you may be right.
can the OP confirm or otherwise?

Days do not have to be sequential, it can be any 5 days in a week thanks for your help

 

[jediwa1ker]

CSE formula (up to10 cars):
=MAX((MMULT(--(B2:H2>=ROW($1:$10)),ROW($1:$7)^0)>4)*ROW($1:$10))

I cant get this to work, I assume I am doing something wrong - which cell are you entering the data into? Do I need any helper data? Will this calc vehicle type by row by row? TIA