• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Array formula

B

BobHoskins

New Member
Basically, I have an array formula that looks like this:

=LARGE($F$6:$F$18,ROWS($H$6:H6))

I want to match the resulting array to F6:F18 to get the row numbers of the matches so I can use them in an index formula to find another value.

For the sake of example, if f6:f18 contains the numbers 1-13 in no particular order, then my array formula is going to produce an array from 13-1 from largest to smallest. If the number 5 is the number in f18, I'd like an array to give me the number 18 when it gets to number 5 in my array.

I want to get the row number instead of simply matching the result of my formula because in my actual problem there are going to be cases where there are two results the same.

Is there a way to do this? Maybe I'm going about this the wrong way, is there a simpler way to do this? I've uploaded a file which hopefully makes the problem clearer.

I'm trying to learn array formulas but still have a long way to go, any help would be appreciated, and if you have any further questions on the problem, please ask.

Thanks

-BH
 

Attachments

Thank you so much Narayank991!

That certainly solves my problem, I wish I could have found the answer myself, but I never would have seen it. I never would have thought to use COUNTIF like that.

If you wouldn't mind, I would like to ask a few questions to help my understanding of your solution:

I'm breaking it down into parts. Why doesn't {=IF(H6=$F$6:$F$18,ROW($E$6:$E$18))} on it's own, i.e. not as part of your formula, why does it give me FALSE instead of an array of row values? There is definitely a value for H6 in $F$6:$F$18, so why doesn't excel evaluate ROW($E$6:$E$18)? I tried to do something similar to this but it just gave me a range of FALSE values so I thought it must not be the correct way.

Surely MIN(ROW($E$6:$E$17))+1 will always evaluate to 7, if we replaced that formula with 7, would it still work as expected in all cases?


Even if you don't reply to this message, you've helped me immeasurably, not just with solving this particular problem, but I will remember and try and implement the ideas in this solution if I come across any similar problems in future.

Thanks again,

BH
 
BH

If you put in J6:
=IF(H6=$F$6:$F$18,ROW($E$6:$E$18))
then instead of pressing Enter press F9
Excel will respond with: ={FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;12;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

As it is an array formula that is the array of values
When entered into a single cell it only shows the Top Left value of the array or False

You can also:
Select J6:J18
Press F2
Type in or Paste in =IF(H6=$F$6:$F$18,ROW($E$6:$E$18))
Ctrl+Shift+Enter to see the array contents

Narrayan may have put the MIN(ROW($E$6:$E$17))+1 in to allow for you to move or insert extra Rows, But I'm not sure why either ?
 
BobHoskins said:
Surely MIN(ROW($E$6:$E$17))+1 will always evaluate to 7, if we replaced that formula with 7, would it still work as expected in all cases?
Click to expand...

You are correct. And yes, if you were sure that your range would never be extended/contracted and/or moved at any point in the future then you could replace that part with a static value, i.e. 7.

In general, however, the construction that Narayan has given you is a rigorous and flexible one. The point being that, should that range change at any point, then this construction will still give correct values. Whereas, if you had hard-coded the 7 in there, you would then have to spend some time and thought as to what this value should become so that the formulas function correctly with your new range.

The point of the construction is that we want to be sure that we can always generate an array of integers from 1 up to the number of rows within the range.

One way to do this would be to use something like:

ROW($E$6:$E$18)

which is:

{6;7;8;9;10;11;12;13;14;15;16;17;18}

and then work out what constant value needs to be subtracted from this array in order to give our desired result.

In this case, 5 would suffice, so that:

ROW($E$6:$E$18)-5

would be:

{6;7;8;9;10;11;12;13;14;15;16;17;18}-5

i.e.:

{1;2;3;4;5;6;7;8;9;10;11;12;13}

as desired.

The problem with this rather static approach is that, whereas it will be fine for a fixed, unchanging range, it is not rigorous enough to be certain of generating the correct array should that range change.

For example, if for whatever reason that range became, not $E$6:$E$18, but e.g. $E$10:$E$30, then:

ROW($E$10:$E$30)-5

would give:

{10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30}-5

i.e.:

{5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25}

which is not at all what this construction requires.

Of course, with a little effort we can always work out what this constant to be added/subtracted should be (in this case 9), but that's hardly ideal.

Whereas a construction such as:

ROW(Range)-MIN(ROW(Range))+1

will always give an array from 1 up to the number of rows within the range, whatever Range happens to be.

For example, if Range is $E$6:$E$18, as in your case, then:

ROW($E$6:$E$18)-MIN(ROW($E$6:$E$18))+1

becomes:

{6;7;8;9;10;11;12;13;14;15;16;17;18}-MIN(ROW($E$6:$E$18))+1

which is:

{6;7;8;9;10;11;12;13;14;15;16;17;18}-6+1

i.e.:

{1;2;3;4;5;6;7;8;9;10;11;12;13}

as required.

Or, if Range is instead $E$10:$E$30, then:

ROW($E$10:$E$30)-MIN(ROW($E$10:$E$30))+1

is:

{10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30}-MIN(ROW($E$10:$E$30))+1

which is:

{10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30}-10+1

i.e.:

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21}

again, as required.

Another construction which you will see in place of e.g.:

ROW($E$6:$E$18)-MIN(ROW($E$6:$E$18))+1

is:

ROW($E$6:$E$18)-ROW($E$6)+1

which, again, is a perfectly rigorous and flexible one.

In fact, most people tend to favour this one, perhaps because it has one less function call (the MIN). I personally prefer that given by Narayan, mainly because I almost always use Named Ranges, and, whereas:

ROW(Range)-MIN(ROW(Range))+1

lends itself perfectly nicely to such a Named Range, the alternative has to be constructed using something like:

ROW(Range)-ROW(INDEX(Range,1))+1

which to me is perhaps a touch less intuitive.

Regards
 
Hi BH,

Just a quick thing this can be achieved with Pivot table also. See the file with pivot in column K & L. With Pivot you will get the freedom of seeing SMALLEST to LARGEST also on the same source.

Also note you have entered the LARGE formula with CSE, which in this case is not required because LARGE can handle array of values.

Regards,
 

Attachments

You must log in or register to reply here.
Back
Top