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...divis ibles 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

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 wrote:
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...divis ibles 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

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

Hi Ramana

Check your calculation setting. ToolsOptionsCalculation. It sounds
like it is set to Manual. Change that to Automatic.

Regards
Rowan

ramana wrote:
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

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

You're welcome.

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

Regards
Rowan

ramana wrote:
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