Multiple of a perticular number

[Lazaeb]

Hi,
I want multiple of a number.
For example i have to give a free gift when any one purchases 18 units. Now what i want is when "A" cell is 18 it should show "1" in B cell and when "A" cell is 36 "B" Cell should be "2".

 

[alansidman]

Assuming data in column A starts in row 1. Place this in B1 and copy down---> =IF(MOD(A1,18)=0,A1/18,"")

 

[jtakw]

Hi,

I think this should work:

Book1
	A	B
1	15	0
2	18	1
3	30	1
4	36	2
5	48	2
6	53	2
7	60	3
Sheet170

Cell Formulas
Range	Formula
B1	=INT(A1/18)

 

[Lazaeb]

In this way if number is not a multiple then it will show 0

 

[alansidman]

Have you tried my solution. It will only show a result when divisible by 18

 

[Lazaeb]

Yes i tried but it gives zero when it is not divisable by 18.
My point is when it is 18 it shows 1 but when it is 20 it should remain 1 instead of 0. Likewise when it is 72 it should show 4 and when it is 74 it should show 4 instead of 0

 

[Joe4]

My point is when it is 18 it shows 1 but when it is 20 it should remain 1 instead of 0. Likewise when it is 72 it should show 4 and when it is 74 it should show 4 instead of 0

Have you tried jtakw's solution?
It does precisely that. It divides the numbers and drops the fractional part, so you can just left with the Integer portion (which based on your description, is what you want to happen).

 

[Lazaeb]

Yes that works. Thanks jtakw's for the awesome solution.

 

[jtakw]

You're welcome, welcome to the forum.

 

[alansidman]

In B1, type 0, then in B2 type =IF(MOD(A2,18)=0,A2/18,OFFSET(A2,-1,1)) and copy down.