Home |
Search |
Today's Posts |
#1
|
|||
|
|||
another EXPERT LEVEL FORMULA from me
Hi All
A B 1 #N/A d 2 #DIV/0!, h 3 5 k 4 3 t In Sheet1 Ive got two columns: Column A and Column B. What Im tiring to do is to create a formula to get the value of the first cell in a column €œB€ which corresponding (offset) cell in column A is the first row in Column A with NO ISERROR on it like #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! In the example above this is €œk€ (the value in cell B3). So far my guided formula is array and look like this::=INDEX(A1:A20,MIN(ROW(B1:B20)*(B1:B20<#N/A))) but it doesnt work at all. Im also planning the formula to be in a different sheet than the range with the data. Any Help is highly appreciated as always. Tim |
#2
|
|||
|
|||
another EXPERT LEVEL FORMULA from me
On the right lines
=INDEX(B:B,MIN(IF(ISERROR(A1:A10),9.99999999999999 E+307,ROW(A1:A10)))) -- HTH RP (remove nothere from the email address if mailing direct) "Tim" wrote in message ... Hi All A B 1 #N/A d 2 #DIV/0!, h 3 5 k 4 3 t In Sheet1 I've got two columns: Column A and Column B. What I'm tiring to do is to create a formula to get the value of the first cell in a column "B" which corresponding (offset) cell in column A is the first row in Column A with NO ISERROR on it like #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! In the example above this is "k" (the value in cell B3). So far my guided formula is array and look like this::=INDEX(A1:A20,MIN(ROW(B1:B20)*(B1:B20<#N/A))) but it doesn't work at all. I'm also planning the formula to be in a different sheet than the range with the data. Any Help is highly appreciated as always. Tim |
#3
|
|||
|
|||
another EXPERT LEVEL FORMULA from me
Another PERFECT ANSWER from You.
Thank you Bob! "Bob Phillips" wrote: On the right lines =INDEX(B:B,MIN(IF(ISERROR(A1:A10),9.99999999999999 E+307,ROW(A1:A10)))) -- HTH RP (remove nothere from the email address if mailing direct) "Tim" wrote in message ... Hi All A B 1 #N/A d 2 #DIV/0!, h 3 5 k 4 3 t In Sheet1 I've got two columns: Column A and Column B. What I'm tiring to do is to create a formula to get the value of the first cell in a column "B" which corresponding (offset) cell in column A is the first row in Column A with NO ISERROR on it like #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! In the example above this is "k" (the value in cell B3). So far my guided formula is array and look like this::=INDEX(A1:A20,MIN(ROW(B1:B20)*(B1:B20<#N/A))) but it doesn't work at all. I'm also planning the formula to be in a different sheet than the range with the data. Any Help is highly appreciated as always. Tim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simplify formula | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Cell shows formula and not the result of the formula. | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |