writting an IF command that will skip cells
 

Hi guys this is my problem.

I have serial numbers in my entire A column and dates in my entire B column.

On a separate workskeet, I want to create a command that will check if the
serial number is a multiple of 8. If it is, I want it to return the date the
the corresponding B column/row. I came up with =IF(MOD(A2,8)=0,B2,"NA").

However, I do not want "NA" returned when the A column value is not a
multiple of 8. Instead I want it to check the value in the next A row, and if
that is not a multiple of 8, to keep going till it reaches a row where the
value the data in cell A is indeed a multiple of 8 and return the date from
the coresponding B column/row.

How might I achieve this? Thank you in advance.

Prem

writting an IF command that will skip cells
 

Hi

On the second sheet, change your formula to
=IF(MOD(A2,8)=0,B2,"")
This will leave Nulls on the rows where there is no match.
Copy down for as many rows as you have data in column A of sheet 1.
When finished, Sort Sheet2 Column A and all the blank rows will be moved to
the end of the list and your results will be bunched at the top.
--
Regards
Roger Govier

"prem" wrote in message
...
Hi guys this is my problem.

I have serial numbers in my entire A column and dates in my entire B
column.

On a separate workskeet, I want to create a command that will check if the
serial number is a multiple of 8. If it is, I want it to return the date
the
the corresponding B column/row. I came up with =IF(MOD(A2,8)=0,B2,"NA").

However, I do not want "NA" returned when the A column value is not a
multiple of 8. Instead I want it to check the value in the next A row, and
if
that is not a multiple of 8, to keep going till it reaches a row where the
value the data in cell A is indeed a multiple of 8 and return the date
from
the coresponding B column/row.

How might I achieve this? Thank you in advance.

Prem

writting an IF command that will skip cells
 

Hi Roger thank you for the reply. I had already thought of that but I was
hoping to completely skip the null values and only return the ones I want so
that I would not have to sort out the second sheet. So in other words,
instead of =IF(MOD(A2,8)=0,B2,"") I need something to replace the "" (null
value) with some sort of function that will skip to the next serial number
and perform the check.

"Roger Govier" wrote:

Hi

On the second sheet, change your formula to
=IF(MOD(A2,8)=0,B2,"")
This will leave Nulls on the rows where there is no match.
Copy down for as many rows as you have data in column A of sheet 1.
When finished, Sort Sheet2 Column A and all the blank rows will be moved to
the end of the list and your results will be bunched at the top.
--
Regards
Roger Govier

"prem" wrote in message
...
Hi guys this is my problem.

I have serial numbers in my entire A column and dates in my entire B
column.

On a separate workskeet, I want to create a command that will check if the
serial number is a multiple of 8. If it is, I want it to return the date
the
the corresponding B column/row. I came up with =IF(MOD(A2,8)=0,B2,"NA").

However, I do not want "NA" returned when the A column value is not a
multiple of 8. Instead I want it to check the value in the next A row, and
if
that is not a multiple of 8, to keep going till it reaches a row where the
value the data in cell A is indeed a multiple of 8 and return the date
from
the coresponding B column/row.

How might I achieve this? Thank you in advance.

Prem

writting an IF command that will skip cells
 

Assuming your serial nos run in A2 down, viz.: 1,2,3 ...
then this might suffice to directly extract the corresp. dates from col B
for serial nos: 8,16,32, etc in col A

In say, C2: =INDEX(B:B,ROWS($1:1)*8+1)
Copy C2 down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"prem" wrote:
Hi Roger thank you for the reply. I had already thought of that but I was
hoping to completely skip the null values and only return the ones I want so
that I would not have to sort out the second sheet. So in other words,
instead of =IF(MOD(A2,8)=0,B2,"") I need something to replace the "" (null
value) with some sort of function that will skip to the next serial number
and perform the check.

writting an IF command that will skip cells
 

Typo in line:
for serial nos: 8,16,32, etc in col A

should read:
for serial nos: 8,16,24,32, etc in col A
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

writting an IF command that will skip cells
 

Hi Max

I had assumed that the serial numbers needed to divide by 8, not that the OP
wanted the 8th, 16th 24th value etc.

--
Regards
Roger Govier

"Max" wrote in message
...
Assuming your serial nos run in A2 down, viz.: 1,2,3 ...
then this might suffice to directly extract the corresp. dates from col B
for serial nos: 8,16,32, etc in col A

