Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) | |||
I need a formula to find rows within a date range in one column? | Excel Worksheet Functions | |||
Insert rows, keep a formula? | Excel Worksheet Functions | |||
can you hide rows with a formula? | Excel Discussion (Misc queries) | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions |