Let's use this analogy for the FREQUENCY function.
We have penquins to ship.
We have boxes that hold various numbers of penguins.
For each penguin order, we need to reserve a box of the appropriate size.
Using your posted workbook and using Row_13 for our example...
each column number represents a number of penguins to ship
These are the values in columns D:M
We have orders for:
The first section of the FREQUENCY function: IF(D13:M13=1,COLUMN(D:M))
shows those numbers when evaluated: {FALSE,FALSE,6,7,8,FALSE,10,FALSE,FALSE,13}
The second section effectively tells us what box sizes we have to work with:
IF(D13:M13<>1,COLUMN(D:M))
resolves to: {4,5,FALSE,FALSE,FALSE,9,FALSE,11,12,FALSE}
So for box capacities, we have:
4
5
9
11
12
Over_12
If we don't have an exact match for a penguin order, we go to the next size up.
The FREQUENCY function tells us which box capacities are appropriate for each penguin order.
Then it calculates the count for each box capacity
These are the boxes required:
We want to know the box capacity we need the most of...
So...our B13 formula resolves this way:
Does that help?
Bookmarks