Formula to look for multiples of a number in a range

**Beantown** · 01-07-2013, 10:44 PM

If I have a range of numbers, say A1 is 3, B1 is 6, C1 is 9...multiples of 3. Is it possible to figure out if there were any multiples of 4 that fell into those categories?
For Example, A1, answer would be no, as the range of 1-3 doesn't have any multiples of 4, yet B2 should be yes, as it includes 4, 5, 6. So should C1, as it contains an 7,8,9.
What formula can I use in Excel to figure that out?
I am thinking of something simple, where you can just point to "above" or to the range of numbers from A1...X1, and the row below (A2, B2...) would tell me if the above number contained a multiple of 4 or not.
Thank you for the help.

**benishiryo** · 01-07-2013, 10:56 PM

hi Beantown, welcome to the forum. you can try:
=MOD(A1,4)<>A1

MOD will give the remainder of the number after dividing by the divisor "4". since 3 cannot be divided by 4, it returns 3 again. and i put a statement behind that solution is not equals to A1. so "3" is not equals to 3 is FALSE. whereas B1 & C1 will get a MOD result of 2 (remainder after dividing by 4) & 1 respectively, & thus not equals to B1 & C1. so when it's TRUE, you can say that it has multiples of 4.

you can further expand if you want the text "yes" or "no"
=IF(MOD(A1,4)<>A1,"yes","no")

**Beantown** · 01-07-2013, 11:15 PM

Thanks for your reply. Much appreciated.
For some reason, it doesn't work...here is what I get using both formulas provided above: - use C1, instead of A1 to show the formula.
3 6 9 12 15 18 21 24
=MOD(C1,4)<>C1 FALSE TRUE TRUE TRUE TRUE TRUE TRUE TRUE
=IF(MOD(C1,4)<>C1,"yes","no") no yes yes yes yes yes yes yes

If it were true, then for the range of numbers from 12-15, it should be "FALSE" or "no" as that range only contains 13, 14, 15.

Thank you.

**dredwolf** · 01-08-2013, 01:01 AM

insert a row at 1, then try this in whatever column,row 2 you need the answer:

Formula:

Please Login or Register  to view this content.

Drag down

Hope this helps

**benishiryo** · 01-08-2013, 10:11 AM

so if what i understand is correct, try this array formula:
=IF(MIN(MOD(ROW(INDIRECT(C1-2&":"&C1)),4)),"no","yes")

you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER