need a conditional formula to generate numbers divisible by 4 between a given starting no. & end No.

[ramana]

Hi everybody,


I need a conditional formula that returns the values(numbers)
divisible by 4 and also that gives the strating value in the first cell
and ending value in the last cell irrespective whether the first & last
numbers are divisible by 4.

Here I'm giving two example for better understanding.

Ex1: Cell A1 = 1(starting number)
Cell B1 = 49(ending number)
Now I want apply formula in column C so that Cell C1 should return a
value 1(starting number),i.e. c1=1,
C2=4,C3=8,c4=12,c5=16,c6=20,c7=24,c8=28,c9=32,c10=36,c11=40,c12=44,c13=48
& c14=49(ending number).

Ex2: cell A1 = 199
Cell b1 = 345
Then the result should be C1=199(starting
number),c2=200,c3=204,c4=208,c5=212,c6=216...divisibles of 4.....then
the last cell = 345(ending number).

Hope that the question & examples are clear.

Pl. can any body help me to write a conditional formula for this
problem.

Regards

Ramana

 

[Rowan Drummond]

In C1 enter:

=IF(ROWS($1:1)=1,$A$1,IF(FLOOR($A$1+((ROWS($1:1)-1)*4),4)<$B$1,FLOOR($A$1+((ROWS($1:1)-1)*4),4),IF(FLOOR($A$1+((ROWS($1:1)-1)*4),4)<$B$1+4,$B$1,"")))

and copy down as required.

Hope this helps
Rwoan

 

[ramana]

Hi Rowan,

Thanks for the solution you have given. Its working partially. when
copy down the formula to the other cells it is displaying the starting
number in all the cells. but when I save the document then it is
displaying the required numbers. But I coudn't understand why it is not
displaying the required values untill unless we save the document. I
need insert this formula in a column where there are many other
formulas in the other columns and they change dynamically by the input
values even I don't save the document. Can you or anybody solve this
problem.

Thanks & Regards

Ramana

 

[Rowan Drummond]

Hi Ramana

Check your calculation setting. Tools>Options>Calculation. It sounds
like it is set to Manual. Change that to Automatic.

Regards
Rowan

 

[ramana]

Thankyou Rowan, Its now working, the calculation setting was in manual
because there is macro in my workbook, where it says
application.calculation = manual, so when ever I runthe marcro it is
going to manual mode. Now my problem is solved.

Thanks and Regards

Ramana

 

[Rowan Drummond]

You're welcome.

Maybe you should add the line of code:
Application.Calculation = xlCalculationAutomatic
at the end of your macro.

Regards
Rowan