Division problem

[hejs]

Hi! I am new with Excel. Could you please help me with this?

I have 6 numbers. I would like to divide all 6 with the other 5. Not with themselves. That is 30 numbers then.

Then i would also like to divide those 30 ratios with the other 29.


Can this be done all at once?


Thanks for your help!

[AlfD]

Hi!

Not sure what form you want the answers to take, but the following VBA routine will generate the answers:

Sub DivDiv()

k = 1
For i = 1 To 6
For j = 1 To 6
If i <> j Then
Range("B" & k) = Range("A" & i) / Range("A" & j)
k = k + 1
End If
Next j
Next i

m = 1
For i = 1 To 30
For j = 1 To 30
If i <> j Then

Range("C" & m) = Range("B" & i) / Range("B" & j)
m = m + 1
End If
Next j
Next i
End Sub

Simply: the 6 initial values are in A1 to A6.

Each division generates an entry in column B (excluding A1/A1 etc).

Now we do just the same thing with B1 - B30 generating values in C1-C870.

Come back if you need help on inserting the VBA routine.

Alf

I

[hejs]

Many thanks!!

I am now in the Visual basic editor. have copied the formula in there so it looks just like your wrote.

Then i have no idea what to do. This formula works for all versions? I have the 97.

[AlfD]

Hi!

Now to trigger the sub.

A simple way is to use a command button. Don't be put off by the number of my words. The actions are straightforward.

Locate the Control Toolbox toolbar (View > Toolbars .. and ensure that Control Toolbox is checked).
Ensure your active (visible) worksheet is the one with the numbers on it and the code behind it.

Hover your mouse over the icons on this toolbar and locate the one with the tooltip Command Button. Click on this icon and then use the cursor to draw a modest rectangle on the worksheet.
Double click this new button (which will have "handles" on it at this stage) and you will be taken to the code page you made earlier and your cursor will be in a new sub thus:

Private Sub CommandButton1_Click()

End Sub

In this sub, type divdiv

Now return to the worksheet (use the Excel tab in the taskbar, for example).

Ensure that the command button is active (the first Control Box toolbar icon toggles between Design Mode (which we don't now want) and Exit Design Mode (which we do want). No more handles on the button? OK. Click it.

Alf

[hejs]

Ooooh maaan. Thank you very much!! :0)


Do you know if it is possible to see from where all ratios come from?
From what numbers in column A.


Many thanks!!

[AlfD]

Hi!

Sounds like progress.

The ratios are done sequentially from the top.
So the result in B1 is A2/A1; B2 has A3/A1 etc. for 5 cells.
Then B6 has A1/A2; B7 has A3/A2 etc.

The C column is similarly structured with B replacing A and C replacing B, with 30 ratios to a block.

Alf

[hejs]

Many thanks Alf