Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 17th 05, 03:43 PM
Sam
 
Posts: n/a
Default 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!


  #2   Report Post  
Old May 17th 05, 04:47 PM
RagDyeR
 
Posts: n/a
Default

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!




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Hiding Rows if the linked rows are blank KG Excel Discussion (Misc queries) 9 May 18th 05 02:32 AM
I need a formula to find rows within a date range in one column? M. Penney Excel Worksheet Functions 5 May 12th 05 12:32 AM
Insert rows, keep a formula? tb Excel Worksheet Functions 1 April 23rd 05 03:41 PM
can you hide rows with a formula? david Excel Discussion (Misc queries) 2 April 8th 05 03:21 PM
Relative Indirect Formula Referencing? Damian Excel Worksheet Functions 1 January 7th 05 04:16 AM


All times are GMT +1. The time now is 06:17 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017