Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-01-2012, 02:25 AM
andrei andrei is offline Complex array formula Windows 7 32bit Complex array formula Office 2007
Novice
Complex array formula
 
Join Date: Sep 2011
Posts: 14
andrei is on a distinguished road
Default 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
Attached Files
File Type: xlsx Sample_data.xlsx (15.7 KB, 15 views)
Reply With Quote
  #2  
Old 02-01-2012, 06:52 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Complex array formula Windows 7 32bit Complex array formula Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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}))
Now, let's look at the (outer) INDIRECT component of the formula:
Code:
INDIRECT(ADDRESS(ROW(),COLUMN(INDIRECT(F2))+1)&":"&CELL("address",$E2))
INDIRECT is a function which returns a reference to a range and, in this case, the range reference being returned is C2: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}))
The N() function is basically saying, "Give me the numbers from C2:E2". Other numerical functions would work too, for example SUM(), but I think the intention of their use is less clear:
Code:
=MIN(IF(
    SUM(INDIRECT(ADDRESS(ROW(),COLUMN(INDIRECT(F2))+1)&":"&CELL("address",$E2)))>0
    ,{1,2,3},{0,0,0}))
If you would like, I can show you how to prove all this by using some VBA to perform an analysis.


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:
By the way, can you recommend some good books / articles / posts etc. about advanced arrays?
My take on array formulas is that learning them is important because if you understand them well then you'll understand pretty much any formula that's thrown at you. That said, it's very often the case that when it comes to building your spreadsheets, you should avoid them. They can have quite a penalty on performance speed and they are complicated so it is often better to split them out into smaller and simpler formulas.
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
Reply With Quote
  #3  
Old 02-01-2012, 08:27 AM
andrei andrei is offline Complex array formula Windows 7 32bit Complex array formula Office 2007
Novice
Complex array formula
 
Join Date: Sep 2011
Posts: 14
andrei is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 02-02-2012, 06:56 AM
andrei andrei is offline Complex array formula Windows 7 32bit Complex array formula Office 2007
Novice
Complex array formula
 
Join Date: Sep 2011
Posts: 14
andrei is on a distinguished road
Default 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
Attached Files
File Type: xlsx Sample_data_.xlsx (16.0 KB, 13 views)
Reply With Quote
  #5  
Old 02-02-2012, 10:27 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Complex array formula Windows 7 32bit Complex array formula Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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?
Reply With Quote
  #6  
Old 02-03-2012, 12:43 AM
andrei andrei is offline Complex array formula Windows 7 32bit Complex array formula Office 2007
Novice
Complex array formula
 
Join Date: Sep 2011
Posts: 14
andrei is on a distinguished road
Default

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
Reply With Quote
  #7  
Old 02-03-2012, 02:39 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Complex array formula Windows 7 32bit Complex array formula Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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?
Reply With Quote
  #8  
Old 02-03-2012, 02:47 AM
andrei andrei is offline Complex array formula Windows 7 32bit Complex array formula Office 2007
Novice
Complex array formula
 
Join Date: Sep 2011
Posts: 14
andrei is on a distinguished road
Default

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
Reply With Quote
  #9  
Old 02-03-2012, 03:17 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Complex array formula Windows 7 32bit Complex array formula Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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)),"")
Regards,
Colin
Attached Files
File Type: xlsx Sample_data_.xlsx (14.4 KB, 9 views)
Reply With Quote
  #10  
Old 02-03-2012, 03:40 AM
andrei andrei is offline Complex array formula Windows 7 32bit Complex array formula Office 2007
Novice
Complex array formula
 
Join Date: Sep 2011
Posts: 14
andrei is on a distinguished road
Default

Dear Colin,

With the "bonus" you have just provided, I'm even happier

Regards,
Andrei
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Complex array formula 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
Complex array formula 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

Other Forums: Access Forums

All times are GMT -7. The time now is 03:01 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft