#1
|
|||
|
|||
Complex array formula
Dear all,
One part of my database contains 5 columns with values >=0. I come up with a solution in order to get the address of cells which has values greater than 0, within each row of my database. This addresses will then be used to extract values of other cells, relatively positioned within this cells. I have used arrays (as I try to improve my knowledge within this feature) and after getting the result, I have tried to simplify the formula, but an error occurred which I cann't explain to myself. Everything is in the attached file. By the way, can you recommend some good books / articles / posts etc. about advanced arrays? Andrei |
#2
|
||||
|
||||
Hi,
Okay, this is a pretty weird one... First, to explain the error. The array formula in L2 which is returning an #VALUE! error is this: Code:
=MIN(IF( INDIRECT(ADDRESS(ROW(),COLUMN(INDIRECT(F2))+1)&":"&CELL("address",$E2))>0 ,{1,2,3},{0,0,0})) Code:
INDIRECT(ADDRESS(ROW(),COLUMN(INDIRECT(F2))+1)&":"&CELL("address",$E2)) Sometimes when INDIRECT is used in a formula, when it returns the range reference, the range can be implicitly dereferenced, meaning that the formula can read the values from that range. On other occassions, the range reference cannot be implicitly dereferenced and you have to give the formula a helping hand in getting the values from the cells - otherwise you will get an error. This is driven by the context of the formula - what other functions INDIRECT is nested inside of, so it would be very difficult to come up with a hard a fast rule as to when you do/don't have to do this. In this case the rest of the formula can't work out what it needs to do with C2:E2 when it's returned. We are dealing with numbers, so we can give the formula a hand by using the N() function to convert the C2:E2 reference into an array of numbers. Code:
=MIN(IF( N(INDIRECT(ADDRESS(ROW(),COLUMN(INDIRECT(F2))+1)&":"&CELL("address",$E2)))>0 ,{1,2,3},{0,0,0})) Code:
=MIN(IF( SUM(INDIRECT(ADDRESS(ROW(),COLUMN(INDIRECT(F2))+1)&":"&CELL("address",$E2)))>0 ,{1,2,3},{0,0,0})) As an aside, I would mention that using array formulas to put cell addresses into cells and then using those addresses in subsequent formulas is very unusual. Typically you would try to get the range reference and use it directly. Quote:
As a result, there really isn't much source material on them. A few years ago I wrote an introductory tutorial on them here: http://www.xtremevbtalk.com/showthread.php?t=296012 and there are some links in the last post which may be of interest. There are several Excel worksheet functions which can return the Range structure you saw in this post, including OFFSET() and INDEX(). There have been a few questions on them on the online forums, such as this one: http://www.excelforum.com/excel-work...k-in-name.html |
#3
|
|||
|
|||
Hello Colin,
Thank's a lot for your answer Just to bring the whole picture (because this is just a part of it), my database has 5 columns with phone numbers, some correct, some incorrect. I added 5 columns which calculates whether this numbers are correct (values greater than 0) or incorrect (values = 0) - this columns are presented in the sample data. After I get the cells where the values are correct, I use the OFFSET function to extract the phone numbers from the corresponding columns. I know this is weird, but I did it in order to test my array understanding (At first look, I come with a simpler solution). If you think it is worth to bring the VBA solution (maybe there will be other people finding it interested)..... Thank you also for the links I will carefully look at them Regards, Andrei |
#4
|
|||
|
|||
Re: Complex array formula
Hello Colin,
When carefully looking at the N function, although the result it is not an ERROR value, it is not a correct value either. N function does not return a range as needed in my example, but rather a value as you can see in the attached file. It seems that N is not the proper function for this issue... Andrei |
#5
|
||||
|
||||
Hi Andrei,
In that case I don't understand what you're trying to do or, more importantly, why. I explained that the N() function extracts the value from the cell in my previous post. In cell K2 you had a formula which was working already and it returns the value 1, which is what I amended your other formula to do. They match. If you don't want to return the value, what do you want to return - the address? If so, why is the address useful to you and why don't you just concatenate the strings in cells F2:J5? |
#6
|
|||
|
|||
Hello Colin,
I have explained in my first reply to your answer what I have tried to do. Basically my question was why I get an error on that formula and your answer was great (the "dereferenced" description worths the money) and I really appreciate it (thank you once again) You are right that the formula results from K2 and L2 match, but they differ on the FALSE condition of the IF function (one has the array {99,99,99} and the other one {0,0,0}. This is my mistake when building the formulas... So it seems to me that the N function does not return the values from C2:E2, but rather the value in C2. Anyway thank you for your details, my "direct" knowledge of INDIRECT function moved a step further Andrei |
#7
|
||||
|
||||
Hi Andrei,
I might be able to offer a formula which will give the final result, but the methodology would be different. From what I can see, you first want to identify the cells in each row which contain values greater than 0. Once those cells have been found, what values to you want to obtain or calculate? |
#8
|
|||
|
|||
Hello Colin,
No need to bother as I get the result using the complicated formulas from F2 to J2; the problem came when trying to simplify the formula and you explained very well why the error occurred. Regards, Andrei |
#9
|
||||
|
||||
Hi Andrei,
Fair enough, if you're happy then I'm happy. Incidentally, if you happened to want to extract a list of values greater than zero, in the order that they appear, then I've attached an example for you. Code:
=IF(COUNTIF($B2:$F2,">0")>=(COLUMN(G2)-COLUMN($G2)+1), INDEX($B2:$F2,SMALL(IF($B2:$F2>0,COLUMN($B2:$F2)-COLUMN($B2)+1),COLUMN(G2)-COLUMN($G2)+1)),"") Colin |
#10
|
|||
|
|||
Dear Colin,
With the "bonus" you have just provided, I'm even happier Regards, Andrei |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Complex Find and Replace | paulkaye | Word | 1 | 11-13-2011 04:23 AM |
Modifying subject in Hyperlink - Complex | amritr | Outlook | 0 | 05-26-2011 11:20 PM |
Searching for Word Reformatter for Complex Docs | jchapman | Word | 1 | 05-05-2011 04:50 PM |
Look up an array based on user input | johnsmb | Excel | 2 | 01-07-2011 01:12 PM |
Complex Document Formatting | rguima | Word | 0 | 02-22-2009 04:31 PM |