Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas
ColumnA ColumnB ColumnC
C 1 *formula? D 2 B 3 E 4 F 5 A #N/A Above is the situation. I have formulas in column A and in Column B. The formulas in Column A will produce a letter. The formulas in Column B will produce a number. An #N/A error will be in only one of the cells in Column B in the specified range. I need a formula in cell C1 that will look in Columns A and B and when it sees the #N/A error in the cell in Column B, it will return the corresponding letter in Column A. So in this example, the formula in cell C1 will produce the result A, because the #N/A error is in cell B6. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas
=INDEX(A1:A200,SMALL(IF(ISERROR(B1:B200),ROW(B1:B2 00)),1),1)
Entered with Ctrl+Shift+Enter Rather than Just Enter since it is an array formula. -- Regards, Tom Ogilvy Todd Huttenstine wrote in message ... ColumnA ColumnB ColumnC C 1 *formula? D 2 B 3 E 4 F 5 A #N/A Above is the situation. I have formulas in column A and in Column B. The formulas in Column A will produce a letter. The formulas in Column B will produce a number. An #N/A error will be in only one of the cells in Column B in the specified range. I need a formula in cell C1 that will look in Columns A and B and when it sees the #N/A error in the cell in Column B, it will return the corresponding letter in Column A. So in this example, the formula in cell C1 will produce the result A, because the #N/A error is in cell B6. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas
Thank you Tom. That works perfectly. Can you explain
what the part of the formula that says Small serves? -----Original Message----- =INDEX(A1:A200,SMALL(IF(ISERROR(B1:B200),ROW (B1:B200)),1),1) Entered with Ctrl+Shift+Enter Rather than Just Enter since it is an array formula. -- Regards, Tom Ogilvy Todd Huttenstine wrote in message ... ColumnA ColumnB ColumnC C 1 *formula? D 2 B 3 E 4 F 5 A #N/A Above is the situation. I have formulas in column A and in Column B. The formulas in Column A will produce a letter. The formulas in Column B will produce a number. An #N/A error will be in only one of the cells in Column B in the specified range. I need a formula in cell C1 that will look in Columns A and B and when it sees the #N/A error in the cell in Column B, it will return the corresponding letter in Column A. So in this example, the formula in cell C1 will produce the result A, because the #N/A error is in cell B6. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas
=INDEX(I16:I21,SMALL(IF(ISERROR(J16:J21),ROW
(J16:J21)),1),1) Why will this formula not work when I change the cells to the ones above in this formula? -----Original Message----- =INDEX(A1:A200,SMALL(IF(ISERROR(B1:B200),ROW (B1:B200)),1),1) Entered with Ctrl+Shift+Enter Rather than Just Enter since it is an array formula. -- Regards, Tom Ogilvy Todd Huttenstine wrote in message ... ColumnA ColumnB ColumnC C 1 *formula? D 2 B 3 E 4 F 5 A #N/A Above is the situation. I have formulas in column A and in Column B. The formulas in Column A will produce a letter. The formulas in Column B will produce a number. An #N/A error will be in only one of the cells in Column B in the specified range. I need a formula in cell C1 that will look in Columns A and B and when it sees the #N/A error in the cell in Column B, it will return the corresponding letter in Column A. So in this example, the formula in cell C1 will produce the result A, because the #N/A error is in cell B6. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas
=INDEX(I16:I21,SMALL(IF(ISERROR(J16:J21),ROW(J16:J 21)-15),1),1)
will work or (starting in I1 for the Index function =INDEX(I1:I21,SMALL(IF(ISERROR(J16:J21),ROW(J16:J2 1)),1),1) Small pulls out the row that we need from the results of the IF function The if function returns the row number, so we need to subtract 15 for the first or work from I1 for the second. -- Regards, Tom Ogilvy Todd Huttenstine wrote in message ... =INDEX(I16:I21,SMALL(IF(ISERROR(J16:J21),ROW (J16:J21)),1),1) Why will this formula not work when I change the cells to the ones above in this formula? -----Original Message----- =INDEX(A1:A200,SMALL(IF(ISERROR(B1:B200),ROW (B1:B200)),1),1) Entered with Ctrl+Shift+Enter Rather than Just Enter since it is an array formula. -- Regards, Tom Ogilvy Todd Huttenstine wrote in message ... ColumnA ColumnB ColumnC C 1 *formula? D 2 B 3 E 4 F 5 A #N/A Above is the situation. I have formulas in column A and in Column B. The formulas in Column A will produce a letter. The formulas in Column B will produce a number. An #N/A error will be in only one of the cells in Column B in the specified range. I need a formula in cell C1 that will look in Columns A and B and when it sees the #N/A error in the cell in Column B, it will return the corresponding letter in Column A. So in this example, the formula in cell C1 will produce the result A, because the #N/A error is in cell B6. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Array Formulas to Regular Formulas | Excel Worksheet Functions | |||
Convert Array Formulas to Regular Formulas | Excel Worksheet Functions | |||
E2007 formulas display as formulas, not results | Excel Worksheet Functions | |||
Counting # of Formulas in a column with formulas and entered data | Excel Worksheet Functions | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions |