Can there be a single formula (maybe an array formula) which can indicate whether the number in F1 is divisible by any of the numbers in the cells (A1:D9)?

Regards.

Something like:

=MIN(MOD(F1, A1:D9))

will be 0 if any of the numbers is a factor.

Try the following small user defined function:

Public Function IsDivisible(rng As Range, v As Long) As Boolean
 Dim r As Range
 IsDivisible = False

 For Each r In rng
 If v Mod r.Value = 0 Then
 IsDivisible = True
 Exit Function
 End If
 Next r
End Function

For example:

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window


2. ALT-I
   ALT-M opens a fresh module


3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above


2. clear the code out


3. close the VBE window

To use the UDF from Excel:

=myfunction(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

SUMPRODUCT 'Deals' in Arrays

Again, credits to Tim Williams for his brilliant response and for simply 'forcing' me to investigate this matter further.

Formulas

You can use this formula:

=IF(SUMPRODUCT(--(MOD(F$1,$A$1:$D$9)=0)),TRUE,FALSE)

which derived from the formula that counts the number of occurrences of zero remainders:

=SUMPRODUCT(--(MOD(F$1,$A$1:$D$9)=0))

which brings us to the reason why I would investigate further.

I wanted to exclude 1 and the value in F1, 81, from the occurrences count i.e. if the only zero occurrence is number 1 or 81, a formula would show FALSE, which the following two formulas do:

=SUMPRODUCT(--(MOD(F$1,$A$1:$D$9)=0),--($A$1:$D$9>1),--($A$1:$D$9<$F$1))

which counts the number of occurrences of zero remainders after dividing the value in F1 with each value in range A1:D9, but not counting if 1 or the value in F1 where being divided and

=IF(SUMPRODUCT(--(MOD(F$1,$A$1:$D$9)=0),--($A$1:$D$9>1),--($A$1:$D$9<$F$1)),TRUE,FALSE)

which returns TRUE if such an occurrence is found, or FALSE if not.

Workbook

Workbook Download (Dropbox)

So I played 'a little' in a worksheet to learn a lot.