In say, C2: =INDEX(B:B,ROWS($1:1)*8+1)
Copy C2 down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"prem" wrote:
Hi Roger thank you for the reply. I had already thought of that but I was
hoping to completely skip the null values and only return the ones I want
so
that I would not have to sort out the second sheet. So in other words,
instead of =IF(MOD(A2,8)=0,B2,"") I need something to replace the ""
(null
value) with some sort of function that will skip to the next serial
number
and perform the check.

writting an IF command that will skip cells
 

Hi Roger,

.. the OP wanted the 8th, 16th 24th value etc.
Yes, those were my assumptions on the OP's underlying intents

.. the serial numbers needed to divide by 8
If it was indeed the case that the serial numbers were of a random nature in
A2 down, then I was going to offer this non-array set-up to the OP (upon his
reply)

In E2: =IF(MOD(A2,8)=0,ROW(),"")
with E1 left empty

In F2:
=IF(ROWS($1:1)COUNT(E:E),"",INDEX(B:B,SMALL(E:E,R OWS($1:1))))
E2:F2 copied down would return required results in col F
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Roger Govier" wrote:
Hi Max

I had assumed that the serial numbers needed to divide by 8, not that the OP
wanted the 8th, 16th 24th value etc.

--
Regards
Roger Govier

writting an IF command that will skip cells
 

Hi Max,

Thank you for your reply. It works just as it should. I just adapted it for
other columns and its works out too. Thank you again.

"Max" wrote:

Typo in line:
for serial nos: 8,16,32, etc in col A

should read:
for serial nos: 8,16,24,32, etc in col A
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

writting an IF command that will skip cells
 

Hi Max,

My serial numbers were in order and not random in nature. However the code
you provided here could prove useful in other situations. Thank you.

Prem

"Max" wrote:

Hi Roger,

.. the OP wanted the 8th, 16th 24th value etc.
Yes, those were my assumptions on the OP's underlying intents

.. the serial numbers needed to divide by 8
If it was indeed the case that the serial numbers were of a random nature in
A2 down, then I was going to offer this non-array set-up to the OP (upon his
reply)

In E2: =IF(MOD(A2,8)=0,ROW(),"")
with E1 left empty

In F2:
=IF(ROWS($1:1)COUNT(E:E),"",INDEX(B:B,SMALL(E:E,R OWS($1:1))))
E2:F2 copied down would return required results in col F
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Roger Govier" wrote:
Hi Max

I had assumed that the serial numbers needed to divide by 8, not that the OP
wanted the 8th, 16th 24th value etc.

--
Regards
Roger Govier

writting an IF command that will skip cells
 

Welcome, Prem. Glad it helped.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"prem" wrote in message
...
Hi Max,

Thank you for your reply. It works just as it should. I just adapted it
for
other columns and its works out too. Thank you again.

writting an IF command that will skip cells
 

Welcome, and thanks for clarifying.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"prem" wrote in message
...
Hi Max,

My serial numbers were in order and not random in nature. However the code
you provided here could prove useful in other situations. Thank you.

Prem

writting an IF command that will skip cells
 

On Fri, 2 May 2008 23:49:01 -0700, prem wrote:

Hi guys this is my problem.

I have serial numbers in my entire A column and dates in my entire B column.

On a separate workskeet, I want to create a command that will check if the
serial number is a multiple of 8. If it is, I want it to return the date the
the corresponding B column/row. I came up with =IF(MOD(A2,8)=0,B2,"NA").

However, I do not want "NA" returned when the A column value is not a
multiple of 8. Instead I want it to check the value in the next A row, and if
that is not a multiple of 8, to keep going till it reaches a row where the
value the data in cell A is indeed a multiple of 8 and return the date from
the coresponding B column/row.

How might I achieve this? Thank you in advance.

Prem

If your serial numbers are randomly entered (or entered with occasional skips
in the sequence), then this array formula, entered into some cell and filled
down until it produces an error, should return the adjacent dates.

SerialNums and Dts are named ranges, but you can substitute any single column
range reference or NAME.

To enter an array formula, hold down <ctrl<shift while hitting <enter. Excel
will place braces {...} around the formula:

=INDEX(Dts,SMALL((MOD(SerialNums,8)=0)*ROW(SerialN ums),
ROWS($1:1)+SUMPRODUCT(--(MOD(SerialNums,8)<0))))

--ron