Not quite an average...

jediwa1ker

New Member
 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....

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
try this, Cols J, K & L are helper columns
copy I2 down, J2 down and across

ABCDEFGHIJKL
1Veh TypeSunMonTuesWedThuFriSatTotal123
2122222222770
3211222222750
4322211111730
5333322222773

</tbody>
Sheet1

Worksheet Formulas
CellFormula
I2=COUNTIF(J2:L2,">=5")

</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
J2{=MAX(FREQUENCY(IF(\$B2:\$H2>=J\$1,ROW(\$B2)),IF(\$B2:\$H2>=J\$1,ROW(\$B2))))}

</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>

Last edited:
sorry, that didn't work

try this, Cols J, K & L are helper columns
copy I2 down, J2 down and across

ABCDEFGHIJKL
1Veh TypeSunMonTuesWedThuFriSatTotal123
2122222222770
3211222222750
4322211111730
5333322222773

</tbody>
Sheet1

Worksheet Formulas
CellFormula
I2=COUNTIF(J2:L2,">=5")

</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
J2{=MAX(FREQUENCY(IF(\$B2:\$H2>=J\$1,ROW(\$B2)),IF(\$B2:\$H2>=J\$1,ROW(\$B2))))}

</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>

ok, try this

Book1
ABCDEFGHIJKL
1Veh TypeSunMonTuesWedThuFriSatTotal123
2122212221730
3211222222750
4322211111730
5333322222773
Sheet1
Cell Formulas
RangeFormula
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.

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

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

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.

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?

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

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

Replies
4
Views
152
Replies
10
Views
319
Replies
6
Views
321
Replies
4
Views
152
Replies
6
Views
221

1,206,762
Messages
6,074,795
Members
446,089
Latest member
Andrew123456789

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back