| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
last occurance in a array
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
last occurance in a array
=MAX(IF(B$1:B$100=2356,$A$1:$A$100))
Array Entered (Ctrl + Shift + Enter) "scidoc" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
last occurance in a array
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 - wrote: =MAX(IF(B$1:B$100=2356,$A$1:$A$100)) Array Entered (Ctrl + Shift + Enter) "scidoc" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
last occurance in a array
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. "Alan Beban" wrote: 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 - wrote: =MAX(IF(B$1:B$100=2356,$A$1:$A$100)) Array Entered (Ctrl + Shift + Enter) "scidoc" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
last occurance in a array
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.) scidoc wrote: 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. "Alan Beban" wrote: 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 - wrote: =MAX(IF(B$1:B$100=2356,$A$1:$A$100)) Array Entered (Ctrl + Shift + Enter) "scidoc" wrote in message ... 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. -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
last occurance in a array
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 "scidoc" wrote in message ... 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. "Alan Beban" wrote: 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 - wrote: =MAX(IF(B$1:B$100=2356,$A$1:$A$100)) Array Entered (Ctrl + Shift + Enter) "scidoc" wrote in message ... 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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
last occurance in a array
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" wrote in message ... 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. "Alan Beban" wrote: 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 - wrote: =MAX(IF(B$1:B$100=2356,$A$1:$A$100)) Array Entered (Ctrl + Shift + Enter) "scidoc" wrote in message ... 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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
last occurance in a array
That works great, it will take me a little time (and study) to understand the
logic of how it works though. "Rick Rothstein (MVP - VB)" wrote: 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" wrote in message ... 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. "Alan Beban" wrote: 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 - wrote: =MAX(IF(B$1:B$100=2356,$A$1:$A$100)) Array Entered (Ctrl + Shift + Enter) "scidoc" wrote in message ... 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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
last occurance in a array
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)" wrote: 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" wrote in message ... 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. "Alan Beban" wrote: 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 - wrote: =MAX(IF(B$1:B$100=2356,$A$1:$A$100)) Array Entered (Ctrl + Shift + Enter) "scidoc" wrote in message ... 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. -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
last occurance in a array
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 "Dave Peterson" wrote in message ... 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)" wrote: 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" wrote in message ... 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. "Alan Beban" wrote: 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 - wrote: =MAX(IF(B$1:B$100=2356,$A$1:$A$100)) Array Entered (Ctrl + Shift + Enter) "scidoc" wrote in message ... 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. -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
last occurance in a array
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 "Rick Rothstein (MVP - VB)" wrote in message ... 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 "Dave Peterson" wrote in message ... 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)" wrote: 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" wrote in message ... 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. "Alan Beban" wrote: 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 - wrote: =MAX(IF(B$1:B$100=2356,$A$1:$A$100)) Array Entered (Ctrl + Shift + Enter) "scidoc" wrote in message ... 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. -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| occurance in each year | Excel Worksheet Functions | |||
| Occurance Counting | Excel Worksheet Functions | |||
| frequency of occurance of all words in 2-D array | Excel Discussion (Misc queries) | |||
| Need Formula for Last Occurance | Excel Worksheet Functions | |||
| frequency for each occurance | Excel Worksheet Functions | |||
Linear Mode
