Multiple MAX values from different columns and rows

**whirlwind1** · 04-07-2011, 09:56 AM

Hi,

I'm after some help with a job I'm trying to do. I'm trying to work out the maximum number of boxes I can fit onto a standard sized pallet but I'm having some issues with my results. See the attached XLS file for a mini example of what I already have.

The last column (max) just asks what the maximum number in the column is. As you can see the forumla is picking up on the fact that the largest number in all cases is in the first column.

What I need it to do is to tell me the largest value in each column and row but not if it has already picked from the row or column. So the result I would like in this case would be 14.21, 1.90 & 0.83.

I've been staring at this for days and I'm hoping someone out there can stop me staring!! I can add as many columns or rows as needed as I have loads of room left!

Thanks in advance for any helpers.

**whirlwind1** · 04-08-2011, 11:11 AM

Bump - no response

Do I need to be giving out more information or is what I want to do not possible?

Thanks

**darkyam** · 04-08-2011, 11:54 AM

Not sure I follow completely. For my clarification, do you want the biggest overall number to be chosen first, then the biggest number in any row or column except for the first number, and then the remaining number (since there will always be only one possibility left for it with a 3x3 grid)? Also, is it correct to assume the grid will always be 3x3?

**whirlwind1** · 04-08-2011, 11:57 AM

Hi darkyam,

That is a much more concise way of putting what I was trying to say - Yes that is exactly what I am after and yes the grid will always be 3x3.

Thanks

**darkyam** · 04-08-2011, 12:29 PM

It's not pretty and maybe someone can come up with a cleaner way to do it, but this seems to work for me. HTH.

**whirlwind1** · 04-11-2011, 11:33 AM

Darkyam - you are awesome!! I'm not bothered that it isn't pretty - it does the job. A couple of tweaks to get rid of decimal points which I forgot to mention but I would never come up with the formula that you did. THANK YOU!