• 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.

Finding the last occurrence of a value in an array

V

Veldrin

New Member
Hey

I managed to track down a formula that returns the row/ column value of the last occurrence of a value in an array. It looks something like this:

=SUMPRODUCT(MAX(($A$1:$W$1="X")*COLUMN($A$1:$W$1)))

It works quite well, but I honestly don't know how it works

This is how I understand it: $A$1:$W$1="X" returns an array of 1's and 0's, so MAX(($A$1:$W$1="X")*COLUMN($A$1:$W$1)) will return the maximum all the 1's and 0's multiplied by their column values, ergo the last one. But if you just enter that function (without the sumproduct that is), the answer is 0. If you add the sumproduct function, you get the correct answer. I've uploaded an example.

Can someone please help me understand what's going on? I get the feeling that if you understand how this function works, you'll be able to do a lot more in Excel

Thanks :)
Veldrin
 

Attachments

@Veldrin
Welcome to the forum.

MAX(($A$1:$W$1="X")*COLUMN($A$1:$W$1)) this returns 0 because MAX() function accepts array of numbers and not array operations which is ($A$1:$W$1="X"), and you press enter after the formula. So it return 0. But if you tell Excel that you are doing an array operation to get an array of numbers, it will return right value. You have to press Ctrl+Shift+Enter, to get it.

Regards!
 
Ok I think I understand it a bit better. I'll have a look again tomorrow when I'm back at work.

Thanks for the response, much appreciated :)
 
This is amazing, i didn't know you can do so much more with functions :DD

So using the Sumproduct, without the array operators {}, tells excel you're dealing with an array and not a value in the input field?
 
@Veldrin
Just to inform you quickly, Sumproduct is designed to handle array. You can see that in the screen tip of the function, it ask for array and not numbers. Functions which requires numbers and you pass an array to them, than you need to inform Excel that you are doing an array operation by pressing Ctrl+Shift+enter, that will put {} around your formula, which you can see only in formula bar.

Small tip: If you are putting IF() with array in it no matter what if te main function can handlr array or not you have to enter with Ctrl+Shift+Enter. e.g. =SUMPRODUCT(if(A1:A5="X",1,0)). Now this function will require Ctrl+Shift+Enter even if sumproduct is capable of handling arrays.

I hope this small tip will prove useful to you. For getting more indepth knowledge about array formulas do visit the link posted by @Faseeh.

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