Rows containing "#N/A" are messing with my formulas, please help
 

Hello,

I have a column of numbers in column C on sheet1.

I display the values in that column in column C on sheet2.
Some rows contain "#N/A" instead of numbers.

In column D on sheet 2, I would like to divide the numbers in consequtive
rows by each other. For example, if Column C didn't have "#N/A", then
D5=(C5/C4).

Suppose "#N/A" are present:
If C5 is "N/A", then I need D5="".
Otherwise,
if C4 contains "#N/A", then I need D5=(C5/C3).
If both C4 and C3 are "N/A", then D5=(C5/C2).
If more than 2 rows above C5 contain "N/A", then I would like D5 to be blank.

What should I do?
I tried using the formula
D391=IF(C3910,IF(C3900,((C391-C390)/C390)),IF(C3890,((C391-C389)/C389),IF(C3880,((C391-C388)/C388),IF(C3870,((C391-C387)/C387),""))))

but in this case, #N/A is displayed in cell D391...


Thank you for your kind help!

Do you have 2 questions, or did I miss something?

Anyway, see if this works for your #NA problem:

=IF(OR(ISNA(C5),AND(ISNA(C2),ISNA(C3),ISNA(C4)))," ",IF(ISNA(C3),C5/C2,IF(ISN
A(C4),C5/C3)))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Sam" wrote in message
...
Hello,

I have a column of numbers in column C on sheet1.

I display the values in that column in column C on sheet2.
Some rows contain "#N/A" instead of numbers.

In column D on sheet 2, I would like to divide the numbers in consequtive
rows by each other. For example, if Column C didn't have "#N/A", then
D5=(C5/C4).

Suppose "#N/A" are present:
If C5 is "N/A", then I need D5="".
Otherwise,
if C4 contains "#N/A", then I need D5=(C5/C3).
If both C4 and C3 are "N/A", then D5=(C5/C2).
If more than 2 rows above C5 contain "N/A", then I would like D5 to be
blank.

What should I do?
I tried using the formula
D391=IF(C3910,IF(C3900,((C391-C390)/C390)),IF(C3890,((C391-C389)/C389),IF
(C3880,((C391-C388)/C388),IF(C3870,((C391-C387)/C387),""))))

but in this case, #N/A is displayed in cell D391...


Thank you for your kind help!