last occurance in a array

[scidoc]

I have a array of numbers set up I need to find the "last" time a particular
number shows up. i.e. the last time that part number sold. Column A
contains the week number. The cells in columns B to G contain the part
numbers that sold that week. What I need is if in week 32 was the last time
part number 2356 sold. I need a function that will return the 32 with out
looking through the list manually.

 

[Guest]

=MAX(IF(B$1:B$100=2356,$A$1:$A$100))

Array Entered (Ctrl + Shift + Enter)

 

[Alan Beban]

Assuming you have only one years's data (i.e., 52 weeks), should be
=MAX(IF(B$1:G$52=2356,$A$1:$A$52)

Perhaps better still

=MAX(IF(B$1:G$52=H1,$A$1:$A$52)

with the part number in H1.

Alan Beban

 

[scidoc]

I've tried both formulas, all they give me is 0.
I've tried the IF function alone when there is only one occurence of 2356 in
the array B1:G52 and all I get is a false unless the I put the formula in the
same row as the occurence of 2356. Then it returns the vaule in the A1:A52
just like its susposed to. Could it be my version can't do ranges in the IF
function??? I'm using excel 2000 9.0.8961 SP-3.

 

[Dave Peterson]

Array Entered (Ctrl + Shift + Enter)

means this:

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

 

[Rick Rothstein \(MVP - VB\)]

Did you note the requirement to press Ctrl+Shift+Enter to "enter" the
formula? If not, select the cell with the formula, then click **in** the
formula bar (the cursor must be in the formula bar for this to work) and
press Crtl+Shift+Enter. That should make the formula work correctly for you.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Here is a formula you can use that can be entered normally...

=SUMPRODUCT(MAX((B1:B53=2356)*(B1:B53<>"")*(A1:A53)))

although I would put the 2356 in a cell, say C1, and then use this
instead...

=SUMPRODUCT(MAX((B1:B53=C1)*(B1:B53<>"")*(A1:A53)))

Put any part number in C1 and the above formula will tell you the week
number is appears in (0 if it doesn't appear anywhere).

Rick

 

[scidoc]

That works great, it will take me a little time (and study) to understand the
logic of how it works though.

 

[Dave Peterson]

Is there a reason you included the b1:b53<>"" factor?

If the cell is equal to 2356, it won't be blank.

 

[Rick Rothstein \(MVP - VB\)]

It stopped the formula from reporting an answer of 53 (the end of the B
range being tested) when C1 was empty (with it, the formula reports 0 as I
believe it should). Since C1 will more than likely never be blank, I guess
it can be removed; I just didn't like leaving the incorrect result just in
case.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Of course, if you are asking your question for the first implementation of
the formula, you are correct... it is not needed at all. What I did is
develop the formula for the general case first, and then modified it for the
specific case (following the layout Alan used of giving both scenarios in
his posting)... I just didn't think about the ramifications of not using C1
to hold the number when I modified the formula for the specific case.

Rick

It stopped the formula from reporting an answer of 53 (the end of the B
range being tested) when C1 was empty (with it, the formula reports 0 as I
believe it should). Since C1 will more than likely never be blank, I guess
it can be removed; I just didn't like leaving the incorrect result just in
case.

Rick