PDA

View Full Version : First Factor Formula



bjoshi
06-30-2013, 12:38 AM
Hi,

I need help with a formula, but I don't even know if its possible.

Lets say I have the following data in my worksheet (A1,A2,A3,etc are cell references)

A1 2
A2 3
A4 4
A5 5
A6 6

In cell B1, I type in a random number, lets say 6,
Then in cell C1, i want a formula such that it gives me the 'FIRST FACTOR' of B1 from the array of values in column A.

So here the outcome of the formula would be 2 since (2*3=6),
if I had B1=9 then the outcome would be 3 since (3*3),
if I had B1=12 then the outcome would be 2 since (2*6).

I hope I have explained myself clearly enough.

Regards,
bjoshi

patel
06-30-2013, 01:03 AM
can you accept a macro ?
Sub a()
LR = Cells(Rows.Count, "A").End(xlUp).Row
For j = 1 To LR
f = Range("B1").Value / Cells(j, 1).Value
If Int(f) = f Then
Range("C1") = Cells(j, 1)
Exit For
End If
Next
End Sub

p45cal
06-30-2013, 02:06 AM
The first one that I got to seem to work, I'm sure it could be shorter: in C1:
=INDEX(A1:A5,SMALL(IF(B1/A1:A5=INT(B1/A1:A5),ROW(A1:A5)),1))
Note that this is an array-entered formula which means that instead of committing it to the sheet after editing by just pressing the Enter key, you need to press Ctrl+Shift+Enter. Curly brackets should appear automatically around the formula if you do this correctly. No attention has been paid to absolute/relative references in this formula. It would need a tweak if the factors in column A didn't start at row 1 of the sheet.

snb
06-30-2013, 06:50 AM
@patel

in VBA \ can be applied:

7\3=2
7/3=2,333

bjoshi
06-30-2013, 07:14 AM
Thanks patel and snb, but I'm looking for a formula to do this. Thanks though.

p45cal, The formula works exactly, as I wanted it to. Thanks for that !!!

patel
06-30-2013, 08:07 AM
@patel

in VBA \ can be applied:

7\3=2
7/3=2,333
thank you snb, I did not know this operator

shrivallabha
06-30-2013, 08:25 AM
Shorter would not mean necessarily better but this seems to work. Needs to be entered as CSE.
=INDEX(A1:A5,MATCH(0,MOD(B1,A1:A5),0))