Calculate formulas/UDFs from input and outputs

This is almost certainly the most unusual question I have ever posted (or ever will post) on this forum.

I have known inputs and (sorta) known outputs and I need Excel formulas (or better still - UDFs) to calculate the outputs.

First picture a user form that we want to place X number of buttons on it (it doesn't matter if you are not familiar with forms - picture cardboard boxes instead). The buttons/boxes are all the same size and will be in alignment with each other. They will be laid out in the shape of a rectangle. I need to calculate 1. the number of rows and 2. the number of columns for any number of buttons/boxes.

Now the tricky bit - there are two goals we need to meet when calculating the number of rows/columns:

1. Try to keep the number of boxes on the last row as close as possible to the total no. of columns - in other words, keep gaps to a minumum. I can't have a last row with only one box/button on it.
2. Try to keep the rows/column ratio to a visually pleasing ratio (aiming between 1.2 to 1.7. This is not as critical as the previous goal)

It might be easier to work out what I am after by referring to the attached workbook.

So can this be expressed as an Excel formula? If you prefer to calculate this using VBA, a UDF would be great

So what is your expected result and where you shown in the excel file?

Originally Posted by :) Sixthsense :)

So what is your expected result and where you shown in the excel file?

From input (Column A), I want to return/output two results (Column E & F).

I use Columns H to P to test what outputs I need.

To give an example:

Take input of 40. I am hoping for an output result of 8 rows by 5 columns.
If I had outputs of 7x6, I would have a total of 42. This result would not be as desirable as 8x5 because 7x6 would have two gaps (42-40=2)
If I had outputs of 10x4, I would have a total of 40 (no gaps). However this is not as desirable as 8x5 because the ratio of the 10x4 rectangle would be 2.5 (10/4=2.5) (8/5=1.6 which is in the ratio I am aiming for)

I believe that there is an area of mathematics which specialises in this. Calculate an area to hold a known quantity with minimal mumber of gaps/space left over.

Now I can understand your logic clearly and I can’t able to achieve this without using VBA… Is it ok for you? Because for finding the nearest match I need to use multiplication table via vba and even the VBA can be avoided if I have the table in excel…

What's the objective function to maximize?

If 34 boxes were needed, how would they be arranged? What's the relative figure of merit between skinny filled rectangles and square-ish partially-populated rectangles?

I thought I can make a multiplication table and compare the values using Vlookup with Approximate & Exact Match… But I don’t have much time to do that… So anyone I just wanted to share what I arrived so far…

The below will create multiplication table with ratio and sort the data using the values…

Sub CreateMultiplicationTableWithRatio()
Dim i As Long, x As Long, nTemp As Long, vTamp As Variant

Sheets.Add
nTemp = 1

Range("A1:F1").Value = Array("DataToMatch", "Row", "Column", "Table", "Values", "Ratio")

For i = 2 To 100
    For x = 2 To 100
        nTemp = nTemp + 1: vtemp = 0
        If (Int(i / x) - (i / x)) Then vtemp = Int((i / x) * 1000)
        Cells(nTemp, "A").Value = (i * x) & "." & vtemp
        Cells(nTemp, "B").Value = i
        Cells(nTemp, "C").Value = x
        Cells(nTemp, "D").Value = i & " x " & x 'Table
        Cells(nTemp, "E").Value = i * x
        Cells(nTemp, "F").Value = (i / x) 'Ratio
    Next x
Next i

Range("A1").CurrentRegion.Sort Key1:=Range("A2"), Order1:=xlAscending, _
    Key2:=Range("b2"), Order2:=xlDescending, Key3:=Range("C2"), Order3:=xlAscending, _
    Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
    xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
    DataOption3:=xlSortNormal

End Sub

Originally Posted by shg

What's the objective function to maximize?

If 34 boxes were needed, how would they be arranged? What's the relative figure of merit between skinny filled rectangles and square-ish partially-populated rectangles?

Very good questions.

34 boxes is 7 rows by 5 columns (refer row 26 on uploaded workbook).

The objectives are:

To display buttons on a userform so that the number of macro buttons passed will fill the total columns and total rows as neatly as possible (i.e. keeping number of gaps on the last row to a minimum).

At the same time, the user form shouldn't be difficult to read. By that I mean that the form shouldn't be stretched too far vertically or horizontally - so I am aiming for a optimum ratio of between ~1.2 to ~1.7 on row number to column number. However if the ratio would have to exceed these limits in order to prevent having 1 button on the last row (see my comment in cell P13) then so be it.

This is not easy for me to explain, which is why I uploaded the workbook to show examples of the rows & columns I am aiming for (column E & F) and why (column P and conditional formatting in columns H-N)

I guess the processing priority is as follows:

1. Avoid having only 1 button on the last row
2. Try to fill the grid as closely as possible/keep gaps to a minimum
3. Aim for a ratio of between ~1.2 to ~1.7 on rows to columns

Goal 3 is more of a guide to what to choose after goals 1 & 2 are completed. For example an input of 24. This can perfectly divide into 12:2 or 8:3 or 6:4. Yet 6:4 is the only one of these three in the desired ratio range. So the functions should return 6 rows and 4 columns.

Having said that: Goal 3 can sometimes take priority over Goal 2; If all results for an input return say 2-4 gaps each (all results almost as bad as each other) then aim for the result which returns the best ratio.

I don't know if this will help or hinder solving this thread.

To see the bigger picture of what I am trying to achieve here with button/box layouts, please see this thread http://www.excelforum.com/excel-prog...t-runtime.html