bradgirman
New Member
- Joined
- Aug 28, 2023
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
Apologies that I can't use XL2BB, company security won't allow it.
I have an Excel sheet I've been working on and the last function I need I can't figure out. I have sizes in width x length which are typed into Columns B and D along with quantities in Col A.
The data is transformed by dividing the width by quantity then length by quantity with their results being placed into Columns E and F.
The sets are then transformed again in Columns H and I so the smaller size is always listed first and longer size second.
Next I have the new sizes calculated into Columns K and L by multiplying the result back to the quantity (it's redundant when that could be the first step but the values are necessary for other functions).
In N3 I have the Sum of quantities, with the sum of the widths and lengths in P3 and Q3.
P4 and Q4 are where the averages are, dividing the sum width in P3 by sum quantity in N3 and respectively sum length of Q3 by sum quantity in N3.
The function I'm trying to work out is how to get the Average Width in P4 as close to the number 60 as possible without going over, swapping the values of width/length per row until the closest average of 60 is met. I've tried combinations of MAX with IF conditions but that didn't work out.
Please help with ideas!
The sheet in question:
Columns A:I
Columns J:Q
I have an Excel sheet I've been working on and the last function I need I can't figure out. I have sizes in width x length which are typed into Columns B and D along with quantities in Col A.
The data is transformed by dividing the width by quantity then length by quantity with their results being placed into Columns E and F.
The sets are then transformed again in Columns H and I so the smaller size is always listed first and longer size second.
Next I have the new sizes calculated into Columns K and L by multiplying the result back to the quantity (it's redundant when that could be the first step but the values are necessary for other functions).
In N3 I have the Sum of quantities, with the sum of the widths and lengths in P3 and Q3.
P4 and Q4 are where the averages are, dividing the sum width in P3 by sum quantity in N3 and respectively sum length of Q3 by sum quantity in N3.
The function I'm trying to work out is how to get the Average Width in P4 as close to the number 60 as possible without going over, swapping the values of width/length per row until the closest average of 60 is met. I've tried combinations of MAX with IF conditions but that didn't work out.
Please help with ideas!
The sheet in question:
Columns A:I
Columns J:Q
